監(jiān)視和檢測(cè)鎖沖突

1、1 1監(jiān)視和檢測(cè)鎖沖突監(jiān)視和檢測(cè)鎖沖突2 2本課目的本課目的經(jīng)過(guò)本課學(xué)習(xí)，應(yīng)該能夠掌握經(jīng)過(guò)本課學(xué)習(xí)，應(yīng)該能夠掌握: :定義鎖的類型和模式定義鎖的類型和模式列出可能導(dǎo)致沖突的原因列出可能導(dǎo)致沖突的原因使用使用 OracleOracle工具工具 檢測(cè)鎖沖突檢測(cè)鎖沖突在緊急情況下解決鎖沖突在緊急情況下解決鎖沖突防止鎖問(wèn)題防止鎖問(wèn)題確認(rèn)死鎖引起的確認(rèn)死鎖引起的 Oracle Oracle 錯(cuò)誤錯(cuò)誤3 3鎖機(jī)制鎖機(jī)制自動(dòng)管理自動(dòng)管理數(shù)據(jù)并發(fā)的高級(jí)別數(shù)據(jù)并發(fā)的高級(jí)別DMLDML事務(wù)的行級(jí)別鎖（事務(wù)的行級(jí)別鎖（Row-level Row-level lockslocks）不許要鎖查詢不許要鎖查詢改變數(shù)據(jù)一致

2、性級(jí)別改變數(shù)據(jù)一致性級(jí)別專用和共享鎖模式專用和共享鎖模式直到提交或回滾出現(xiàn)，鎖一直保持直到提交或回滾出現(xiàn)，鎖一直保持4 4兩種類型鎖兩種類型鎖(TM)(TX)DML DML 或數(shù)據(jù)鎖或數(shù)據(jù)鎖: :表級(jí)鎖表級(jí)鎖行級(jí)鎖行級(jí)鎖DDL DDL 或或 字典鎖字典鎖5 5DML 鎖鎖一個(gè)一個(gè) DMLDML事務(wù)至少需要事務(wù)至少需要兩個(gè)鎖兩個(gè)鎖: :共享表鎖共享表鎖專用行鎖專用行鎖排隊(duì)機(jī)制保持跟蹤排隊(duì)機(jī)制保持跟蹤: :等待鎖的用戶等待鎖的用戶請(qǐng)求鎖的模式請(qǐng)求鎖的模式用戶請(qǐng)求鎖的順序用戶請(qǐng)求鎖的順序6 6表鎖模式表鎖模式自動(dòng)獲取自動(dòng)獲取: :專用行專用行 (RX): INSERT, UPDATE, (RX):

3、INSERT, UPDATE, DELETEDELETE共享行共享行 (RS): SELECT. FOR UPDATE(RS): SELECT. FOR UPDATE7 7表鎖模式表鎖模式SQL LOCK TABLE table_name IN mode_name MODE;在在LOCK LOCK 語(yǔ)句中手工獲取鎖語(yǔ)句中手工獲取鎖: :共享共享 (S)不允許不允許DML參照完整性隱含使用參照完整性隱含使用8 8表鎖模式表鎖模式在在LOCK LOCK 語(yǔ)句中手工獲取鎖語(yǔ)句中手工獲取鎖: :共享專用行共享專用行 (SRX)(SRX)不允許不允許 DMLDML或共享模式或共享模式參照完整性隱含使用參

4、照完整性隱含使用專用專用 (X)(X)9 9塊中的塊中的DML鎖鎖Row 6Block HeaderLock bytesRow 112TX slot 1TX slot 21010DDL鎖鎖專用專用 DDLDDL鎖鎖: :DROP TABLE DROP TABLE 語(yǔ)句語(yǔ)句ALTER TABLE ALTER TABLE 語(yǔ)句語(yǔ)句共享共享 DDL DDL 鎖鎖: :CREATE PROCEDURE CREATE PROCEDURE 語(yǔ)句語(yǔ)句AUDIT AUDIT 語(yǔ)句語(yǔ)句易碎分析鎖易碎分析鎖: : 無(wú)效的共享無(wú)效的共享 SQL SQL 區(qū)域區(qū)域1111鎖沖突可能原因鎖沖突可能原因不必要的高級(jí)別鎖不

5、必要的高級(jí)別鎖長(zhǎng)時(shí)間運(yùn)行的事物長(zhǎng)時(shí)間運(yùn)行的事物未提交變更未提交變更其它產(chǎn)品強(qiáng)加的高級(jí)別其它產(chǎn)品強(qiáng)加的高級(jí)別鎖鎖1212 UPDATE emp SET sal = sal x 1.1 WHERE empno = 1000;監(jiān)視鎖活動(dòng)的診斷工具監(jiān)視鎖活動(dòng)的診斷工具V$LOCKV$LOCKED_OBJECTTransaction 1 UPDATE emp SET sal = sal x 1.1;Transaction 2Transaction 3 UPDATE emp SET sal = sal x 1.1 WHERE empno = 2000;DBA_WAITERSDBA_BLOCKERS1313

6、鎖等待鎖等待SQL select SQL select * * from dba_waiters; from dba_waiters;WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2MODE_REQUESTED LOCK_ID1 LOCK_ID2- - - - - - - - - - - - - 16 17 Transaction Exclusive 16 17 Trans

7、action Exclusive Exclusive 524293 173Exclusive 524293 173SQL select SQL select * * from dba_blockers; from dba_blockers;HOLDING_SESSIONHOLDING_SESSION- 17 17SQL SQL 1414性能管理器性能管理器15159:009:0510:3011:30事務(wù)事務(wù) 1指南指南: 沖突解決沖突解決 UPDATE emp SET sal = sal x 1.1 WHERE empno = 1000;COMMIT/ROLLBACK; UPDATE emp

8、SET sal = sal x 1.1 WHERE empno = 1000;1 row updated; 事務(wù)事務(wù) 2ALTER SYSTEM KILL SESSION 10,23;1616事務(wù)事務(wù)2事務(wù)事務(wù) 1死鎖死鎖 UPDATE emp SET sal = sal x 1.1 WHERE empno = 1000; UPDATE emp SET sal = sal x 1.1 WHERE empno = 2000;ORA-00060:Deadlock detected while waiting for resource UPDATE emp SET mgr = 1342 WHERE empno = 2000; UPDATE emp SET mgr = 1342 WHERE empno = 1000;9:009:159:161717死鎖死鎖ORA-00060:Deadlock detected while waiting for resource跟蹤文件跟蹤文件 ServerprocessSID_ora_PID.trcU