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

最新推荐文章于 2024-03-07 12:01:59 发布

iteye_4515

最新推荐文章于 2024-03-07 12:01:59 发布

阅读量195

点赞数

文章标签：数据库

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 TM
Waiter:    1039      2     1158       23285      0         0        2 TM
Waiter:    1038      2     1659       23285      0         0        2 TM
Waiter:    1036      2     1601       23285      0         0        2 TM
Waiter:    1028      2      893       23285      0         0        2 TM
Waiter:    1025      2     1854       23285      0         0        2 TM
Waiter:    1024      2     1872       23285      0         0        2 TM
Waiter:    1020      2     2845       23285      0         0        2 TM
Waiter:    1018      2     2703       23285      0         0        2 TM
Waiter:    1004      2     1689       23285      0         0        2 TM
Waiter:     998      2     2934       23285      0         0        2 TM
Waiter:     981      2     1959       23285      0         0        2 TM
Waiter:     979      2     1813       23285      0         0        2 TM
Waiter:     978      2     2083       23285      0         0        2 TM
Waiter:     971      2     1723       23285      0         0        2 TM
Waiter:     969      2     2668       23285      0         0        2 TM
Waiter:     963      2     2920       23285      0         0        2 TM
......

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

sql1 1fjf8sujb2wuw

insert into WWW_VARIABLEINSTANCE (NAME_, CONVERTER_, TOKEN_, TO
KENVARIABLEMAP_, PROCESSINSTANCE_, STRINGVALUE_, CLASS_, ID_) va
lues (:1, :2, :3, :4, :5, :6, 'S', :7)


sql2 6ukh0kxukjthv

insert into WWW_TOKEN (VERSION_, NAME_, START_, END_, NODEENTER
_, NEXTLOGINDEX_, ISABLETOREACTIVATEPARENT_, ISTERMINATIONIMPLIC
IT_, ISSUSPENDED_, NODE_, PROCESSINSTANCE_, PARENT_, SUBPROCESSI
NSTANCE_, ID_) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10,
:11, :12, :13, :14)

sql3 dgr3mp4cc6sjx

insert into WWW_COMMENT (VERSION_, ACTORID_, TIME_, MESSAGE_, T
OKEN_, TASKINSTANCE_, ID_) values (:1, :2, :3, :4, :5, :6, :7)

sql4 4fypdt56k14a3

delete from WWW_SWIMLANEINSTANCE where ID_=:1

sql5 g2hntwxrq2502

delete from WWW_TASKINSTANCE where ID_=:1


sql6 agjsdnk7951hy

delete from WWW_MESSAGE where TOKEN_=:1

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

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

COLUMN COLUMNS format a30 word_wrapped
COLUMN tablename format a15 word_wrapped
COLUMN constraint_name format a15 word_wrapped
SELECT 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 124

sys@HRTESTDB(rhel5)>delete t_f where fid = 2;

1 row deleted

---session 2 sid=364

sys@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 = 2
124 :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

iteye_4515

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
外键无索引导致oracle数据库锁的经典问题

2年前的事情了，现在总结一下，至于深层次的外键索引导致数据库锁的原因网上文章比较多，这里就不再详细介绍了1，数据库响应慢，登录后查看大量enq锁存在SQL&gt; SELECT 2 DECODE (request, 0, 'Holder: ', 'Waiter: ') status, SID, 3 inst_id,ctime, id1, id2, lmode, requ...
复制链接

扫一扫