Database Deadlocks when using Rownum?

Question

If I have the following code being called from multiple threads in an application, is there a deadlock risk? The transaction used to connect to the database for this is opened just before this call and closed once it returns. Application : Java Database: Oracle

  FUNCTION reserveWork(in_batch_id NUMBER,
                       in_work_size NUMBER,
                       in_contentType_id NUMBER) RETURN NUMBER IS
    rows_reserved NUMBER := 0;

  BEGIN
    UPDATE
          D_Q1
    SET
          DQ1_BAT_ID = in_batch_id
    WHERE
         DQ1_BAT_ID is null
         AND DCT_ID = in_contentType_id
         AND ROWNUM < (in_work_size + 1);

    rows_reserved := SQL%ROWCOUNT;

    RETURN (rows_reserved);

  END;

Answer 1

There is a definite deadlock risk if you're running multiple UPDATEs on the same table.

Particularly since I can't see a COMMIT or ROLLBACK in your code? I presume this is done in the JDBC?

The longer the UPDATE takes, the higher the deadlock risk will be.

Answer 2

In order for deadlock to happen you must have these two conditions.

1. Each transaction must multiple locks.

2. The locks must be grabbed in a different order.

Condition 1 is true because each of your threads locks multiple rows. Condition 2 is true in theory because the order of the rows returned is not deterministic. For example, thread 1 might try to update rows 1,2,3 and thread 2 might try to update rows 3,2,1.

In practice, Oracle might always return the rows in the same order so it might never deadlock. Anyway, be prepared to handle the ORA-00060 error and resubmit the request.

Another idea is to do this in two steps. The first procedure does a SELECT * WHERE ... FOR UPDATE NO WAIT to lock the rows, if that doesn't return an ORA-00054, the second procedure does the actual update. Otherwise you retry.

Either way, make sure you have INITTRANS in your CREATE TABLE set to as many clients as will be simultaneously updating the table.

Answer 3

A deadlock occurs when transaction A locks a record then has to wait for transaction B to unlock a record, while transaction B is waiting on a record already locked by transaction A.

Oracle has a pretty sophisticated mechanism for handling changes to tables during the course of an update. See

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:11504247549852

Generally, the risk of a deadlock increases the longer a transaction runs and the more data a transaction changes. I'd say this is unlikely to deadlock, but is likely to 'queue' - if you have three or four concurrent sessions running this SQL, each session will have the same execution path for the SQL, will identify the same rows for update, one will get to them first, the others will wait. When that first transaction completes, another will re-grab the records, find they are changed, and restart as described in Tom Kyte's article and select the next bunch of rows.

If you are on 11g, there is a SKIP LOCKED you can use. It is present, but undocumented, in earlier versions. So there it would be USE AT YOUR OWN RISK.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF01702

In that way, you'd

SELECT primary_key BULK COLLECT INTO pk_variable_array FROM D_Q1
WHERE    DQ1_BAT_ID is null
AND DCT_ID = in_contentType_id
AND ROWNUM < (in_work_size + 1)
FOR UPDATE SKIP LOCKED;
--
FORALL i in 1..pk_variable_array
 UPDATE D_Q1
 SET DQ1_BAT_ID = in_batch_id
 WHERE primary_key = pk_variable_array(i)