外键无索引导致Oracle数据库锁的经典问题
外键无索引导致Oracle数据库锁的经典问题
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
|
评论暂时关闭