Get Updated Record

[Scott Selikoff]

Is it psosible to perform an update in JDBC (preferably via Oracle) such that you can perform an update and read the results in a single SQL statement?

Other restrictions are that the row does not have any generated keys. I'd like to do the equivalent of the following "Fetch me a single open record, change a column value to be closed, and tell me which record I updated"

I can do this with 2 statements using SELECT FOR UPDATE then performing the UPDATE but in high traffic this throws deadlock exceptions in oracle about 5% of the time.

The query that performs the update is:

UPDATE myTable
SET status ='Closed'
WHERE id IN (SELECT id
FROM myTable
WHERE status = 'Open' AND ROWNUM = 1)

This will perform the update I want but it won't tell me which record was updated especially if there are multiple threads executing at the same time with this query.
[ March 23, 2006: Message edited by: Scott Selikoff ]

[R. M. Menon]

Originally posted by Scott Selikoff:
Is it psosible to perform an update in JDBC (preferably via Oracle) such that you can perform an update and read the results in a single SQL statement?

Other restrictions are that the row does not have any generated keys. I'd like to do the equivalent of the following "Fetch me a single open record, change a column value to be closed, and tell me which record I updated"

I can do this with 2 statements using SELECT FOR UPDATE then performing the UPDATE but in high traffic this throws deadlock exceptions in oracle about 5% of the time.

The query that performs the update is:

UPDATE myTable
SET status ='Closed'
WHERE id IN (SELECT id
FROM myTable
WHERE status = 'Open' AND ROWNUM = 1)

This will perform the update I want but it won't tell me which record was updated especially if there are multiple threads executing at the same time with this query.

[ March 23, 2006: Message edited by: Scott Selikoff ]

Use sequence for generating unique values, if required - you have to have a way of uniquely identifying a row in a table (primary key)

You can do something like this in PL/QSL:

UPDATE myTable
SET status ='Closed'
WHERE id IN (SELECT id
FROM myTable
WHERE status = 'Open' AND ROWNUM = 1)
RETURNING <your primary key> INTO l_uniq_id;

where l_uniq_id is a local variable in a PL/SQL procedure...

Also to avoid the deadlock you should order the rows (by primary key) that you are selecting from the table within the update so threads dont step on each other. Something like:


UPDATE myTable
SET status ='Closed'
WHERE id IN (SELECT id fROM (SELECT id
FROM myTable
WHERE status = 'Open' ORDER BY <primary_key> AND ROWNUM = 1)
RETURNING <primary_key> INTO l_uniq_id;

[Scott Selikoff]

I do have a unique id (just not one generated at the time of the update). I try to avoid PLSQL solutions when I can, I was hoping for something in pure SQL.

Ironically, adding the ORDER BY caused actual deadlocks instead of "deadlock exceptions" from oracle since they were all grabbing the first record. Oh well.

I decided to solve the problem using java method synchronization on the 2 sql queries since the environment is such that its a single jvm with dozen of threads. It still uses manuel committing for database protection, but so far seems to be more reliable than what I had before.

[R. M. Menon]

Originally posted by Scott Selikoff:
I do have a unique id (just not one generated at the time of the update). I try to avoid PLSQL solutions when I can, I was hoping for something in pure SQL.

Ironically, adding the ORDER BY caused actual deadlocks instead of "deadlock exceptions" from oracle since they were all grabbing the first record. Oh well.


I decided to solve the problem using java method synchronization on the 2 sql queries since the environment is such that its a single jvm with dozen of threads. It still uses manuel committing for database protection, but so far seems to be more reliable than what I had before.

Any reason to avoid PL/SQL? You are missing out on many features - including this one...

Also, realized in this case, you will "block" (not deadlock) by putting order by - sorry about that (I assumed sessions commit immediately after the update - which was stupid of me, of course)

can you give me pseudo code for the case where you were using "select for update" that caused deadlock 5% of time?

Also, I am trying to understand why one would want to update a "random" row in a session. (Using rownum gives you a random row to update.)
Can you explain why?

[Scott Selikoff]

Its no problem, I liked your suggestions.

My goal is to simulate a user log-on locking system that simulates X number of random users logging to the system and performing tests. Each user can be logged in at most once at a time and the users are connected to real database objects. I could have probably created a similar system in memory via java using a static HashMap, although I wanted to be able to monitor different users logging in and out via the database.

The psuedo-code was probably deadlocking because it was querying too many records... It was something like:

// Psuedo code
setAutoCommitOff()
execute query "SELECT id FROM myTable WHERE status='Open' FOR UPDATE"
Read First Record
execute query "UPDATE status='Closed' for first record"
commit()

It worked most of the time, but started to throw deadlock exceptions on the first query after awhile (6 out of 75 tests).

[Martin Simons]

Query for the records to change with a select rather than an
update using a updateable resultset, then use the resultset
update methods to change the values.