enable table lock 的enqueue等待
对单个表的enable table lock的等待尽然是TX等待，这个等待要等待数据库中的所有在其命令执行以前的事务全部完成后才能结束。
ALTER TABLE test2 DISABLE TABLE LOCK;
SESSION 1
INSERT INTO test1 SELECT * FROM test1 WHERE ROWNUM=1
SESSION 2
INSERT INTO test1 SELECT * FROM test1 WHERE ROWNUM=1
SESSION 3
ALTER TABLE test2 ENABLE TABLE LOCK;
该语句卡住
-------------------------------------------------------------------------------------------------------
SYS@SOURCE10 > select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
30434224 30434238 165 XR 4 0 1 0 62272 0
30434280 30434294 165 CF 0 0 2 0 62263 0
30434338 3043434C 165 RS 25 1 2 0 62260 0
30434394 304343A8 167 PW 1 0 3 0 62255 0
304343F0 30434404 166 RT 1 0 6 0 62260 0
3043444C 30434460 139 TX 131110 817 0 4 3 0
30434560 30434574 167 MR 1 0 4 0 62257 0
304345BC 304345D0 167 MR 3 0 4 0 62257 0
30434618 3043462C 167 MR 5 0 4 0 62257 0
30434674 30434688 167 MR 6 0 4 0 62257 0
304346D0 304346E4 167 MR 7 0 4 0 62257 0
3043472C 30434740 167 MR 201 0 4 0 62257 0
304347E4 304347F8 164 TS 3 1 3 0 62251 0
2F9C6550 2F9C6568 145 TM 13753 0 3 0 162 0
2F9C65FC 2F9C6614 143 TM 13753 0 3 0 162 0
2FA04690 2FA046B4 145 TX 131110 817 6 0 162 1
2FA39400 2FA39424 143 TX 327700 835 6 0 162 0
-------------------------------------------------------------------------------------------------------
SESSION 1
commit;
-------------------------------------------------------------------------------------------------------
SYS@SOURCE10 > select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
30434224 30434238 165 XR 4 0 1 0 62353 0
30434280 30434294 165 CF 0 0 2 0 62344 0
30434338 3043434C 165 RS 25 1 2 0 62341 0
30434394 304343A8 167 PW 1 0 3 0 62336 0
304343F0 30434404 166 RT 1 0 6 0 62341 0
3043444C 30434460 139 TX 327700 835 0 4 3 0
30434560 30434574 167 MR 1 0 4 0 62338 0
304345BC 304345D0 167 MR 3 0 4 0 62338 0
30434618 3043462C 167 MR 5 0 4 0 62338 0
30434674 30434688 167 MR 6 0 4 0 62338 0
304346D0 304346E4 167 MR 7 0 4 0 62338 0
3043472C 30434740 167 MR 201 0 4 0 62338 0
304347E4 304347F8 164 TS 3 1 3 0 62332 0
2F9C65FC 2F9C6614 143 TM 13753 0 3 0 243 0
2FA39400 2FA39424 143 TX 327700 835 6 0 243 1
-------------------------------------------------------------------------------------------------------
SESSION 3 继续HANG住
SESSION 2
commit;
SESSION 3 释放
--------------------------------------------------------------------------------------------------------
可以测试，SESSION 3运行ENABLE TABLE LOCK后的事务不会继续锁住该语句，这个习惯和SAVEPOINT的很相似
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8242091/viewspace-611532/ ，如需转载，请注明出处，否则将追究法律责任。
