死鎖:死鎖是指兩個或兩個以上的程序在執行過程中,由于競争資源或者由于彼此通信而造成的一種阻塞的現象,若無外力作用,它們都将無法推進下去。此時稱系統處于死鎖狀态或系統産生了死鎖,這些永遠在互相等待的程序稱為死鎖程序。
死鎖産生的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
後續方法繼續補充