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部落格 ” ,連結:http://blog.itpub.net/8242091/viewspace-611532/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle enqueue lockOracleENQ
- MySQL 5.6 drop database時,table metadata lock等待MySqlDatabase
- 【等待事件】等待事件系列(5.1)--Enqueue(佇列等待)事件ENQ佇列
- 關於tc型別的enqueue等待型別ENQ
- sequence可能引發的各種lock/enqueueENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!的分析AIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- 【MySQL】select for update 的Row Lock 與Table LockMySql
- Oracle 9i RAC enqueue等待測試OracleENQ
- 故障排除:"WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "AIENQ
- 等待事件之Row Cache Lock事件
- LIBRARY CACHE LOCK 等待事件事件
- mutex,latch,lock,enqueue hash chains latch基礎概念MutexENQAI
- 一次WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCKAIENQ
- create table if not exists Waiting for table metadata lockAI
- latch:library cache lock等待事件事件
- 關於DFS lock handle等待事件事件
- Cell smart table scan等待事件事件
- ORA-25307 ENQUEUE RATE TOO HIGH. ENABLE FLOW CONTROLENQ
- Metlink:Troubleshooting:WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- 關於Oracle 9i RAC enqueue等待的一點測試OracleENQ
- 尋找 library cache lock 等待事件的session事件Session
- 'library cache lock'等待事件的處理方法事件
- alter table modify constraint_disable_enable_novalidateAI
- Waiting for table metadata lockAI
- 等待事件enq: TX - row lock contention事件ENQ
- LightDB/PostgreSQL等待事件 Lock transactionidSQL事件
- 【等待事件】-enq: TX - row lock contention事件ENQ
- enq:Library cache lock/pin等待事件ENQ事件
- [ORACLE 11G]ROW CACHE LOCK 等待Oracle
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! 與 dc_tablespcesAIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK-[ID 278316.1]AIENQ
- 由row cache lock等待事件引起的效能問題事件
- Oracle blocking issue with lock table in exclusive modeOracleBloC
- Lock物件Condition介面實現等待/通知物件
- 等待事件enq TX row lock contention分析事件ENQ
- MySQL 5.5 -- innodb_lock_wait 鎖 等待MySqlAI
- 翻譯metalink關於WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK文章AIENQ