Oracle选择Order By更新DeadLock

Question

我收到了以下oracle日志：

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00120007-0002acbf        82     185     X             67     240           X
TX-0008001c-0009ed94        67     240     X             82     185           X

session 185: DID 0001-0052-00000004 session 240: DID 0001-0043-00000004 
session 240: DID 0001-0043-00000004 session 185: DID 0001-0052-00000004 

Rows waited on:
  Session 185: obj - rowid = 0001285C - AAAShcAAiAAEoVFAAi
  (dictionary objn - 75868, file - 34, block - 1213765, slot - 34)
  Session 240: obj - rowid = 0001285C - AAAShcAAiAAEoRWAA3
  (dictionary objn - 75868, file - 34, block - 1213526, slot - 55)

----- Information for the OTHER waiting sessions -----
Session 240:
  sid: 240 ser: 7 audsid: 32762787 user: 76/NETCRACKER1
    flags: (0x8000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 67 O/S info: user: oracle, term: UNKNOWN, ospid: 26869908
    image: oracle@opipodb1
  client details:
    O/S info: user: adipoas1, term: unknown, ospid: 1234
    machine: opipoas1 program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  select o.object_id
  from nc_objects o,
       (select object_id
          from nc_objects
        connect by prior object_id = parent_id
         start with object_id = :a1
        union
        select object_id
          from nc_references
         where reference in (select object_id
                               from nc_objects
                             connect by prior object_id = parent_id
                              start with object_id = :a1)) ids
 where o.object_id = ids.object_id
 order by o.object_id
   for update of o.object_id

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=3mxkkzhttwa2q) -----
select o.object_id
  from nc_objects o,
       (select object_id
          from nc_objects
        connect by prior object_id = parent_id
         start with object_id = :a1
        union
        select object_id
          from nc_references
         where reference in (select object_id
                               from nc_objects
                             connect by prior object_id = parent_id
                              start with object_id = :a1)) ids
 where o.object_id = ids.object_id
 order by o.object_id
   for update of o.object_id
===================================================

这是否意味着两个相等的查询(但可能包含不同的数据)相互锁定？

如果是这样，它怎么会发生呢？-这个查询锁定了有序序列，我想我们在这里不能有死锁。

DB是元模型object_id - PK

谢谢

Answer 1

ORDER BY will not always prevent deadlocks。

下面是一个可能导致查询死锁的场景：

死锁会话1使用:id=A

• Session运行查询2使用:id=B

• Session运行查询1使用:id=B运行查询，会话1现在等待会话2

• 会话2使用:id=A运行查询，已发出死锁

当您发出SELECT FOR UPDATE命令时，您将面临等待另一个会话的风险。如果此会话已在等待您，则会出现死锁。

在OLTP环境中避免死锁的一种常见方法是只发出SELECT FOR UPDATE NOWAIT。让第二个会话失败，而不是等待。

更新下面的评论

您有多个会话在更新同一个表。你有机会让他们互相阻挡。如果您让它们等待，而它们更新了多个行，则有可能发生死锁。您有许多选项来管理这一点：

1. 让会话死锁。Oracle将自动检测到这一点，并回滚一个。Oracle will only rollback the last statement of one of the session, not the whole transaction，这意味着出现错误的会话应该先使用回滚，然后再重试。
2. 按照我的建议使用NOWAIT运行它们。

如果你不想让它们失败，你可以：

1. lock row with NOWAIT 
2. if ORA-56 **rollback** 
3. retry from the beginning of the transaction after sleep (X seconds). 

您需要回滚，以确保两个会话不会无限期地互相阻塞。

1. 序列化更新。这通常是通过确保只有一个会话可以同时更新表(或一系列行)来完成的。

例如，您可以让所有会话插入到事务表中，并让单个作业将修改合并到最终表中。并发会话不能互相阻塞。这显然会推迟对主表的更新(至少在提交之后)。

我不建议选择1，因为您无法控制死锁检测(因此您无法对其进行优化)。