Oracle中的死锁

Question

我想创建一个脚本，其中进入死锁的oracle会话会自动终止。有可能找到进入死锁的会话的会话ID。截至目前，我必须反弹数据库来消除死锁。这个问题的解决方案有可能吗？

Answer 1

我想使凡进入死锁在Oracle会话被杀害自动

编辑以更好的方式解释，修正一些句子的脚本，并添加了一个测试案例展示死锁情况。

为什么你想重新发明*？ Oracle自动检测到死锁，引发ORA-00060: deadlock detected while waiting for resource，并回滚Oracle确定为受害者的死锁中涉及的其中一个事务。以前成功的事务不会回滚。即使在死锁错误之后，如果发出提交，则前一个成功的事务将被提交。此时，其他会话的事务也会成功，您可以发出提交。这里没有什么需要明确地做。死锁会自动清除 - 你永远不需要明确他们。

通常，Oracle需要一两秒钟来检测死锁并引发错误。

你可以用一个简单的测试情况下，尝试为证明此：Understanding Oracle Deadlock

让我们来看看在测试案例 -

SQL> CREATE TABLE t_test(col_1 NUMBER, col_2 NUMBER); 

Table created 
SQL> INSERT INTO t_test VALUES(1,2); 

1 row inserted 
SQL> INSERT INTO t_test VALUES(3,4); 

1 row inserted 

SQL> COMMIT; 

Commit complete 

SQL> SELECT * FROM t_test; 

    COL_1  COL_2 
---------- ---------- 
     1   2 
     3   4 

注意每一笔交易的时候，我已经设置时间定时对更好的理解。

会话：1

12:16:06 SQL> UPDATE t_test SET col_1 = 5 WHERE col_2=2; 

1 row updated. 

Elapsed: 00:00:00.00 

会议：2

12:16:04 SQL> UPDATE t_test SET col_1 = 6 WHERE col_2=4; 

1 row updated. 

Elapsed: 00:00:00.00 
12:16:31 SQL> UPDATE t_test SET col_1 = 7 WHERE col_2=2; 

在这一点上，SESSION 2继续等待。

会话：1

12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4; 

在这一点上，SESSION 2死锁的牺牲品，SESSION 1仍在等待。

让我们看看会议的细节，从SESSION 2 -

12:22:15 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe'; 

     SID STATUS PROGRAM   SQL_ID  STATE    WAIT_CLASS  BLOCKING_SE EVENT 
---------- -------- --------------- ------------- ------------------- --------------- ----------- ---------------------------------------------------------------- 
     14 ACTIVE sqlplus.exe  60qmqpmbmyhxn WAITED SHORT TIME Network   NOT IN WAIT SQL*Net message to client 
     134 ACTIVE sqlplus.exe  5x0zg4qwus29v WAITING    Application  VALID  enq: TX - row lock contention 

Elapsed: 00:00:00.00 
12:22:18 SQL> 

因此，在SESSION 2看v$session细节时，即SID 14，表示状态为ACTIVE。

让我们看看来自另一个会话的会话详细信息，为此我们称之为SESSION 3。请记住，SESSION 1仍在等待。

SQL> set time on timing on 
12:24:41 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe' 

     SID STATUS PROGRAM   SQL_ID  STATE    WAIT_CLASS BLOCKING_SE EVENT 
---------- -------- --------------- ------------- ------------------- ---------- ----------- ------------------------------ 
     13 ACTIVE sqlplus.exe  60qmqpmbmyhxn WAITED SHORT TIME Network NOT IN WAIT SQL*Net message to client 
     14 INACTIVE sqlplus.exe     WAITING    Idle  NO HOLDER SQL*Net message from client 
     134 ACTIVE sqlplus.exe  5x0zg4qwus29v WAITING    Applicatio VALID  enq: TX - row lock contention 
                     n 


Elapsed: 00:00:00.01 
12:24:44 SQL> 

所以，对于其他会话，SESSION 2，即SID 14，是INACTIVE。 SESSION 1仍然是等待与事件enq: TX - row lock contention。

让我们犯SESSION 2 -

12:22:18 SQL> commit; 

Commit complete. 

Elapsed: 00:00:00.01 
12:25:43 SQL> 

在这一点上，锁被释放SESSION 1，让我们提交会议1以及 -

12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4; 

1 row updated. 

Elapsed: 00:08:27.29 
12:25:43 SQL> commit; 

Commit complete. 

Elapsed: 00:00:00.00 
12:26:26 SQL> 

Elapsed: 00:08:27.29显示会议1正在等待，直到SESSION 2已提交。

总之，这里是会议的整个故事1 -

12:16:06 SQL> UPDATE t_test SET col_1 = 5 WHERE col_2=2; 

1 row updated. 

Elapsed: 00:00:00.00 
12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4; 

1 row updated. 

Elapsed: 00:08:27.29 
12:25:43 SQL> commit; 

Commit complete. 

Elapsed: 00:00:00.00 
12:26:26 SQL> 

要在这里总结一下，是会话的整个故事2 -

12:16:04 SQL> UPDATE t_test SET col_1 = 6 WHERE col_2=4; 

1 row updated. 

Elapsed: 00:00:00.00 
12:16:31 SQL> UPDATE t_test SET col_1 = 7 WHERE col_2=2; 
UPDATE t_test SET col_1 = 7 WHERE col_2=2 
            * 
ERROR at line 1: 
ORA-00060: deadlock detected while waiting for resource 


Elapsed: 00:00:24.47 
12:22:15 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe'; 

     SID STATUS PROGRAM   SQL_ID  STATE    WAIT_CLASS  BLOCKING_SE EVENT 
---------- -------- --------------- ------------- ------------------- --------------- ----------- ---------------------------------------------------------------- 
     14 ACTIVE sqlplus.exe  60qmqpmbmyhxn WAITED SHORT TIME Network   NOT IN WAIT SQL*Net message to client 
     134 ACTIVE sqlplus.exe  5x0zg4qwus29v WAITING    Application  VALID  enq: TX - row lock contention 

Elapsed: 00:00:00.00 
12:22:18 SQL> commit; 

Commit complete. 

Elapsed: 00:00:00.01 
12:25:43 SQL> 

现在，让我们来看看哪一笔交易实际回滚，哪笔交易 -

12:25:43 SQL> select * from t_test; 

    COL_1  COL_2 
---------- ---------- 
     5   2 
     8   4 

Elapsed: 00:00:00.00 
12:30:36 SQL> 

结论

在我看来，要知道死锁的会议细节的最好办法是登录细节的详细越好。否则，如果没有正确的信息记录，这对DBA来说是一场噩梦。对于这个问题，即使是开发人员也会发现，如果死锁错误的详细信息没有被记录为详细的信息，那么纠正和修复实际设计缺陷是一项艰巨的任务。并且以单线程语句结束，一个僵局是由于设计缺陷造成的，Oracle只是受害者，应用程序是罪魁祸首。僵局是可怕的，但他们指出了迟早必须纠正的设计缺陷。

Answer 2

用户1

update table_c set id = 200 where id = 13; 
BEGIN 
DBMS_LOCK.sleep(14); 
END; 
/
update table_c set id = 200 where id = 15; 

用户2

update table_c set id = 2000 where id = 15; 

BEGIN 
DBMS_LOCK.sleep(14); 
END; 
/

update table_c set id = 1000 where id = 13;