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