學習動態效能表(八)-(2)-v$lock

V$LOCK中的連線列

Column View Joined Column(s) 
SID V$SESSION SID
ID1, ID2, TYPE V$LOCK ID1, ID2, TYPE
ID1 DBA_OBJECTS OBJECT_ID
TRUNCID1/65536) V$ROLLNAME USN

如果session在等待鎖，這可被用於找出session持有的鎖，。
可被用於找出DML鎖型別的被鎖物件(type='TM')
可被用於找出行級事務鎖(TYPE='TX')使用中的回滾段，不過，需要透過V$TRANSACTION連線查詢得到。

表1 Oracle的TM鎖型別 
鎖模式 鎖描述 解釋 SQL操作 
0 none 
1 NULL 空 Select 
2 SS(Row-S) 行級共享鎖，其他物件只能查詢這些資料行 Select for update、Lock for update、Lock row share 
3 SX(Row-X) 行級排它鎖，在提交前不允許做DML操作 Insert、Update、Delete、Lock row share 
4 S(Share) 共享鎖 Create index、Lock share 
5 SSX(S/Row-X) 共享行級排它鎖 Lock share row exclusive 
6 X(Exclusive) 排它鎖 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

　　數字越大鎖級別越高, 影響的操作越多。一般的查詢語句如select ... from ... ;是小於2的鎖, 有時會在v$locked_object出現。select ... from ... for update; 是2的鎖。

　　當對話使用for update子串開啟一個遊標時，所有返回集中的資料行都將處於行級(Row-X)獨佔式鎖定，其他物件只能查詢這些資料行，不能進行update、delete或select...for update操作。insert / update / delete ... ; 是3的鎖。

　　沒有commit之前插入同樣的一條記錄會沒有反應, 因為後一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。

　　建立索引的時候也會產生3,4級別的鎖。locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作會提示ora-00054錯誤。有主外來鍵約束時 update / delete ... ; 可能會產生4,5的鎖。DDL語句時是6的鎖。

　　如果出現了鎖的問題, 某個DML操作可能等待很久沒有反應。當你採用的是直接連線資料庫的方式，也不要用OS系統命令 $kill process_num 或者 $kill -9 process_num來終止使用者連線，因為一個使用者程式可能產生一個以上的鎖, 殺OS程式並不能徹底清除鎖的問題。記得在資料庫級別用alter system kill session 'sid,serial#';殺掉不正常的鎖。

示例：
我按照自己的理解演示的TX,TM鎖如下：
1.create table TMP1(col1 VARCHAR2(50));--建立臨時表
2.select * from v$lock;--關掉當前鎖資訊
3.select * from tmp1 for update; --加鎖
4.select * from v$lock; ---看看現在的鎖列表，是不是多了兩條記錄。Type分別為tx,tm，對照表1。
5.新開一個連線，然後
select * from tmp1 for update; --呵呵，等待狀態了吧
6.select * from v$lock; --又新增了兩條記錄，其它一條type=tx,lmode=0
7.檢視當前被鎖的session正在執行的sql語句
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value
8.將之前的for update語句commit或者rollback，然後新開連線的session擁有鎖。有興趣的朋友還可以試試兩條for update的時候，關閉先執行的那個視窗，看看oracle會給出什麼樣的響應。

　　這一節是我在自整理v$系列檢視以來花費時間和精力最多的一個，我反覆看了document，又從網上搜尋了各種資料實際使用案例等，就是不開竅。這一節至今我也仍未有把握說盡在掌握，所以在上述文字中除了例子，我如實貼出了收集來的內容，未加任何自我理解，就是擔心萬一我的理解有誤，會對其它瀏覽本文的人造成困擾。同時我把在收集過程中自我感覺對理解v$lock可能有幫助的資料地址列出，供有心人參考：

Oracle資料庫中的鎖機制研究

DB2和 Oracle的併發控制（鎖）比較
http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0512niuxzh/

Itpub論壇的oracle專題深入討論區也有一篇非常精彩的討論，地址如下：
我對ORACLE資料鎖的一點體會
http://www.itpub.net/270059.html