|
oracle 死鎖和鎖等待的區(qū)別(轉(zhuǎn)載)
所謂的鎖等待:就是一個(gè)事務(wù)a對(duì)一個(gè)數(shù)據(jù)表進(jìn)行ddl或是dml操作時(shí),，系統(tǒng)就會(huì)對(duì)該表加上表級(jí)的排它鎖,，此時(shí)其他的事務(wù)對(duì)該表進(jìn)行操作的時(shí)候會(huì)等待a提交或是回滾后,，才可以繼續(xù)b的操作 所謂的死鎖：當(dāng)兩個(gè)或多個(gè)用戶相互等待鎖定的數(shù)據(jù)時(shí)就會(huì)發(fā)生死鎖,，這時(shí)這些用戶被卡在不能繼續(xù)處理業(yè)務(wù),，oracle可以自動(dòng)檢測(cè)死鎖并解決他們，通過(guò)回滾一個(gè)死鎖中的語(yǔ)句,，釋放鎖定的數(shù)據(jù),，回滾的話會(huì)遇到ora-00060 deadlock detected while waiting for resource 模擬鎖等待： 兩個(gè)事務(wù)a和b，分別創(chuàng)建t1,t2,并且初始化一條數(shù)據(jù),， a 更改t1的數(shù)據(jù),，此時(shí)并不提交，這時(shí)候b更改相同的一列,，此時(shí)b一直處于等待的狀態(tài) 我們可以查詢鎖等待的內(nèi)容： wait_lock.sql select (select username from v$session where sid = a.sid) username, a.sid, (select serial# from v$session where sid = a.sid) serial#, a.type, a.id1, a.id2, a.lmode, a.request, a.block, b.sid blocking_sid from v$lock a, ( select * from v$lock where request > 0 and type <> 'MR' ) b where a.id1 = b.id1(+) and a.id2 = b.id2(+) and a.lmode > 0 and a.type <> 'MR' order by username,a.sid,serial#,a.type 此時(shí)可以查詢到鎖等待的現(xiàn)象,，最后一列不為空的就是等待的事件 此時(shí)我們可以跟a用戶提示讓其提交事務(wù)或是回滾，也可以直接殺死 alter system kill session 'sid,serial#'; 保持現(xiàn)狀不動(dòng),，在a事務(wù)更改t2表此時(shí)在a事務(wù)會(huì)產(chǎn)生 SQL> update t1 set id=1000 where id=1; update t1 set id=1000 where id=1 * 第 1 行出現(xiàn)錯(cuò)誤: ORA-00060: 等待資源時(shí)檢測(cè)到死鎖 此時(shí)oracle已經(jīng)幫我解決了這個(gè)死鎖問(wèn)題 死鎖的產(chǎn)生需要四個(gè)必須的條件： 1 ,，mutual execution(互斥)資源不能被共享，只能由一個(gè)進(jìn)程使用 2,，hold and wait(請(qǐng)求并持續(xù))已經(jīng)得到資源的進(jìn)程可以再次申請(qǐng)新的資源 3,，no pre-emption(不可剝奪)已經(jīng)分配的資源不能被相應(yīng)的進(jìn)程強(qiáng)制剝奪 4，circular wait(循環(huán)等待條件)系統(tǒng)中若干進(jìn)程組成環(huán)路，該環(huán)路中的每個(gè)進(jìn)程都在等待相鄰進(jìn)程正占用的資源 定位死鎖： 系統(tǒng)級(jí)別的定位 Select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object) Username死鎖的用戶,，lockwait死鎖的狀態(tài),，status中active表示死鎖，machine死鎖所在的機(jī)器,，program死鎖來(lái)自于那個(gè)程序 語(yǔ)句級(jí)別的定位 Select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object)); 進(jìn)程級(jí)別的定位 Select s.username,l.object_id,l.session_id,s.serial#,l.oracle_usrename,l.os_user_name,l.process from v$locked_object l,v$session s where l.session_id=s.sid; 處理死鎖的一般策略 1,，鴕鳥算法忽略該問(wèn)題 2，定位死鎖并且恢復(fù) 3,，仔細(xì)對(duì)資源進(jìn)行動(dòng)態(tài)分配,，避免死鎖 4，破壞死鎖中的一個(gè)條件 如果oracle解決不了的死鎖,，我們需要定位到進(jìn)程級(jí)別,，找到對(duì)應(yīng)的sid和serial# alter system kill 'sid,serail#' 失敗的話，找到對(duì)應(yīng)的進(jìn)程強(qiáng)制關(guān)閉 Select p.spid from v$session s, v$process p where s.sid=xx and s.paddr=p.addr ps -ef | grep spid kill -9 xx 查詢oracle的死鎖 lock.sql SELECT bs.username "Blocking User", bs.username "DB User", ws.username "Waiting User", bs.SID "SID", ws.SID "WSID", bs.serial# "Serial#", bs.sql_address "address", bs.sql_hash_value "Sql hash", bs.program "Blocking App", ws.program "Waiting App", bs.machine "Blocking Machine", ws.machine "Waiting Machine", bs.osuser "Blocking OS User", ws.osuser "Waiting OS User", bs.serial# "Serial#", ws.serial# "WSerial#", DECODE (wk.TYPE, 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'UN', 'USER Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL USER LOCK', 'DX', 'Distributed Xaction', 'CF', 'Control FILE', 'IS', 'Instance State', 'FS', 'FILE SET', 'IR', 'Instance Recovery', 'ST', 'Disk SPACE Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'LOG START OR Switch', 'RW', 'ROW Wait', 'SQ', 'Sequence Number', 'TE', 'Extend TABLE', 'TT', 'Temp TABLE', wk.TYPE ) lock_type, DECODE (hk.lmode, 0, 'None', 1, 'NULL', 2, 'ROW-S (SS)', 3, 'ROW-X (SX)', 4, 'SHARE', 5, 'S/ROW-X (SSX)', 6, 'EXCLUSIVE', TO_CHAR (hk.lmode) ) mode_held, DECODE (wk.request, 0, 'None', 1, 'NULL', 2, 'ROW-S (SS)', 3, 'ROW-X (SX)', 4, 'SHARE', 5, 'S/ROW-X (SSX)', 6, 'EXCLUSIVE', TO_CHAR (wk.request) ) mode_requested, TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2, DECODE (hk.BLOCK, 0, 'NOT Blocking', /**//* Not blocking any other processes */ 1, 'Blocking', /**//* This lock blocks other processes */ 2, 'Global', /**//* This lock is global, so we can't tell */ TO_CHAR (hk.BLOCK) ) blocking_others FROM v$lock hk, v$session bs, v$lock wk, v$session ws WHERE hk.BLOCK = 1 AND hk.lmode != 0 AND hk.lmode != 1 AND wk.request != 0 AND wk.TYPE(+) = hk.TYPE AND wk.id1(+) = hk.id1 AND wk.id2(+) = hk.id2 AND hk.SID = bs.SID(+) AND wk.SID = ws.SID(+) AND (bs.username IS NOT NULL) AND (bs.username <> 'SYSTEM') AND (bs.username <> 'SYS') ORDER BY 1; 這些語(yǔ)句的執(zhí)行最好是在plsql或是sqldeveloper如果是直接在數(shù)據(jù)庫(kù)里面執(zhí)行的需要格式化表,，否則會(huì)很讓你眼花的,。 |
|
|
