TM LOCK MODE

1，在DML操作時，自動獲取表上的row exclusive mode（lmod=3）的鎖。

2，可以線上建立索引，但無法線上刪除索引或修改為UNUSABLE（被dml操作阻塞），報“ORA-00054: resource busy and acquire with NOWAIT specified”；一般透過多次嘗試刪除。

--可以嘗試如下方式刪除嗎？
lock table ta in row exclusive mode;
drop index idx_ta;
--問題：drop 操作時，首先提交，然後才執行刪除操作，由於鎖定表的事務結束，刪除操作需要再次排隊獲取TM鎖，實際上該會話並不一定能順利獲取鎖定。

3，不同鎖模式的相容性：

lockmode:
ROW SHARE(lmod=2)：ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.
--阻止表級排他鎖定

ROW EXCLUSIVE(lmod=3)：ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
--阻止表級排他鎖定，同時阻止共享鎖定

SHARE UPDATE(lmod=2)：See ROW SHARE.

SHARE SHARE(lmod=4)：permits concurrent queries but prohibits updates to the locked table.
--允許併發查詢，阻止更新

SHARE ROW EXCLUSIVE(lmod=5)：SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.
--阻止更新，同時阻止共享鎖定

EXCLUSIVE(lmod=6)：EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.
--阻止所有其他鎖定

lock table ta in row share mode nowait;
commit;

lock table ta in row exclusive mode nowait;
commit;

lock table ta in share mode nowait;
commit;

lock table ta in share row exclusive mode nowait;
commit;

lock table ta in exclusive mode nowait;
commit;

4,相容性測試：
4.1會話1：
lock table ta in row share mode;

會話2：
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> lock table ta in row share mode nowait;
表已鎖定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
表已鎖定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share mode nowait;
表已鎖定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
表已鎖定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

4.2會話1：
lock table ta in row exclusive mode;

會話2：
SQL>
SQL> lock table ta in row share mode nowait;
表已鎖定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
表已鎖定。
SQL> commit;
提交完成。

 

SQL>
SQL> lock table ta in share mode nowait;
lock table ta in share mode nowait
           *
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
lock table ta in share row exclusive mode nowait
           *
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
           *
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

4.3會話1：
lock table ta in share mode;

會話2：
SQL> lock table ta in row share mode nowait;
表已鎖定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
lock table ta in row exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share mode nowait;
表已鎖定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
lock table ta in share row exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

4.4會話1：
lock table ta in share row exclusive mode;

會話2：
SQL>
SQL> lock table ta in row share mode nowait;
表已鎖定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
lock table ta in row exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share mode nowait;
lock table ta in share mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
lock table ta in share row exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

4.5會話1：
lock table ta in exclusive mode;

會話2：
SQL> lock table ta in row share mode nowait;
lock table ta in row share mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
lock table ta in row exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share mode nowait;
lock table ta in share mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
lock table ta in share row exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
*
第 1 行出現錯誤:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。