[OCP 21 Professional Study Guide] [OCP 17 Complete Study Guide] [OCP 17 Practice Tests] [OCP 11 Complete Study Guide] [OCP 11 Programmer I] [OCP 11 Programmer II] [OCP 11 Practice Tests] [OCA 8] [OCP 8] [OCP 8 Practice Tests Book] [Blog]
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 ]
[OCP 21 Professional Study Guide] [OCP 17 Complete Study Guide] [OCP 17 Practice Tests] [OCP 11 Complete Study Guide] [OCP 11 Programmer I] [OCP 11 Programmer II] [OCP 11 Practice Tests] [OCA 8] [OCP 8] [OCP 8 Practice Tests Book] [Blog]
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.
[OCP 21 Professional Study Guide] [OCP 17 Complete Study Guide] [OCP 17 Practice Tests] [OCP 11 Complete Study Guide] [OCP 11 Programmer I] [OCP 11 Programmer II] [OCP 11 Practice Tests] [OCA 8] [OCP 8] [OCP 8 Practice Tests Book] [Blog]
Don't get me started about those stupid light bulbs. |