ORA-00060: Deadlock detected 模擬死鎖產生與解決方案

我愛睡蓮發表於2020-09-27

死鎖：死鎖是指兩個或兩個以上的程式在執行過程中，由於競爭資源或者由於彼此通訊而造成的一種阻塞的現象，若無外力作用，它們都將無法推進下去。此時稱系統處於死鎖狀態或系統產生了死鎖，這些永遠在互相等待的程式稱為死鎖程式。

死鎖產生的4個必要條件

產生死鎖的必要條件：

  1. 互斥條件：程式要求對所分配的資源進行排它性控制，即在一段時間內某資源僅為一程式所佔用。
  2. 請求和保持條件：當程式因請求資源而阻塞時，對已獲得的資源保持不放。
  3. 不剝奪條件：程式已獲得的資源在未使用完之前，不能剝奪，只能在使用完時由自己釋放。
  4. 環路等待條件：在發生死鎖時，必然存在一個程式--資源的環形鏈。

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

環境介紹：

使用者test01 建立表tab01，使用者test02建立表tab02。Test01 更新tab01不提交，test02 更新表tab02不提交。然後test01 更新test02下的表tab02，此時有鎖阻塞、鎖等待情況發生。接著test02 更新test01下的tab01，那麼此時就會有 test01、test02 都在等待對方的資源，但是資源無法釋放，滿足死鎖條件死鎖產生

建立test01使用者：

SQL>  create user test01 identified by test01;

User created.

SQL> grant create session,resource,dba to test01;

Grant succeeded.

SQL> conn test01/test01;
Connected.
SQL>  create table tab01 (id number);

Table created.

SQL>  insert into tab01 values(01);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tab01;

    ID
----------
     1

建立test02使用者：

SQL> create user test02 identified by test02;

User created.

SQL> grant create session,resource,dba to test02;

Grant succeeded.

SQL> conn test02/test02;
Connected.
SQL> create table tab02 (id number);

Table created.

SQL>  insert into tab02 values(02);

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from tab02;

    ID
----------
     2

製造死鎖：

test01：

SQL> update tab01 set id=id*1;

1 row updated.

SQL> update test02.tab02 set id=id*1;
update test02.tab02 set id=id*1
              *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

test02：

SQL> update tab02 set id=id*1 ;

1 row updated.

SQL> update test01.tab01 set id=id*1;
此時一直會hang住

檢視oracle後臺日誌，存在ORA-00060報錯

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_3692.trc.

 

解決方案：

在我們知道是何原因引起死鎖的時候，可以將一個使用者的資料進行提交，可以解決死鎖問題，但是如果實際生產過程中，使用者以及應用產生的死鎖，我們不清楚就需要去查詢原因了。

1.使用PLSQL圖形工具解決死鎖

點選工具->會話

 

所有會話：

 

 找到Blocking session status列，如果看到有valid的行，就說明存在死鎖，點選箭頭方向，killed掉死鎖，然後進行會話重新整理，就可以看到死鎖被釋放掉

 

 點選該行，可以看到具體的SQL語句

 

 kill掉該死鎖重新整理，就看不到該會話

後臺語句

 

 

2.使用sqlplus進行語句查詢

重新制造死鎖：

SQL> update test02.tab02 set id=id*1;
update test02.tab02 set id=id*1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

查詢資料庫中存在的死鎖：

col BLOCKER for a20
col BLOCKEE for a15
select
(select username from v$session where sid=a.sid) blocker,a.sid,'is blocking',
  (select username from v$session where sid=b.sid) blockee,b.sid
   from v$lock a,v$lock b
    where a.block=1
     and b.request>0
  and a.id1=b.id1
  and a.id2=b.id2;

 

 

使用SQL語句生成殺鎖會話方法：

col machine for a30
col kill_command for a50
col object_name for a20
col osuser for a15
col oracle_username for a30
set lines 400
select s.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode,s.osuser,
    'alter system kill session '''|| s.sid || ', '|| s.serial#||''';' as kill_command
from v$locked_object l,v$session s, all_objects o ,v$lock k
where k.block=0 and s.sid=k.sid and l.locked_mode>2 and  l.session_id=s.sid and l.object_id=o.object_id order by 3;

 

 

 根據sid檢視鎖會話sql語句：

select a.sid,a.event,a.sql_id,a.sql_child_number,b.sql_text
from v$session a,v$sql b
where a.sql_address=b.address
and a.sql_hash_value=b.HASH_VALUE
and a.sql_child_number=b.child_number
order by 1 desc;

 

 根據SQL語句生成殺鎖會話方法給出的KILL_COMMAND

SQL> alter system kill session '70, 63';

System altered.

然後sqlplus中行住的SQL語句被強制結束：

 

 觀察後臺日誌，ospid (4064)作業系統4064號程式被強制終止。

Sun Sep 27 15:03:38 2020
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_3692.trc.
Sun Sep 27 15:10:25 2020
opiodr aborting process unknown ospid (4064) as a result of ORA-28

後續方法繼續補充

 

相關文章