【開發篇sql】 基礎概述（一） 鎖定和事務
1，oracle的鎖定與事務
僅僅來看下oracle的一些鎖和事務的相關檢視或引數，基本原理見基礎篇的事務原理一節。（本節內容可以參考9i或10g的
V$lock:
這個檢視列出了所有資料庫中的所有鎖（locks）和閂（latches）和所有在一個lock或者latch上的請求。簡述如下：
Addr
處於lock狀態的物件的地址
Kaddr
lock的地址
Sid
session id
Type
使用者或者系統鎖的型別，使用者鎖例如：TM-DML入隊，
TX-事務入隊，UL-使用者提供的（User supplied）
系統型別的鎖定諸如：SMON指派恢復程式，臨時段入隊，
分配新的塊入隊，redo執行緒全域性入隊，
寫redo log的入隊（media recovery）等等
例如：
SQL> select distinct type from v$lock;
TYPE
----
MR
RT
TS
XR
與dba_lock中的lock_type對應，可以得到：
SQL> select a.ADDR, a.KADDR, a.TYPE, b.lock_type, a.ID1, a.LMODE
2 from v$lock a, dba_locks b
3 where a.SID = b.session_id;
ADDR KADDR TYPE LOCK_TYPE ID1 LMODE
-------- -------- ---- -------------------------- ---------- ----------
682BE818 682BE828 MR Media Recovery 201 4
682BE7CC 682BE7DC MR Media Recovery 10 4
682BE780 682BE790 MR Media Recovery 9 4
682BE734 682BE744 MR Media Recovery 8 4
682BE6E8 682BE6F8 MR Media Recovery 7 4
682BE69C 682BE6AC MR Media Recovery 6 4
682BE650 682BE660 MR Media Recovery 5 4
682BE604 682BE614 MR Media Recovery 4 4
682BE5B8 682BE5C8 MR Media Recovery 3 4
682BE56C 682BE57C MR Media Recovery 2 4
682BE520 682BE530 MR Media Recovery 1 4
682BE43C 682BE44C RT Redo Thread 1 6
682BE30C 682BE31C XR XR 4 1
682BE488 682BE498 TS Temp Segment 2 3
14 rows selected
再來看看一般開發中涉及到比較重要的兩種鎖定型別：DML鎖定和DDL鎖定
DML鎖定
DML鎖定簡單講，用於保證一行在一段時間只有一個使用者進行修改，並且其他人不能夠刪除這個表或者修改這個表的結構。Dml鎖定指定了資料行的鎖定，或者資料表的鎖定，也即行鎖或者表鎖。
TX（事務）鎖定
從一個事務開始，一直到該事務commit或者rollback，該事務擁有一個TX鎖定，它是一個排隊機制，使得其他會話等待這個事務的完成。簡單的看一個TX例子：
SQL> update scott.dept a set a.dname = 'dname' where a.deptno = 10;
1 row updated
SQL>
SQL> select /*+ rule*/
2 a.TYPE,
3 c.lock_type lt,
4 a.ID1,
5 c.lock_id1 l_id1,
6 a.ID2,
7 c.lock_id2 l_id2,
8 a.LMODE
9 from v$lock a, (select sid from v$mystat where rownum = 1) b, dba_lock c
10 where a.SID = b.SID
11 and a.SID = c.session_id
12 and a.ID1 = c.lock_id1
13 and a.ID2 = c.lock_id2;
TYPE LT ID1 L_ID1 ID2 L_ID2 LMODE
---- -------------------------- ---------- --------- ---------- ----------
TM DML 30137 30137 0 0 3
TX Transaction 196647 196647 2527 2527 6
注意到上面的v$lock中id1,id2(或者dba_lock中的lock_id1,lock_id2）欄位，
對於tm鎖，id1就是object_id，id2為0，對於tx鎖id1是以十進位制數值表示事務佔用的回滾段號和事務solt number,具體演算法就是把id1除以2的16次方，餘數就是solt number，結果的取整數值是回滾段號（參見asktom），那麼改進一下上述查詢：
SQL> select /*+ rule*/
2 a.TYPE,
3 c.lock_type lt,
4 trunc(a.ID1/power(2,16)) rollback_seq,
5 mod(a.ID1,power(2,16)) slot,
6 c.lock_id1 l_id1,
7 a.ID2 seq,
8 c.lock_id2 l_id2,
9 a.LMODE
10 from v$lock a, (select sid from v$mystat where rownum = 1) b, dba_lock c
11 where a.SID = b.SID
12 and a.SID = c.session_id
13 and a.ID1 = c.lock_id1
14 and a.TYPE = 'TX';
TYPE LT ROLLBACK_SEQ SLOT L_ID1 SEQ L_ID2 LMODE
-----------------------------------------------------------------------
TX Transaction 3 39 196647 2527 2527 6
再看看事務和鎖定物件的檢視，通過鎖定物件的地址和session id進行關聯，再改進一下上述查詢：
SQL> select /*+ rule*/
2 a.TYPE,
3 trunc(a.ID1/power(2,16)) rollback_seq,
4 mod(a.ID1,power(2,16)) slot,
5 a.ID2 seq,
6 a.LMODE,
7 d.XIDUSN,
8 d.XIDSLOT,
9 d.XIDSQN,
10 e.OBJECT_ID,
11 e.LOCKED_MODE
12 from v$lock a, (select sid from v$mystat where rownum = 1) b, dba_lock c,
13 v$transaction d,v$locked_object e
14 where a.SID = b.SID
15 and a.SID = c.session_id
16 and a.ID1 = c.lock_id1
17 and a.TYPE = 'TX'
18 and a.SID = e.SESSION_ID
19 and a.ADDR = d.ADDR;
TYPE ROLLBACK_SEQ SLOT SEQ LMODE XIDUSN XIDSLOT XIDSQN OBJECT_ID LOCKED_MODE
---- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
TX 3 39 2527 6 3 39 2527 30137 3
上述（ROLLBACK_SEQ，SLOT，SEQ）分別對應事務的（XIDUSN，XIDSLOT，XIDSQN）這就是事務id，lmode便是鎖定的模式，見稍後的詳述。
那麼如果在一個另外的session中再執行對scott.dept中的相同的行進行修改，這樣將會看到tx鎖是如何工作的。按照此要求改動後的查詢（本例中第一個事務和第二個事務id分別為10，12）：
SQL> select /*+ rule*/
2 a.SID,
3 a.BLOCK,
4 a.REQUEST,
5 a.LMODE,
6 d.XIDUSN,
7 d.XIDSLOT,
8 d.XIDSQN,
9 e.OBJECT_ID,
10 e.LOCKED_MODE
11 from v$lock a,
12 v$transaction d,v$locked_object e
13 where a.SID in (10,12)
14 and a.TYPE = 'TX'
15 and a.SID = e.SESSION_ID
16 and a.ADDR = d.ADDR(+)/*因為session 12的事務被10阻塞了，故現在看到的v$transaction中12的事務並沒有開始。 */
17 ;
SID BLOCK REQUEST LMODE XIDUSN XIDSLOT XIDSQN OBJECT_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
10 1 0 6 3 39 2527 30137 3
12 0 6 0 30137 3
對比一下session 10和session 12 的屬性值：
10的block=1代表了阻塞了其他，request是請求的鎖的型別，其值代表的意義與LMODE一樣，為0說明沒有請求，Lmode則是擁有6的鎖定，事務id為（3 ，39，2527），請求或者鎖定的物件id為30137，locked_mode說明事務請求或者鎖定已經完成的鎖定型別，為3，即是完成了對行的修改。（具體的LOMODE見後續詳細說明）。
而12的block=0，說明了當前沒有阻塞其他，request=6說明請求了一個6的鎖定，當前的lmode為0，還沒有獲得鎖定，事務並沒有開始，故而還沒有事務id。
接下來讓10提交，看看又會發生什麼：
SQL> select sid from v$mystat where rownum = 1;
SID
----------
10
SQL> commit;
Commit complete
檢視session 12，發現已經完成了update語句：
SQL> update scott.dept a set a.dname = 'dname' where a.deptno = 10;
1 row updated
SQL>
再看看前面的查詢語句：
SQL> select /*+ rule*/
2 a.SID,
3 a.BLOCK,
4 a.REQUEST,
5 a.LMODE,
6 d.XIDUSN,
7 d.XIDSLOT,
8 d.XIDSQN,
9 e.OBJECT_ID,
10 e.LOCKED_MODE
11 from v$lock a,
12 v$transaction d,v$locked_object e
13 where a.SID in (10,12)
14 and a.TYPE = 'TX'
15 and a.SID = e.SESSION_ID
16 and a.ADDR = d.ADDR(+)/*現在session 10已經commit了 */
17 ;
SID BLOCK REQUEST LMODE XIDUSN XIDSLOT XIDSQN OBJECT_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
12 0 0 6 9 42 2544 30137 3
可以很清楚的看到變化，session 12不在被block，請求也完成了，獲得了6的鎖定，事務id已經生成，而session 10的事務已經完成，已經釋放掉了鎖定和資源，v$transaction和v$lock中已經沒有相應記錄。
TM（dml入隊）鎖定
Tm鎖用來保證修改表資料的時候，表結構不被修改。例如：
在一個session中(10)更新表scott.dept，而在另外一個session中（12）中修改表結構，則會出現下面的情況：
SQL> alter table scott.dept add (add_col number);
alter table scott.dept add (add_col number)
ORA-00054: resource busy and acquire with NOWAIT specified.
仍然繼續修改在講述TX鎖時使用的查詢：
SQL> select /*+ rule*/
2 a.SID,
3 a.ID1,
4 a.TYPE,
5 a.BLOCK,
6 a.REQUEST,
7 a.LMODE,
8 d.XIDUSN,
9 d.XIDSLOT,
10 d.XIDSQN,
11 e.OBJECT_ID,
12 e.LOCKED_MODE
13 from v$lock a,
14 v$transaction d,v$locked_object e
15 where a.SID in (10,12)
16 and a.TYPE in ( 'TM','TX')
17 and a.SID = e.SESSION_ID
18 and a.ADDR = d.ADDR(+)/*現在session 10已經commit了 */
19 ;
SID ID1 TYPE BLOCK REQUEST LMODE XIDUSN XIDSLOT XIDSQN OBJECT_ID LOCKED_MODE
---------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
10 30137 TM 0 0 3 30137 3
10 131097 TX 0 0 6 2 25 2545 30137 3
注意到tm鎖的id1就是tx鎖的物件id。
DDL鎖定
主要有三種：
獨佔的ddl鎖定
共享ddl鎖定
Breakable parse lock
像前面示例中的：
alter table scott.dept add (add_col number);
就是獨佔的ddl鎖。
DBA_DDL_LOCKS檢視
Session_id session id
Owner owner of the lock
Name name of the lock
Type
鎖定型別，ddl的鎖定型別：
Cursor ，Table/Procedure/Type，Body，Trigger，Index，Cluster，Java Source，Java Resource，Java Data
Mode_held和Mode_request分別是Lock mode和Lock request type，包括：
通常在重新編譯包或者過程的時候，實際已經有使用者在執行，則編譯會被掛起，或者在grant某個正在執行的過程時，也會發生這樣的情況，查詢DBA_DDL_LOCKS檢視可以找到問題的具體所在。
具體將專門在附錄中分析鎖定相關的問題集。
LMODE（鎖定模式）和REQUEST（請求的鎖定模式）
會話擁有的鎖的模式：
0：none
1：null 空
2：row-s 行共享(RS):共享表鎖，用於行的查詢
3：row-x 行專用（RX）：用於行的修改，通常表明持有該鎖的事務已經完成了對行的修改
4：share 共享鎖（S）：阻止其他dml操作
5：s/row-x 共享行專用（SRX）：阻止其他事務操作
6：exclusive 專用（X）：獨立訪問使用，是表鎖的最具限制性的形式。
Block
當前的鎖是否阻塞了其他的鎖。
鎖定相關的幾個引數：
SQL> show parameter DML_LOCKS;
NAME TYPE VALUE
------------------------------------ -----------
dml_locks integer 748
這個引數用來規定TM鎖的總數，要獲得表鎖需要TM鎖。
SQL> show parameter ROW_LOCKING;
NAME TYPE VALUE
------------------------------------ -----------
row_locking string always
這個引數指定行鎖的方式，ROW_LOCKING該引數指定行封鎖方式。若設定為ALWAYS，則在修改表時只實施行封鎖。若設定為INTENT時，則行封鎖只適用於SELECT FOR UPDATE，而在修改時實施表封鎖。
v$transaction
ADDR Address of the transaction state object 事務物件地址
XIDUSN Undo segment number 回滾段編號
XIDSLOT Slot number 槽（slot）編號
XIDSQN Sequence number 序列編號
UBAFIL Undo block address (UBA) filenum 回滾塊地址在的檔案序號
UBABLK UBA block number uba塊數量
UBASQN UBA sequence number uba序列編號
UBAREC UBA record number uba記錄數
STATUS Status 事務狀態
START_TIME Start time (wall clock)
START_SCNB Start system change number (SCN) base
START_SCNW Start SCN wrap
START_UEXT Start extent number
START_UBAFIL Start UBA file number
START_UBABLK Start UBA block number
START_UBASQN Start UBA sequence number
START_UBAREC Start UBA record number
SES_ADDR User session object address 使用者會話物件地址
FLAG Flag
SPACE YES if a space transaction 是否是空間事務
RECURSIVE YES if a recursive transaction 是否是遞迴事務
NOUNDO YES if a no undo transaction 是否是非撤銷事務
PTX YES if parallel transaction 是否是並行事務
NAME Name of a named transaction 是否命名事務
PRV_XIDUSN Previous transaction undo segment number
PRV_XIDSLT Previous transaction slot number
PRV_XIDSQN Previous transaction sequence number
PTX_XIDUSN Rollback segment number of the parent XID
PTX_XIDSLT Slot number of the parent XID
PTX_XIDSQN Sequence number of the parent XID
DSCN-B Dependent SCN base
DSCN-W Dependent SCN wrap
USED_UBLK Number of undo blocks used 使用的撤銷塊數
USED_UREC Number of undo records used 書用的撤銷記錄數
LOG_IO Logical I/O 邏輯io
PHY_IO Physical I/O 物理io
CR_GET Consistent gets 一致性讀
CR_CHANGE Consistent changes 一致性更改
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-662793/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【開發篇sql】 基礎概述（三） DDL和DMLSQL
- 【開發篇sql】 基礎概述（二） undo和redoSQL
- 一篇和Redis有關的鎖和事務的文章Redis
- sql 開發篇一 之 表鎖查詢及解鎖SQL
- SQL基礎-->資料庫事務(TRANSACTION)SQL資料庫
- MySQL基礎架構和事務MySql架構
- 【學習】SQL基礎-012-鎖定SQL
- SQL鎖機制和事務隔離級別SQL
- MySQL 事務和鎖MySql
- MySQL事務和鎖MySql
- MS SQL基礎教程：備份和恢復概述SQL
- 一天學會PostgreSQL應用開發與管理-6事務和鎖SQL
- 重新整理 mysql 基礎篇————— mysql 事務[三]MySql
- JavaScript基礎（一）概述JavaScript
- mysql基礎_事務MySql
- SQL Server基礎之《檢視的概述和基本操作》SQLServer
- SQL Server中的事務與鎖SQLServer
- mysql事務和鎖InnoDBMySql
- sqlite的事務和鎖SQLite
- Oracle的事務和鎖Oracle
- 視訊開發基礎篇
- 【開發篇plsql】plsql事務處理SQL
- T-SQL:事務鎖下的併發處理（十五）SQL
- Sql Server 資料庫事務與鎖，同一事務更新又查詢鎖？期望大家來解惑SQLServer資料庫
- 分散式基礎（一）概述分散式
- Mysql基礎 --- 索引+事務MySql索引
- iOS開發小記-基礎篇iOS
- iOS 藍芽開發·基礎篇iOS藍芽
- JAVA基礎學習筆記 一 計算機基礎、JAVA開發環境、開發注意事項Java筆記計算機開發環境
- 十、Redis事務、事務鎖Redis
- 分散式事務概述分散式
- ORACLE事務管理概述Oracle
- 動態SQL開發基礎和經驗再總結SQL
- 重新整理 mysql 基礎篇————— 事務隔離級別[四]MySql
- MySQL – 事務的啟動 / 設定 / 鎖 / 解鎖——入門MySql
- redis（10）事務和鎖機制Redis
- Redis的事務、樂觀鎖和悲觀鎖Redis
- Flask RESTful API 開發----基礎篇 (1)FlaskRESTAPI