(Session 1 still hangs while waiting for Session 2 to COMMIT or ROLLBACK the first row that was inserted)

In Session 2 (confirming that the session was not killed/terminated and that the first row inserted still exists – indicates that the current TRANSACTION was not rolled back, just the statement that was involved in the deadlock):

SELECT
*
FROM
T1;
C1 C2
---------- ----------
2 2

While recently reading an OTN thread I was reminded of a bit of information that several sources simply miss-state about Oracle deadlocks. The incorrect information is repeated, seemingly quite frequently, in various books and forum threads. As luck would have it, several of the books that I have read about Oracle Database also repeat this incorrect information. For instance, a quote from page 352 of the book “Expert Oracle Database 11g Administration” (a book that I reviewed and gave 4 out of 5 stars, maybe I need to re-evaluate that rating?):

“This is a catch-22 situation, because the stalemate can’t be broken by either session unilaterally. In such circumstances, Oracle steps in, terminates one of the sessions, and rolls back its statement. Oracle quickly recognizes that two sessions are deadlocked and terminates the session that issued the most recent request for a lock. This will release the object locks that the other session is waiting for.”

A second book, “Secrets of the Oracle Database” (another book that I reviewed and gave 4 out of 5 stars, actually it was probably worth about 4.4 stars), states the following on page 57:

“Hence, the ORACLE DBMS detects circular chains pertaining to interdependent locks, signals the error ‘ORA-00060: deadlock detected while waiting for resource’, and rolls back one of the sessions involved in the would-be deadlock.”

“The immediate solution to the problem requires no user intervention: Oracle detects the deadlock and automatically rolls back one of the transactions, releasing one of the sets of row locks so that the other session’s DML will complete.”

“While the database can automatically resolve deadlocks between sessions (by killing one of the sessions holding a needed lock)…”

The Google book search found that the above four books were not alone in their statements of deadlocks that do not match the test case results at the start of this blog article. One of the books found by Google is one that I have not read yet (and probably never will), “Oracle PL/SQL for Dummies”, which states the following on page 298:

“This is a deadlock. Oracle resolves it by raising a deadlock exception (ORA-00060: deadlock detected while waiting for resource) in user 1’s session. This terminates the procedure and allows user 2’s procedure to successfully complete.”

A second book found by the Google search is a bit old, “Oracle 9i DBA 101″, which on page 194 states:

“A deadlock occurs when two or more users wait for data that is locked by each other. Oracle will kill the first server process that detects the deadlock, so you should always look for and fix code that can cause deadlocks.”

There are certainly many other statements in other books regarding deadlocks, both correct and incorrect. A quick search through the OTN forums found several threads in addition to the one at the start of this blog article that also drifted a bit from an accurate picture of what happens when a deadlock is broken in Oracle Database.

“The two session are inserting records in the same table with a unique index on it. With the same database version on another server and the same treatment, the deadlocks are correctly handled (one session is killed with ora-00060) and the process can continue.”

While reading the book “Oracle Core Essential Internals for DBAs and Developers” I almost flagged a sentence in a discussion of deadlocks as potentially containing an error. That statement is found on page 82 near the end of the third to the last paragraph on the page. After reading that paragraph about 10 times I decided that the statement is probably specific to TM locks, and probably is correct (the thought of TX locks maintained at the individual block level that contains the table rows kept running through my head the first nine times I read the paragraph). This section of the book is interesting, as it not only avoids the mistakes mentioned in the above books, but it also highlights an inaccurate statement or two found in the Oracle Database documentation.

Actions

Information

10 responses

I wonder if the misconception arises because of the way some client applications handle the error. A badly coded application which crashes on receiving the ORA-00060 usually because there is poor or no error handling could lead to the assumption that oracle has killed the session rather than the client application crashing. We have a wonderful third party application here that on any ORA-* message crashes and dumps a stack trace, as the trace then shows the crash occurred during a database call its the DBA’s fault not the developers.

In the third OTN link you’ve listed, I gave the following answer
”
When deadlock occurs Oracle will not roll back the entire transaction. It will only do a rollback of one of the statements (belonging to one of the sessions) involded in the deadlock. The other session remains blocked if the session for which one of the statement has been rolled back by Oracle will not commit or rollback.

In other words, in the Justin Cave example, Session 1 will remains blocked if Session 2 will not issue a rollback after it has received the deadlook error

So, the OP should verify that his application must forsee a rollback in case of exception (and the deadlock error could be one of those exceptions)

There is also a misconception about oracle choosing ARBITRARILY one of the two sessions involved in the deadlocks and rollback one of its statement. The choosen session is not done arbitrarily but it seems that it is the session that start first waiting

Another piece of the puzzle. Jonathan mentioned something similar in the Oracle Core book – and his writing style made it seem so obvious that it works as he indicated, that I had a hard time understanding why I did not understand the reasoning earlier. :-)

Please keep in mind that a TM enqueue will not necessarily lead to a deadlock, and that a deadlock involving a TM equeue will not kill the sessions that ends up being the deadlock “victim” (this might not have been clear because of all of the incorrect quotes from the various books).

I had a little bit of trouble when I tried to recreate your test case:
In Session 1:

The above shows that Session 2 was still connected to the database, and that the first row inserted by Session 2 was not rolled back by an automatic transaction rollback (there was an automatic statement-level rollback).

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: