A-A+
外键缺失索引导致锁表的问题
外键缺失索引导致锁表的问题
一般建议在外键上添加B树索引，如果没有B树索引，那么可能在对子表操作时，造成主表锁定。以下便是验证子表存在未决事务，对主表的增删改是否会因此受到影响。
结论：
子表的外键列上没有索引时，发现子表存在未决事务时，主表的增加不会受到影响，但是删除和修改会受到影响。
子表的外键列上创建B树索引后，发现子表存在未决事务时，主表的增删改不会受到影响。
子表的外键列上创建位图索引后，发现子表存在未决事务时，主表的增删改受到的影响和未加索引一致。
查看测试表
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
查看该模式下的那些外键缺失索引
SQL> column columns format a30 word_wrapped SQL> column tablename format a15 word_wrapped SQL> column constraint_name format a15 word_wrapped SQL> SQL> select table_name, 2 constraint_name, 3 cname1 || nvl2(cname2, ',' || cname2, null) || 4 nvl2(cname3, ',' || cname3, null) || 5 nvl2(cname4, ',' || cname4, null) || 6 nvl2(cname5, ',' || cname5, null) || 7 nvl2(cname6, ',' || cname6, null) || 8 nvl2(cname7, ',' || cname7, null) || 9 nvl2(cname8, ',' || cname8, null) columns 10 from (select b.table_name, 11 b.constraint_name, 12 max(decode(position, 1, column_name, null)) cname1, 13 max(decode(position, 2, column_name, null)) cname2, 14 max(decode(position, 3, column_name, null)) cname3, 15 max(decode(position, 4, column_name, null)) cname4, 16 max(decode(position, 5, column_name, null)) cname5, 17 max(decode(position, 6, column_name, null)) cname6, 18 max(decode(position, 7, column_name, null)) cname7, 19 max(decode(position, 8, column_name, null)) cname8, 20 count(*) col_cnt 21 from (select substr(table_name, 1, 30) table_name, 22 substr(constraint_name, 1, 30) constraint_name, 23 substr(column_name, 1, 30) column_name, 24 position 25 from user_cons_columns) a, 26 user_constraints b 27 where a.constraint_name = b.constraint_name 28 and b.constraint_type = 'R' 29 group by b.table_name, b.constraint_name) cons 30 where col_cnt > ALL (select count(*) 31 from user_ind_columns i 32 where i.table_name = cons.table_name 33 and i.column_name in (cname1, 34 cname2, 35 cname3, 36 cname4, 37 cname5, 38 cname6, 39 cname7, 40 cname8) 41 and i.column_position <= cons.col_cnt 42 group by i.index_name); TABLE_NAME CONSTRAINT_NAME COLUMNS ------------------------------ --------------- ------------------------------ EMP FK_DEPTNO DEPTNO
注意：此处发现子表emp的外键列deptno缺失索引
测试1：子表存在未决事务，对父表进行插入操作
会话1删除子表一条记录，并不提交
SQL> select sid from v$mystat where rownum<2;
SID
----------
159
SQL> delete from emp where EMPNO=7369;
1 row deleted.
测试会话2插入一条新记录到父表是否成功
SQL> select sid from v$mystat where rownum<2;
SID
----------
149
SQL> insert into dept values(50,'yallonking','yallonking');
1 row created.
测试2：子表存在未决事务，对父表进行删除操作
会话1删除子表一条记录，并不提交
SQL> select sid from v$mystat where rownum<2;
SID
----------
159
SQL> delete from emp where EMPNO=7369;
1 row deleted.
测试会话2删除父表一条未级联子表的记录是否成功
SQL> select sid from v$mystat where rownum<2;
SID
----------
149
SQL> delete from dept where DEPTNO=40;
注意：此时会话2等待
用第三个会话查看用户等待事件
SQL> select sid from v$mystat where rownum<2;
SID
----------
138
SQL> col event form A50
SQL> col Prev form 99999
SQL> col Curr form 99999
SQL> col Tot form 99999
SQL> set linesize 140
SQL> select inst_id,event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
2 from gv$session_Wait group by inst_id,event order by 5
3 ;
INST_ID EVENT Prev Curr Tot
---------- -------------------------------------------------- ------ ------ ------
1 Streams AQ: waiting for time management or cleanup 0 1 1
tasks
1 enq: TM - contention 0 1 1
1 smon timer 0 1 1
1 Streams AQ: qmn slave idle wait 0 1 1
1 SQL*Net message from client 0 1 1
1 pmon timer 0 1 1
1 Streams AQ: qmn coordinator idle wait 0 1 1
1 jobq slave wait 0 1 1
1 SQL*Net message to client 1 0 1
INST_ID EVENT Prev Curr Tot
---------- -------------------------------------------------- ------ ------ ------
1 rdbms ipc message 0 11 11
10 rows selected.
SQL> select SID,SERIAL#,SQL_ID,BLOCKING_SESSION,EVENT from v$session;
SID SERIAL# SQL_ID BLOCKING_SESSION EVENT
---------- ---------- ------------- ---------------- --------------------------------------------------
138 40 br360w88pbxdf SQL*Net message to client
139 34 Streams AQ: qmn slave idle wait
148 1 4gd6b1r53yt88 Streams AQ: waiting for time management or cleanup
tasks
149 110 adayn6h80by5s 159 enq: TM - contention
151 1 Streams AQ: qmn coordinator idle wait
155 1 rdbms ipc message
156 1 rdbms ipc message
158 163 jobq slave wait
159 17 SQL*Net message from client
SID SERIAL# SQL_ID BLOCKING_SESSION EVENT
---------- ---------- ------------- ---------------- --------------------------------------------------
160 1 4gd6b1r53yt88 rdbms ipc message
161 1 rdbms ipc message
162 1 rdbms ipc message
163 1 rdbms ipc message
164 1 rdbms ipc message
165 1 smon timer
166 1 rdbms ipc message
167 1 rdbms ipc message
168 1 rdbms ipc message
169 1 rdbms ipc message
170 1 pmon timer
20 rows selected.
此处发现会话159阻塞了149.
将159即就是会话1的事务结束后，会话149即就是会话2会即时执行。
测试3：子表存在未决事务，对父表进行修改操作
会话1删除子表一条记录，并不提交
SQL> select sid from v$mystat where rownum<2;
SID
----------
159
SQL> delete from emp where EMPNO=7369;
1 row deleted.
测试会话2修改父表一条未级联子表的记录是否成功
SQL> select sid from v$mystat where rownum<2;
SID
----------
149
SQL> update dept set DEPTNO=50 where DEPTNO=40;
注意：此时会话2等待
用第三个会话查看用户等待事件
SQL> select sid from v$mystat where rownum<2;
SID
----------
138
SQL> select SID,SERIAL#,SQL_ID,BLOCKING_SESSION,EVENT from v$session;
SID SERIAL# SQL_ID BLOCKING_SESSION EVENT
---------- ---------- ------------- ---------------- --------------------------------------------------
138 40 br360w88pbxdf SQL*Net message to client
139 34 Streams AQ: qmn slave idle wait
148 1 4gd6b1r53yt88 Streams AQ: waiting for time management or cleanup
tasks
149 110 640jbq09zkk8s 159 enq: TM - contention
151 1 Streams AQ: qmn coordinator idle wait
155 1 rdbms ipc message
156 1 rdbms ipc message
158 179 jobq slave wait
159 17 SQL*Net message from client
SID SERIAL# SQL_ID BLOCKING_SESSION EVENT
---------- ---------- ------------- ---------------- --------------------------------------------------
160 1 4gd6b1r53yt88 rdbms ipc message
161 1 rdbms ipc message
162 1 rdbms ipc message
163 1 rdbms ipc message
164 1 rdbms ipc message
165 1 smon timer
166 1 rdbms ipc message
167 1 rdbms ipc message
168 1 rdbms ipc message
169 1 rdbms ipc message
170 1 pmon timer
20 rows selected.
此处发现会话159阻塞了149.
同样将159即就是会话1的事务结束后，会话149即就是会话2会即时执行。
以下为在子表的外键上创建索引的测试
首先创建B树索引进行测试
SQL> create index idx_emp on emp(DEPTNO); Index created.
在对子表的外键列上创建B树索引后，并进行以上测试，发现子表存在未决事务时，主表的增删改不会受到影响。
其次创建位图索引进行测试
SQL> drop index idx_emp; Index dropped. SQL> create bitmap index idx_bit_emp on emp(DEPTNO); Index created. SQL> delete from emp where EMPNO=7369; 1 row deleted.
在对子表的外键列上创建位图索引后，并进行以上测试，发现子表存在未决事务时，主表的增删改受到的影响和未加索引一致。
1 条留言 访客：0 条 博主：0 条 引用： 1 条
来自外部的引用： 1 条