anything here, but not rollback() - WRONG That way the scenario described in this thread can't happen, since the ORA-30006 will trigger the rollback(), thus letting the other session continue. That will always close the transaction when an exception occurs, freeing (among other things) any other session that is waiting on your TX enqueue.
connection.preparecall ("begin storedProcedure. connection.prepareStatement ("update./insert./delete.") To clarify, here's that pseudocode incarnated in Java:
DATABASE DEADLOCK EXCEPTION CODE
It was meant to be pseudocode, not "pl/sql code in a stored procedure", as both you and Tom understood it :) ITL slots simply do not have such information. Timeout information is just unavailable to deadlock detection procedures. Let me rephrase it and please, correct me if I'm wrong: > that information just isn't exposed, this will raise a deadlock. > It doesn't see that one of them will eventually give up trying, Other transactions that have not already requested the rows can request and access the rows immediately."Īnd I think I got your remark: "when you get blocked, you enqueue ON THE TRANSACTION itself".īut this seems to be the answer to my original question: Other transactions that have requested access to rows locked after the savepoint must continue to wait until the transaction is committed or rolled back. Releases all table and row locks acquired since the savepoint. > try this to see what i mean about getting blocked on transactionsĪs for your example with savepoints - yes, there's a corresponding note in SQL reference which explains it: Session1: commit <<< session 2 blocked again, I say again, because it became unblocked on session1 and reblocked by session3.
DATABASE DEADLOCK EXCEPTION UPDATE
Session3: select for update row 2 <<<= will NOT block, the row isn't locked. Session1: rollback to savepoint <<< session2, still blocked. Session2: select for update row 2 <<= becomes blocked on session 1, ON SESSION 1, not row 2 really. Try this to see what i mean about getting blocked on transactions: It doesn't see that one of them will eventually give up trying, that information just isn't exposed, this will raise a deadlock. The concepts guide actually seems to be going out of its way here - "no matter how long". when you get blocked, you enqueue ON THE TRANSACTION itself. It does not matter that one of the sessions would eventually stop blocking the other - that information is not even visible outside of the session itself.įor you see, you are not really blocking "on a row", you are waiting for the transaction to commit. The background processes, every three seconds, will wake up and look for any two sessions blocking eachother - such as session 1 was blocking 2 and 2 was blocking 1.
We have a deadlock there because of the way deadlocks are detected. Well, in our example we are NOT in the situation where "no matter how long each transaction waits, the conflicting locks are held". "It is a deadlock because no matter how long each transaction waits, the conflicting locks are held." It waits for 60 seconds and then gives up with ORA-30006.īut why deadlock? Let me quote "Concepts" once again: Oracle can not obtain lock as it's concurrently held by some other session.
In my opinion, we can expect only two possible outcomes of our requests:Ģ. ORA-00060: deadlock detected while waiting for resourceĪnd that's where my doubts are hidden: do we really have deadlock situation here? In both sessions, we just asked Oracle "hey, give me that row in exclusive mode and if it's already locked wait no more than 60 seconds". ORA-30006: resource busy acquire with WAIT timeout expiredīut as for second session result is a bit surprising (at least for me): However, let's consider the simple testcase (two sessions try to lock rows of the same table):Įventually, we get the following feedback in out first session: It is a deadlock because no matter how long each transaction waits, the conflicting locks are held." Deadlocks prevent some transactions from continuing to work. "A deadlock can occur when two or more users are waiting for data locked by each other. "Oracle Database Concepts 10g" gives us the following explanation of deadlock:
0 kommentar(er)
