Shulou(Shulou.com)06/01 Report--

Original link: https://www.modb.pro/db/22800

Abstract: today, I saw a question on the Ferris wheel, "Why does the non-indexing of oracle foreign keys lead to deadlock" and why? Now test the relationship between foreign key indexes and locks through some simple cases. 1. Environment simulation 1. Create parent table dept, primary key deptnoSQL > create table dept (deptno number,dname varchar2 (20), 2 constraint pk_dept primary key (deptno) 3); Table created.SQL > 2, create child table emp, primary key empno, foreign key deptnoSQL > 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 > 3. Insert data 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 OPERATIONSSQL > 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 1014 rows selected.SQL > II. Simulation test-foreign key indexed session 1: insert a record on a child table Do not submit SQL > select userenv ('sid') from dual USERENV ('SID')-170

SQL > insert into emp values (3000 mnemonic Xia olive pr 10)

1 row created.

SQL >

Session 2: change a record on the parent table and it will be suspended SQL > select userenv ('sid') from dual;USERENV (' SID')-191SQL > update dept set deptno=10,dname='AAAAA' where deptno=10 Query locks: 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, 'Nice, 2,' SS | RS', 3,'SX | RX', 4, 'slots, 5,' SSX | SRX', 6,'X') lmode Decode (mm.request, 0, null, 1, 'Nissan, 2,' SS | RS', 3,'SX | RX', 4, 'Stiles, 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) * 60M09') 2) | | 'Min' | | lpad (to_char (mm.ctime-trunc (mm.ctime/60) * 60 training 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

Here we can see:

Session 1 is doing DML processing, and for DML processing, the SX mode lock is added to the table-level lock ™.

When session 2 updates the primary key deptno, because there is no lock on the foreign key field corresponding to the child table EMP, it needs to append an S mode lock at the table level (TM).

Session 2 requests to append S-mode locks on TM, because SX and S-mode locks are mutually exclusive, so session 2 is blocked and suspended.

Session 3: insert a record into the child table and it will also be suspended SQL > select userenv ('sid') from dual;USERENV (' SID')-213SQL > insert into emp values (3001) Query locks: 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, 'Nice, 2,' SS | RS', 3,'SX | RX', 4, 'slots, 5,' SSX | SRX', 6,'X') lmode Decode (mm.request, 0, null, 1, 'Nissan, 2,' SS | RS', 3,'SX | RX', 4, 'Stiles, 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) * 60M09') 2) | | 'Min' | | lpad (to_char (mm.ctime-trunc (mm.ctime/60) * 60 training 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

Here we can see:

Session 3 needs to do DML processing, and it also needs to request a SX mode lock on TM, so it is blocked by session 2's request for an S-mode lock on TM.

3. Simulation test-the foreign keys have indexes session 1:SQL > insert into emp values (3000 row created.SQL > session 2:SQL > update dept set deptno=10,dname='AAAAA' where deptno=10;1 row updated.SQL >)

It is found that session 2 is not blocked by session 1.

IV. Conclusion

1. Create indexes on all foreign keys to avoid unnecessary deadlocks.

2. Update the statement of the parent table to avoid updating the primary key.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.