Oracle 中的死锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28455231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deadlocks in Oracle
提问by user1
I want to make a script where the oracle sessions which go into a deadlock are killed automatically.Is it possible to find out the session id for the sessions which go into a deadlock.As of now I have to bounce the database to remove the deadlock.Is any solution to this problem possible?
我想制作一个脚本，其中进入死锁的 oracle 会话会自动终止。是否可以找出进入死锁的会话的会话 ID。截至目前，我必须退回数据库以消除死锁. 有没有可能解决这个问题？
回答by Lalit Kumar B
I want to make a script where the oracle sessions which go into a deadlock are killed automatically
我想制作一个脚本，其中进入死锁的 oracle 会话会自动终止
EDITExplained in a better way, corrected few sentences, and added a test case to demonstrate deadlock scenario.
EDIT以更好的方式解释，更正了几句话，并添加了一个测试用例来演示死锁场景。
Why do you want to re-invent the wheel? Oracle detects a deadlock automatically, throws ORA-00060: deadlock detected while waiting for resource, and rolls back one of the transactions involved in the deadlock which Oracle decided as the victim. The previous successful transactions are not rolled back. Even after the deadlock error, if a commit is issued, the previous successful transaction will be committed. At this time, the other session's transaction will also succeed and you could issue a commit.There is nothing that you need to explicitly do here. Deadlocks are automatically cleared -- you never need to clearthem.
为什么要重新发明轮子？Oracle 自动检测到死锁，抛出ORA-00060: deadlock detected while waiting for resource并回滚 Oracle 决定为受害者的死锁中涉及的事务之一。先前成功的事务不会回滚。即使在死锁错误之后，如果发出提交，也会提交先前成功的事务。此时，另一个会话的事务也会成功，您可以发出提交。您无需在此处明确执行任何操作。死锁会自动清除——您永远不需要清除它们。
Usually, Oracle takes a second or two to detect a deadlock and throws the error.
通常，Oracle 需要一两秒钟来检测死锁并抛出错误。
You can try with a simple test case as demonstrated here : Understanding Oracle Deadlock
您可以尝试使用一个简单的测试用例，如下所示：Understanding Oracle Deadlock
Let's look at a test case -
让我们看一个测试用例——
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
Note the time of each transaction, I have set time on timing on for a better understanding.
注意每笔交易的时间，为了更好地理解，我已经设置了时间。
SESSION : 1
会话 : 1
12:16:06 SQL> UPDATE t_test SET col_1 = 5 WHERE col_2=2;
1 row updated.
Elapsed: 00:00:00.00
SESSION : 2
会话 : 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;
At this point, SESSION 2 keeps waiting.
此时，SESSION 2 一直在等待。
SESSION : 1
会话 : 1
12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4;
At this point, SESSION 2is the victim of deadlock, SESSION 1is still waiting.
此时，SESSION 2是死锁的牺牲品，SESSION 1还在等待。
Let's look at the session details from SESSION 2-
让我们看看来自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>
So, v$sessiondetails when viewed in SESSION 2, i.e. SID 14, says the status is ACTIVE.
因此，v$session在SESSION 2 中查看的详细信息，即 SID 14，表示状态为ACTIVE。
Let's look at the session details from another session, lets call it SESSION 3for the sake. Remember, SESSION 1is still waiting.
让我们看看另一个会话的会话详细信息，我们将其称为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>
So, for other sessions, SESSION 2, i.e. SID 14, is INACTIVE. SESSION 1is still WAITINGwith event enq: TX - row lock contention.
因此，对于其他会话，SESSION 2，即 SID 14 是INACTIVE。SESSION 1仍在等待event enq: TX - row lock contention。
Let's commit SESSION 2-
让我们提交SESSION 2-
12:22:18 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
12:25:43 SQL>
At this point, the lock is released for SESSION 1, let's commit session 1 as well -
此时，释放了SESSION 1的锁，让我们也提交 session 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.29shows SESSION 1was waiting that long till SESSION 2was committed.
Elapsed: 00:08:27.29显示SESSION 1等待了那么久，直到SESSION 2被提交。
To summarize, here is the entire story of session 1-
总而言之，这是会话 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>
To summarize, here is the entire story of session 2-
总而言之，这是第 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>
Now, let's see which transaction actually got rolled back and which got committed-
现在，让我们看看哪个事务实际上被回滚了，哪个被提交了——
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>
Conclusion
结论
In my opinion, the best way to know the session details of a deadlock is to log the details as verbose as possible. Else, it is a nightmare for a DBA to investigate without proper information logged. For that matter, even a Developer would find it to be an herculean task to rectify and fix the actual design flaw if the deadlock error details are not logged verbosely. And to conclude with a one liner statement, A deadlock is due to design flaw, Oracle is just the victim and the application being the culprit. Deadlocks are scary, but they point out the design flaws that must be rectified sooner or later.
在我看来，了解死锁会话详细信息的最佳方法是尽可能详细地记录详细信息。否则，在没有记录正确信息的情况下进行调查对 DBA 来说是一场噩梦。就此而言，如果没有详细记录死锁错误详细信息，即使是开发人员也会发现纠正和修复实际设计缺陷是一项艰巨的任务。并以单行语句结束，死锁是由于设计缺陷造成的，Oracle 只是受害者，而应用程序是罪魁祸首。死锁很可怕，但它们指出了迟早必须纠正的设计缺陷。
回答by zloctb
user 1
用户 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;
user 2
用户 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;
