溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
原文鏈接： https://www.modb.pro/db/22800
SQL> create table dept(deptno number,dname varchar2(20), 2 constraint pk_dept primary key (deptno) 3 ); Table created. SQL>
SQL> create table emp(empno number,ename varchar2(20),deptno number, 2 constraint pk_emp primary key (empno), 3 constraint fk_deptno foreign key (deptno) references dept (deptno) 4 ); Table created. SQL>
SQL> insert into dept select deptno,dname from scott.dept; 4 rows created. SQL> insert into emp select empno,ename,deptno from scott.emp; 14 rows created. SQL> commit;Commit complete.SQL> select * from dept; DEPTNO DNAME---------- ---------------------------------------- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS SQL> select * from emp; EMPNO ENAME DEPTNO---------- ---------------------------------------- ---------- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10 14 rows selected. SQL>
SQL> select userenv('sid') from dual;
USERENV('SID')--------------
170
SQL> insert into emp values(3000,‘xiaoli’,10);
1 row created.
SQL>
SQL> select userenv('sid') from dual;
USERENV('SID')--------------
191
SQL> update dept set deptno=10,dname='AAAAA' where deptno=10;
select mm.addr , mm.kaddr , mm.sid , row_number() over (partition by mm.type,mm.id1,mm.id2 order by mm.lmode desc ,mm.ctime desc) resource_row_number , mm.type , mm.id1 , mm.id2 , decode(mm.lmode, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') lmode , decode(mm.request, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') request-- , mm.ctime , lpad(trunc(mm.ctime/60/60),3) || ' Hour ' || lpad(to_char(trunc(mm.ctime/60) - trunc(mm.ctime/60/60) * 60,'fm09'), 2) || ' Min ' || lpad(to_char(mm.ctime - trunc(mm.ctime/60)*60,'fm09'), 2) || ' Sec' ctime , case when mm.block = 1 and mm.lmode != 0 then 'holder' when mm.block = 0 and mm.request != 0 then 'waiter' else null end role , case when ee.blocking_session is not null then 'waiting for SID '|| ee.blocking_session else null end blocking_session , dd.sql_text sql_text , cc.event wait_eventfrom v$lock mm , v$session ee , v$sqlarea dd , v$session_wait ccwhere mm.sid in ( select nn.sid from ( select tt.* , count(1) over (partition by tt.type,tt.id1,tt.id2) cnt , max(tt.lmode) over (partition by tt.type,tt.id1,tt.id2) lmod_flag , max(tt.request) over (partition by tt.type,tt.id1,tt.id2) request_flag from v$lock tt ) nn where nn.cnt > 1 and nn.lmod_flag != 0 and nn.request_flag != 0) and mm.sid = ee.sid(+) and ee.sql_id = dd.sql_id(+) and mm.sid = cc.sid(+)order by mm.type, mm.id1, mm.id2,mm.lmode desc ,mm.ctime desc;
這里我們可以看到：
session 1 正在做DML處理，對于DML處理會在表級鎖?上加上SX模式的鎖。
session 2 在更新主鍵deptno的時候，因為在子表EMP對應的外鍵字段上沒有鎖，因此需要在表級（TM）追加了一個S模式的鎖。
session 2 請求追加S模式的鎖在了TM上，因為SX與S模式的鎖是互斥的，因此session 2 被阻塞而掛起。
SQL> select userenv('sid') from dual;
USERENV('SID')--------------
213
SQL> insert into emp values(3001,'xiaozhang',20);
select mm.addr , mm.kaddr , mm.sid , row_number() over (partition by mm.type,mm.id1,mm.id2 order by mm.lmode desc ,mm.ctime desc) resource_row_number , mm.type , mm.id1 , mm.id2 , decode(mm.lmode, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') lmode , decode(mm.request, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') request-- , mm.ctime , lpad(trunc(mm.ctime/60/60),3) || ' Hour ' || lpad(to_char(trunc(mm.ctime/60) - trunc(mm.ctime/60/60) * 60,'fm09'), 2) || ' Min ' || lpad(to_char(mm.ctime - trunc(mm.ctime/60)*60,'fm09'), 2) || ' Sec' ctime , case when mm.block = 1 and mm.lmode != 0 then 'holder' when mm.block = 0 and mm.request != 0 then 'waiter' else null end role , case when ee.blocking_session is not null then 'waiting for SID '|| ee.blocking_session else null end blocking_session , dd.sql_text sql_text , cc.event wait_eventfrom v$lock mm , v$session ee , v$sqlarea dd , v$session_wait ccwhere mm.sid in ( select nn.sid from ( select tt.* , count(1) over (partition by tt.type,tt.id1,tt.id2) cnt , max(tt.lmode) over (partition by tt.type,tt.id1,tt.id2) lmod_flag , max(tt.request) over (partition by tt.type,tt.id1,tt.id2) request_flag from v$lock tt ) nn where nn.cnt > 1 and nn.lmod_flag != 0 and nn.request_flag != 0) and mm.sid = ee.sid(+) and ee.sql_id = dd.sql_id(+) and mm.sid = cc.sid(+)order by mm.type, mm.id1, mm.id2,mm.lmode desc ,mm.ctime desc;
這里我們可以看到：
session 3 需要做DML處理，同樣需要請求SX模式的鎖在TM上，因此它被session 2 在TM上S模式鎖的請求阻塞。
SQL> insert into emp values(3000,'xiaoli',10); 1 row created. SQL>
SQL> update dept set deptno=10,dname='AAAAA' where deptno=10; 1 row updated. SQL>
這里發現session 2 就沒有被 session 1 所阻塞。
1、所有的外鍵上創建索引，避免不必要的死鎖產生。
2、update 父表的語句，盡量避免更新主鍵。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
