Oracle動態效能檢視學習之v$lock & v$locked_object
這個檢視列出Oracle 伺服器當前擁有的鎖以及未完成的鎖或栓鎖請求。如果你覺著session在等待等待事件佇列那你應該檢查本檢視。如果你發現session在等待一個鎖,那麼按如下先後順序:
使用V$LOCK找出session持有的鎖。
使用V$SESSION找出持有鎖或等待鎖的session執行的sql語句。 使用V$SESSION_WAIT找出什麼原因導致session持有鎖堵塞。 使用V$SESSION獲取關於持有鎖的程式和使用者的更多資訊。 V$LOCK中的常用列
SID:表示持有鎖的會話資訊。
TYPE:表示鎖的型別。值包括TM和TX等。
LMODE:表示會話等待的鎖模式的資訊。用數字0-6表示,和表1相對應。 REQUEST:表示session請求的鎖模式的資訊。 ID1,ID2:表示鎖的物件標識。 公共鎖型別
在Oracle資料庫中,DML鎖主要包括TM鎖和TX鎖,其中TM鎖稱為表級鎖,TX鎖稱為事務鎖或行級鎖。
當Oracle執行DML語句時,系統自動在所要操作的表上申請TM型別的鎖。當TM鎖獲得後,系統再自動申請TX型別的鎖,並將實際鎖定的資料行的鎖標誌位進行置位。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標誌,而只需檢查TM鎖模式的相容性即可,大大提高了系統的效率。TM鎖包括了SS、SX、S、X等多種模式,在資料庫中用0-6來表示。不同的SQL操作產生不同型別的TM鎖,如下表1。 TX:行級鎖,事務鎖
在改變資料時必須是排它模式(mode 6)。每一個活動事務都擁有一個鎖。它將在事務結束(commit/rollback)時釋放。如果一個塊包括的列被改變而沒有ITL(interested
transaction list)槽位(entries),那麼session將鎖置於共享模式(mode 4)。當session獲得塊的ITL槽位時釋放。當一個事務首次發起一個DML語句時就獲得一個TX鎖,該鎖保持到事務被提交或回滾。當兩個或多個會話在表的同一條記錄上執行DML語句時,第一個會話在該條記錄上加鎖,其他的會話處於等待狀態。當第一個會話提交後,TX鎖被釋放,其他會話才可以加鎖。指出回滾段和事務表項。
按下列項以避免競爭:
避免TX-6型別競爭,需要根據您的應用而定。
避免TX-4型別競爭,可以考慮增加物件INITRANS引數值。 TM:表級鎖
資料庫執行任何DDL語句時必須是排它模式;例如alter table,drop table。執行像insert,update,delete這類DML語句時處於共享模式。它防止其它session對同一個物件同時執行ddl語句。任何物件擁有正被改變的資料,TM鎖都將必須存在。鎖指向物件。在TM佇列避免競爭,可以考慮遮蔽物件表級鎖,遮蔽表級鎖防止物件執行任何ddl語句。 ST:空間事務鎖
每個資料庫(非例項)擁有一個ST鎖。除了本地管理表空間,在space管理操作(新建或刪除extents)時必須是排它模式。物件creation, dropping, extension, 以及truncation都處於這種鎖。多數公共原因的爭奪,是在磁碟排序(並非使用真正的臨時表空間)或回滾段擴充套件或收縮。
按如下項以避免競爭:
使用真正的臨時表空間(true temporary tablespaces),利用臨時檔案。臨時段在磁碟排序之後並不建立或刪除。 使用本地管理表空間。
指定回滾段避免動態擴充套件和收縮,或使用自動undo management。 避免應用執行建立或刪除資料庫物件。 UL:使用者定義鎖
使用者可以自定義鎖。內容較多並與此節關係不大,略過。 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 table、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; --呵呵,等待狀態了吧
select * from v$lock; --又新增了兩條記錄,其它一條type=tx,lmode=0 7.檢視當前被鎖的session正在執行的sql語句
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資料庫中的鎖機制研究
和 Oracle的併發控制(鎖)比較
http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0512niuxzh/ Itpub論壇的oracle專題深入討論區也有一篇非常精彩的討論,地址如下: 我對ORACLE資料鎖的一點體會
http://www.itpub.net/270059.html
本檢視列出系統上的每個事務處理所獲得的所有鎖。
V$LOCKED_OBJECT中的列說明:
XIDUSN:回滾段
XIDSLOT:槽號
XIDSQN:序列號
OBJECT_ID:被鎖物件ID
SESSION_ID:持有鎖的sessionID
ORACLE_USERNAME:持有鎖的Oracle使用者名稱
OS_USER_NAME:持有鎖的作業系統使用者名稱
PROCESS:作業系統程式號
LOCKED_MODE:鎖模式,值同上表1
示例:
以DBA角色, 檢視當前資料庫裡鎖的情況可以用如下SQL語句:
SQL> select object_id,session_id,locked_mode from v$locked_object;
SQL> select t2.username, t2.sid, t2.serial#, t2.logon_time from v$locked_object t1, v$session t2 where t1.session_id = t2.sid order by t2.logon_time;
如果有長期出現的一列,可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖:
SQL> alter system kill session 'sid,serial#';
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/196700/viewspace-1178239/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- (轉)學習Oracle動態效能表-(3)V$LOCK,V$LOCKED_OBJECTOracleObject
- Oracle動態效能檢視學習之v$sqltext & v$sqlareaOracleSQL
- (轉)Oracle動態效能檢視學習之v$processOracle
- Oracle動態效能檢視學習之 V$ROLLSTAT -- 轉Oracle
- 學習動態效能表(八)-(3)-V$LOCKED_OBJECTObject
- (轉)Oracle動態效能檢視學習之V$DB_OBJECT_CACHEOracleObject
- Oracle動態效能檢視學習筆記(1)_v$sysstatOracle筆記
- Oracle動態效能檢視學習筆記(3)_v$undostatOracle筆記
- Oracle動態效能檢視學習筆記(4)_v$rollstatOracle筆記
- Oracle動態效能檢視學習筆記(6)_v$filestatOracle筆記
- Oracle動態效能檢視學習筆記(7)_v$sessionOracle筆記Session
- Oracle動態效能檢視學習筆記(2)_v$sesstat_v$mystat_v$statnameOracle筆記
- 學習動態效能表(八)-(1)-v$lock
- 學習動態效能表(八)-(2)-v$lock
- Oracle動態效能檢視學習筆記(4)_v$waitstatOracle筆記AI
- Oracle動態效能檢視學習筆記(8)_v$waitstatOracle筆記AI
- Oracle動態效能檢視學習筆記(9)_v$system_eventOracle筆記
- V$PGASTAT動態效能檢視AST
- Oracle動態效能檢視之v$session_longops ztOracleSessionGo
- Oracle動態效能檢視學習筆記(10)_v$session_waitOracle筆記SessionAI
- 授權某使用者,檢視動態效能檢視的許可權(如v$latch,v$lock,v$sqlarea,v$sql,v$sysstat)SQL
- 動態檢視學習之v$session_waitSessionAI
- 學習oracle動態效能表--v$transactionOracle
- oracle鎖表問題處理 v$lock v$locked_objectOracleObject
- 學習動態效能表第八篇-(1)-V$LOCK
- (轉):學習Oracle動態效能表-(7)-V$SQLTEXT,V$SQLAREAOracleSQL
- [轉]學習Oracle動態效能表-(6)-V$SQLTEXT,V$SQLAREAOracleSQL
- Oracle的v$動態檢視 收藏Oracle
- 動態檢視V$SESSION_LONGOPS學習SessionGo
- (轉):學習Oracle動態效能表-(12)-V$PROCESSOracle
- (轉):學習Oracle動態效能表-(10)-V$FILESTATOracle
- (轉):學習Oracle動態效能表-(8)-V$SESSIONOracleSession
- (轉):學習Oracle動態效能表-(5)-V$SESSTATOracle
- (轉)學習Oracle動態效能表-(4)-V$SYSSTATOracle
- (轉):學習Oracle動態效能表-(2)-V$SQLTEXTOracleSQL
- (轉):學習Oracle動態效能表-(1)-V$SQLAREAOracleSQL
- (轉):學習Oracle動態效能表-(21)-V$UNDOSTATOracle
- (轉):學習Oracle動態效能表-(19)-v$rowcacheOracle