原文链接： 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进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
