Oracle中的僵局 [英] Deadlocks in Oracle
问题描述
我想制作一个脚本，其中进入死锁的oracle会话会被自动杀死.是否有可能找出进入死锁的会话的会话ID.到目前为止，我必须将数据库反弹到消除死锁.可以解决此问题吗?
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会话会被自动杀死
I want to make a script where the oracle sessions which go into a deadlock are killed automatically
编辑，以更好的方式进行了说明，更正了几句话，并添加了一个测试用例来演示死锁情况.
EDIT Explained in a better way, corrected few sentences, and added a test case to demonstrate deadlock scenario.
您为什么要重新发明轮子? Oracle自动检测到死锁，抛出ORA-00060: deadlock detected while waiting for resource，并回滚该死锁中涉及的事务之一，而Oracle认为该事务是受害者.先前的成功事务不会回滚.即使在死锁错误之后，如果发出了提交，则先前的成功事务也将被提交.此时，另一个会话的事务也将成功，并且您可以发出提交.在这里，您无需明确地执行任何操作.死锁会自动清除-您无需清除.
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 clear them.
通常，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
我们来看一个测试用例-
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.
会议: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;
此时，第2届会议一直在等待.
会议:1
12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4;
此时，第2届是僵局的受害者，第1届仍在等待.
At this point, SESSION 2 is the victim of deadlock, SESSION 1 is still waiting.
让我们看一下第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>
因此，在第2届会议(即SID 14)中查看的v$session详细信息表示状态为有效.
So, v$session details when viewed in SESSION 2, i.e. SID 14, says the status is ACTIVE.
让我们看一下另一个会话中的会话详细信息，为方便起见，将其称为第3届.请记住，第1届会议仍在等待.
Let's look at the session details from another session, lets call it SESSION 3 for the sake. Remember, SESSION 1 is still waiting.
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>
因此，对于其他会话，第二届会议(即SID 14)为无效. 第1届会议仍在正在等待事件enq: TX - row lock contention.
So, for other sessions, SESSION 2, i.e. SID 14, is INACTIVE. SESSION 1 is still WAITING with event enq: TX - row lock contention.
让我们提交第2届会议-
12:22:18 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
12:25:43 SQL>
这时，已释放 SESSION 1 的锁，让我们也提交会话1-
At this point, the lock is released for SESSION 1, let's commit session 1 as well -
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显示 SESSION 1 等待了很长时间，直到提交了 SESSION 2 .
Elapsed: 00:08:27.29 shows SESSION 1 was waiting that long till SESSION 2 was committed.
总结一下，这是第一节的全部故事-
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>
总结一下，这是第二节的全部故事-
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只是受害者，而应用程序才是罪魁祸首.死锁令人恐惧，但它们指出了必须早晚纠正的设计缺陷.
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.
这篇关于Oracle中的僵局的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
