This content has been marked as final. Show 5 replies
Hm. Is this part of an XA transaction by any chance? I know that Oracle maintains separate bookkeeping for such transactions which can cause rows to stay 'locked' even when the regular DBA views will indicate there is no user currently locking the record at all. I have no idea about the actual details and what you need to do to clean up such a situation, that is something a DBA should know and do.
If not... well this is more of a question for people who know the DBMS, which makes it a target for the Oracle DBMS forums. Java developers don't tend to have DBA-level knowledge of the database, you should ask the question where you have more chance of people with expertise answering stuff:
General Database Discussions
You are making a fundamental mistake, based on your not really understanding
how Oracle, and/or other DBMSes may differ wildly in how/whether they lock
data rows. So far as what you've said, your select query will lock nothing, nada,
zero. Nothing about that read will stop some other used from updating or deleting
that row. Depending on your connection state, neither will even the update.
At commit time, as long as the DBMS can show a logical way that the final outcome
could have happened if the competing transactions had been serialized (either one
went first, then the other) then the DBMS will allow both to commit.
For the complicated-but-necessary truth about locking, you need to read the
DBMS-specific documentation on locking and transaction isolation. For Oracle,
here's a hint: your read can lock a row if it uses the 'FOR UPDATE' syntax.
I'm very sorry I did miss the "for update" words.
Can you review the statement?
In fact we use the row lock of the database to be the distributed lock service.
In order to avoid the single point failure, our application is multiple-instances and only one instance can succeed in grabbing the row lock to do business operations.
It will hold the row lock as long as possible, once it's failed to extend the lock the application will close the connection and exit the JVM at last.
Since all other instances will keep trying to grab the same lock, one of them can also succeed in grabbing the row lock if the previous instance lose the lock.
It works well for a long time while sometimes we see that the lock is not released and all the rest instances are all waiting for grab the lock and none can succeed.
Sometimes it can succeed after 10+ minutes or so.
Hope I clarify our use case this time.