A Three Rows Deadlock

Well, it just happens too many times, way too many times... This post does not explain anything in details, it just shows the code and presents a quotation from the official Oracle RDBMS documentation. I use 10.2.0.3 version of Oracle RDBMS.

Here we go... Let's suppose we've got a table. It has two column one is a numeric — an identifier, the other one is a date when the row was inserted or modified. For clarity and simplicity I don't use any other columns. So, here is what we have:

CREATE TABLE t (id, dt)
AS
  SELECT 1, SYSDATE
    FROM dual
   UNION ALL
  SELECT 2, SYSDATE + 1
    FROM dual
/

A simple table with two rows in it. Nothing fancy.

We also have to define some simple operations. From time to time we want to get some rows from this table for update purposes. To make sure we don't introduce any deadlocks we order them by date.

SELECT id, dt
  FROM t
 ORDER BY dt
   FOR UPDATE
/

Also, from time to time people come and do modifications, or insert new rows, for instance:

UPDATE t
   SET dt = dt + 1
 WHERE id = 2
/

or

INSERT INTO t VALUES(3, SYSDATE - 1)
/

Again, nothing fancy. The fancy part comes now. Sit tight! Let's simulate the process here:

SESSION#1:
UPDATE t
   SET dt = dt + 1
 WHERE id = 2
/

SESSION#2:
SELECT id, dt
  FROM t
 ORDER BY dt
   FOR UPDATE
/

SESSION#3:
INSERT INTO t VALUES(3, SYSDATE - 1)
/
COMMIT
/
SELECT id, dt
  FROM t
 ORDER BY dt
   FOR UPDATE
/

After this point we have two sessions #2 and #3 blocked.

The show is about to begin! If issue COMMIT in the session #1 we see something like this:

SESSION#2:
        ID DT
---------- ---------
         3 10-SEP-07
         1 11-SEP-07
         2 13-SEP-07

SESSION#3:
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

Ta-da! We got a deadlock even we used a legitimate SELECT FOR UPDATE statement. Why? Let's read the documentation:

The return set for a SELECT... FOR UPDATE may change while the query is running; for example, if columns selected by the query are updated or rows are deleted after the query started. When this happens, SELECT... FOR UPDATE acquires locks on the rows that did not change, gets a new read-consistent snapshot of the table using these locks, and then restarts the query to acquire the remaining locks.

This can cause a deadlock between sessions querying the table concurrently with DML operations when rows are locked in a non-sequential order. To prevent such deadlocks, design your application so that any concurrent DML on the table does not affect the return set of the query. If this is not feasible, you may want to serialize queries in your application.

Life is cruel.