This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Get Updated Record Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Get Updated Record" Watch "Get Updated Record" New topic
Author

Get Updated Record

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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 ]

My Blog: Down Home Country Coding with Scott Selikoff
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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:



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
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Get Updated Record
 
Similar Threads
Record Locking using EJB
Pagination Query in Oracle
how to create BLOB and CLOB data
JSP and Access
hibernate how to make a count and group by