使用Rownum时数据库死锁？

Question

如果我从应用程序中的多个线程调用以下代码，是否存在死锁风险？用于连接到数据库的事务在此调用之前打开，并在它返回后关闭。应用程序: Java数据库: 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

为了使死锁发生，你必须满足这两个条件。

1. 每个事务必须有多个锁。
2. 锁必须以不同的顺序抓取。

条件1为真，因为每个线程都会锁定多个行。条件2在理论上是真的，因为返回的行的顺序不是确定性的。例如，线程1可能尝试更新行1、2、3，而线程2可能尝试更新行3、2、1。

在实践中，Oracle可能总是以相同的顺序返回行，因此它可能永远不会死锁。无论如何，准备好处理ORA-00060错误并重新提交请求。

另一个想法是分两步完成这项工作。第一个过程执行SELECT * WHERE ...FOR update NO WAIT锁定行，如果没有返回ORA-00054，则第二个过程执行实际的更新。否则，请重试。

无论采用哪种方法，请确保将CREATE table中的INITTRANS设置为与将同时更新表的客户机数相同的数量。

Answer 2

如果在同一个表上运行多个更新，肯定会有死锁风险。

尤其是因为我在你的代码中看不到提交或回滚？我假设这是在JDBC中完成的？

更新花费的时间越长，死锁风险就越高。

Answer 3

当事务A锁定一条记录，然后必须等待事务B解锁一条记录，而事务B正在等待事务A已经锁定的记录时，就会发生死锁。

Oracle有一种非常复杂的机制，用于在更新过程中处理表的更改。看见

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

通常，事务运行的时间越长，更改的数据越多，死锁的风险就越大。我想说这不太可能死锁，但很可能会“排队”-如果你有三到四个并发会话运行这个SQL，每个会话将有相同的SQL执行路径，将标识相同的行进行更新，一个将首先到达它们，其他的将等待。当第一个事务完成时，另一个事务将重新获取记录，发现它们已更改，然后重新启动，如Tom Kyte的文章所述，并选择下一组行。

如果你使用的是11g，你可以使用一个跳过锁。它在早期版本中存在，但没有文档记录。所以它的使用风险由你自己承担。

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

这样的话，你会

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)