外键无索引导致oracle数据库锁的经典问题

来源：互联网发布：小世界网络编辑：程序博客网时间：2024/05/15 06:12

2年前的事情了，现在总结一下，至于深层次的外键索引导致数据库锁的原因网上文章比较多，这里就不再详细介绍了
1，数据库响应慢，登录后查看大量enq锁存在

SQL> SELECT     2  DECODE (request, 0, 'Holder: ', 'Waiter: ') status, SID,  3  inst_id,ctime, id1, id2, lmode, request, TYPE  4  FROM gv$lock  5  WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$lock WHERE request > 0)  6  ;STATUS        SID    INST_ID    CTIME         ID1    ID2     LMODE      REQUEST TY-------- ------ ---------- ---------- ---------- ---------- ---------- ---------- --Waiter:    1047      2     1932       23285      0         0        2 TMWaiter:    1039      2     1158       23285      0         0        2 TMWaiter:    1038      2     1659       23285      0         0        2 TMWaiter:    1036      2     1601       23285      0         0        2 TMWaiter:    1028      2      893       23285      0         0        2 TMWaiter:    1025      2     1854       23285      0         0        2 TMWaiter:    1024      2     1872       23285      0         0        2 TMWaiter:    1020      2     2845       23285      0         0        2 TMWaiter:    1018      2     2703       23285      0         0        2 TMWaiter:    1004      2     1689       23285      0         0        2 TMWaiter:     998      2     2934       23285      0         0        2 TMWaiter:     981      2     1959       23285      0         0        2 TMWaiter:     979      2     1813       23285      0         0        2 TMWaiter:     978      2     2083       23285      0         0        2 TMWaiter:     971      2     1723       23285      0         0        2 TMWaiter:     969      2     2668       23285      0         0        2 TMWaiter:     963      2     2920       23285      0         0        2 TM......

2,查看被锁住的会话执行的sql语句，一般是下面几个

sql1 1fjf8sujb2wuwinsert into WWW_VARIABLEINSTANCE (NAME_, CONVERTER_, TOKEN_, TOKENVARIABLEMAP_, PROCESSINSTANCE_, STRINGVALUE_, CLASS_, ID_) values (:1, :2, :3, :4, :5, :6, 'S', :7)sql2 6ukh0kxukjthvinsert into WWW_TOKEN (VERSION_, NAME_, START_, END_, NODEENTER_, NEXTLOGINDEX_, ISABLETOREACTIVATEPARENT_, ISTERMINATIONIMPLICIT_, ISSUSPENDED_, NODE_, PROCESSINSTANCE_, PARENT_, SUBPROCESSINSTANCE_, ID_) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10,:11, :12, :13, :14)sql3 dgr3mp4cc6sjxinsert into WWW_COMMENT (VERSION_, ACTORID_, TIME_, MESSAGE_, TOKEN_, TASKINSTANCE_, ID_) values (:1, :2, :3, :4, :5, :6, :7)sql4 4fypdt56k14a3delete from WWW_SWIMLANEINSTANCE where ID_=:1sql5 g2hntwxrq2502delete from WWW_TASKINSTANCE where ID_=:1sql6 agjsdnk7951hydelete from WWW_MESSAGE where TOKEN_=:1

3，由于锁的对象不一，且被锁住的有insert语句，因此怀疑为外键没有索引导致，使用tom给的脚本查看

通过以上脚本分析，确有很多外键没有索引

COLUMN COLUMNS format a30 word_wrappedCOLUMN tablename format a15 word_wrappedCOLUMN constraint_name format a15 word_wrappedSELECT TABLE_NAME,       CONSTRAINT_NAME,       CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||       NVL2(CNAME3, ',' || CNAME3, NULL) ||       NVL2(CNAME4, ',' || CNAME4, NULL) ||       NVL2(CNAME5, ',' || CNAME5, NULL) ||       NVL2(CNAME6, ',' || CNAME6, NULL) ||       NVL2(CNAME7, ',' || CNAME7, NULL) ||       NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS  FROM (SELECT B.TABLE_NAME,               B.CONSTRAINT_NAME,               MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,               MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,               MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,               MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,               MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,               MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,               MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,               MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,               COUNT(*) COL_CNT          FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,                       POSITION                  FROM USER_CONS_COLUMNS) A,               USER_CONSTRAINTS B         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME           AND B.CONSTRAINT_TYPE = 'R'         GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS WHERE COL_CNT > ALL (SELECT COUNT(*)          FROM USER_IND_COLUMNS I         WHERE I.TABLE_NAME = CONS.TABLE_NAME           AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,                CNAME6, CNAME7, CNAME8)           AND I.COLUMN_POSITION <= CONS.COL_CNT         GROUP BY I.INDEX_NAME)/

4，添加索引后问题解决，再也没有出现过大量enq锁

CREATE INDEX appuser.IDX_SWIMLANINSTANCE ON appuser.JBPM_TASKINSTANCE (SWIMLANINSTANCE_); CREATE INDEX appuser.IDX_TASK ON appuser.JBPM_TASKINSTANCE (TASK_); CREATE INDEX appuser.IDX_TOKEN ON appuser.JBPM_TASKINSTANCE (TOKEN_ );CREATE INDEX appuser.IDX_MESSAGE_NODE ON appuser.JBPM_MESSAGE (NODE_); CREATE INDEX appuser.IDX_MESSAGE_TASKINSTANCE ON appuser.JBPM_MESSAGE (TASKINSTANCE_); CREATE INDEX appuser.IDX_MESSAGE_TOKEN ON appuser.JBPM_MESSAGE (TOKEN_ );

5,下面看简单的小例子来测试一下

hrdb>hrdb>alter session set nls_language=english;Session altered.hrdb>create table t_p (id number primary key, name varchar2(30));Table created.hrdb>create table t_f (fid number, f_name varchar2(30), foreign key (fid) references t_p);Table created.hrdb> insert into t_p values (1, 'a');1 row created.hrdb>insert into t_f values (1, 'a');1 row created.hrdb>insert into t_p values (2, 'b');1 row created.hrdb>insert into t_f values (2, 'c');1 row created.hrdb>commit;Commit complete.-----  session 1 sid 124sys@HRTESTDB(rhel5)>delete t_f where fid = 2;1 row deleted---session 2 sid=364sys@HRTESTDB(rhel5)>delete t_f where fid = 1;1 row deleted.sys@HRTESTDB(rhel5)>select sid from v$mystat where rownum <2;          SID----------       364

--session 1,删除主表，由于得不到t_f的表锁，会话被hang住
sys@HRTESTDB(rhel5)>delete t_p where id = 2;

察看此时数据库锁的情况，资源被364 holder

sys@HRTESTDB(rhel5)>sys@HRTESTDB(rhel5)>SELECT a.sid ||  2         decode(request,  3                0,  4                ' :holder',' :Waiter') sess_id,blocking_session blocker,  5         lmode,  6         request,  7         a.type,  8         c.object_name,  9         decode(row_wait_obj#, 10                -1, 11                'Holder of Lock !!!', 12                dbms_rowid.rowid_create(1, 13                                        row_wait_obj#, 14                                        row_wait_file#, 15                                        row_wait_block#, 16                                        row_wait_row#)) row_id, 17         nvl(SQL_FULLTEXT, 'Holder of Lock !!!') sqltext 18    FROM V$LOCK A, V$LOCKED_OBJECT B, ALL_OBJECTS C, V$SESSION D, V$SQL E 19   WHERE (id1, id2, a.type) in 20         (select id1, id2, type from v$lock where request > 0) 21     AND a.sid = b.session_id 22     AND b.object_id = c.object_id 23     AND d.sid = a.sid 24     AND d.sql_hash_value = e.hash_value(+);SESS_ID            BLOCKER      LMODE    REQUEST TY OBJECT_NAME                    ROW_ID             SQLTEXT--------------- ---------- ---------- ---------- -- ------------------------------ ------------------ --------------------------------------------------364 :holder                         3          0 TM T_F                            Holder of Lock !!! Holder of Lock !!!364 :holder                         3          0 TM T_P                            Holder of Lock !!! Holder of Lock !!!124 :Waiter            364          3          5 TM T_F                            Holder of Lock !!! delete t_p where id = 2124 :Waiter            364          3          5 TM T_P                            Holder of Lock !!! delete t_p where id = 2

参考资料：
http://space.itpub.net/4227/viewspace-553481
http://up2pu.iteye.com/blog/1434852