SQL入門之7 鎖

鎖的型別,產生的情況[@more@]

準備知識：
0：none
1：null 空
2：Row-S 行共享(RS)：共享表鎖
3：Row-X 行專用(RX)：用於行的修改
4：Share 共享鎖(S)：阻止其他DML操作
5：S/Row-X 共享行專用(SRX)：阻止其他事務操作
6：exclusive 專用(X)：獨立訪問使用

1級鎖有：Select，有時會在v$locked_object出現。
2級鎖有：Select for update,Lock For Update,Lock Row Share
select for update當對話使用for update子串開啟一個遊標時，所有返回集中的資料行都將處於行級(Row-X)獨佔式鎖定，其他物件只能查詢這些資料行，不能進行update、delete或select for update操作。
3級鎖有：Insert, Update, Delete, Lock Row Exclusive
沒有commit之前插入同樣的一條記錄會沒有反應, 因為後一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。
4級鎖有：Create Index, Lock Share
locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作會提示ora-00054錯誤。
00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5級鎖有：Lock Share Row Exclusive
具體來講有主外來鍵約束時update / delete ... ; 可能會產生4,5的鎖。
6級鎖有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

V$LOCK
欄位內容：SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK
TYPE：TX（TM，DML，表鎖），MR介質恢復，ST磁碟空間管理
LMODE/REQUEST：01：空， 2：RS， 3：RX 4：S 5：SRX 6：X
LMODE：0，1表明程式已經獲得一個鎖，非0，等待獲得一個瑣
ID1：TX狀態下，等待鎖的物件ID，TM狀態下，回滾號碼的十進位制
ID2：TM：0 TX：回滾槽重新使用的次數

-- 消除鎖 exit rollback commit
rollback

SQL> lock table emp in share mode;
SQL> select * from v$lock where sid=11
SID TY ID1 ID2 LMODE
---- -- ---------- ---------- ----------
11 TM 7667 0 4
解釋：鎖型別TM，鎖的模式SHARE，對錶7667加的鎖，鎖的LMODE=4指的是 Share 共享鎖(S)：阻止其他DML操作

-- 顯式發出一個鎖表命令及結果
SQL> lock table emp in exclusive mode;
SQL> select * from v$lock;
...
11 TM 7667 0 6
解釋：鎖型別TM，鎖的模式exclusive，對錶7667加的鎖，鎖的LMODE=6指的是 exclusive 專用(X)：獨立訪問使用

-- select for update 產生的鎖
SQL> select * from emp where empno=7934 for update;
SQL> select * from v$lock;
11 TX 655377 459 6
11 TM 7667 0 3
解釋：for update對錶產生了一個row exclusive鎖(TM),LMODE=3;
另外對索引產生了一個exclusive鎖(TX),LMODE=6;

-- insert 產生的鎖
SQL> insert into dept values (11,'ww','zhengzhou');
SQL> select * from v$lock where sid=11;
TY ID1 ID2 LMODE
-- ---------- ---------- ----------
TX 393254 447 6
TM 7668 0 3
解釋：insert對錶產生了一個row exclusive鎖(TM),LMODE=3;
另外insert對dept上的索引加了一個exclusive鎖(TX),LMODE=6;
這與for update產生的鎖相同。

-- update產生的鎖
SQL> update dept set loc='zz' where deptno=11;
SQL> select type,id1,lmode from v$lock where sid=11;
TY ID1 LMODE
-- ---------- ----------
TX 655396 6
TM 7668 3
解釋：update對錶產生了一個row exclusive鎖(TM),LMODE=3;
另外update對dept上的索引加了一個exclusive鎖(TX),LMODE=6;
這與for updateinsert產生的鎖相同。

-- 刪除產生的鎖
SQL> delete from dept where deptno=11;
SQL> select type,id1,lmode from v$lock where sid=11;
TY ID1 LMODE
-- ---------- ----------
TX 458763 6
TM 7668 3
解釋： delete 對錶產生了一個row exclusive鎖(TM),LMODE=3;
另外 delete 對dept上的索引加了一個exclusive鎖(TX),LMODE=6;
這與for updateinsertupdate產生的鎖相同。
小結：insert / update / delete ... ; 是3的鎖。

-- 建立索引時產生的鎖
SQL> create index big_table_idx_on on big_table(object_name);
TY ID1 ID2 LMODE
-- ---------- ---------- ----------
TX 458786 449 6--對big_table表加的exclusive鎖 有限制的ROWID
TM 18 0 3--對系統表OBJ$加的一個行級排它鎖
TM 7880 0 4--對big_table表加的share鎖
DL 7880 0 3--對big_table表加的DL
未完