談到oracle的鎖機制，這是oracle和其他數據庫區別比較大的地方，為了更好的解釋其中的原來(lái)，我們在這篇文章中主要介紹tx lock,所以以下提到的lock均代表tx lock。 在很多其他數據庫中，lock實(shí)際上是通過(guò)一個(gè)in-meory lock list來(lái)實(shí)現的，當有session請求一個(gè)lock時(shí)，它會(huì )鎖定lock list，然后去搜索lock list看是否這條記錄上有別的lock，如果沒(méi)有就創(chuàng )建一個(gè)lock list entry，然后unlock lock list。 在oracle中，我們先會(huì )定位到修改的記錄在哪個(gè)block，哪條記錄，如果有別的active transaction也是修改這條記錄，那么會(huì )在enqueue lock fixed array里面創(chuàng )建一個(gè)對象（按請求lock的時(shí)間順序），排隊等待前一個(gè)事務(wù)commit或rollback，同時(shí)設置timeout時(shí)間，如果發(fā)生timeout則再去檢查請求的lock是否已經(jīng)可用。如果沒(méi)有別的active transaction占有lock,那么它會(huì )在enqueue resource fixed array里面創(chuàng )建一個(gè)對象,并修改block的itll Lck標志位，修改記錄lb標志位指向事務(wù)所在的itl。如果事務(wù)結束，將會(huì )去檢查enqueue lock array,enqueue conversion array，并通知等待最久的那個(gè)事務(wù)可以請求lock。關(guān)于enqueue lock，enqueue resource,enqueue resource以及和這些結構相關(guān)的一些初始化參數請詳見(jiàn)steve adams的《oracle8i internal services for waits, latches, locks》。 可以看到oracle其實(shí)是把row-level lock直接在block里面實(shí)現了，不像其他數據庫要為每一條需要修改的記錄創(chuàng )建一個(gè)lock list對象，oracle只需要針對每個(gè)transaction來(lái)創(chuàng )建一些結構。所以在oracle里面，lock并不是惜缺資源。 下面來(lái)看一下block內lock的處理 SQL 9I>select * from test; A
---------
14-DEC-05
SQL 9I>select dbms_rowid.ROWID_RELATIVE_FNO(rowid),dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from test; DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
13 13 SQL 9I>alter system dump datafile 13 block 13; System altered. Start dump data blocks tsn: 10 file#: 13 minblk 13 maxblk 13
buffer tsn: 10 rdba: 0x0340000d (13/13)
scn: 0x0005.102623f0 seq: 0x01 flg: 0x00 tail: 0x23f00601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0340000d
Object id on Block? Y
seg/obj: 0x12b22 csc: 0x05.102623f0 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3400009 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.018.0001ce00 0x00000000.0000.00 C--- 0 scn 0x0005.102623e5
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xad7ec7c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0ad7ec7c
bdba: 0x0340000d
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f61
tosp=0x1f61
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 11 fb: --H-FL-- lb: 0x0 cc: 1 --lb指向0x0的itl，表示這條記錄沒(méi)有被修改過(guò)，所以指向一個(gè)空itl
col 0: [ 7] 78 69 0c 0e 11 28 2b
end_of_block_dump
End dump data blocks tsn: 10 file#: 13 minblk 13 maxblk 13 SQL 9I>update test set a=sysdate; 1 row updated. SQL 9I>alter system dump datafile 13 block 13; System altered. update了一下在dump block來(lái)看 Start dump data blocks tsn: 10 file#: 13 minblk 13 maxblk 13
buffer tsn: 10 rdba: 0x0340000d (13/13)
scn: 0x0005.1026243b seq: 0x01 flg: 0x00 tail: 0x243b0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0340000d
Object id on Block? Y
seg/obj: 0x12b22 csc: 0x05.102623f0 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3400009 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.018.0001ce00 0x00000000.0000.00 C--- 0 scn 0x0005.102623e5
0x02 0x0008.023.0001c861 0x0080007c.1dfa.02 ---- 1 fsc 0x0000.00000000 0x02的itl的lck標志位為1，表示鎖定了一條記錄，flag表示是未遞交的。
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xad7ec7c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0ad7ec7c
bdba: 0x0340000d
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f61
tosp=0x1f61
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 11 fb: --H-FL-- lb: 0x2 cc: 1 --指向了0x2的itl
col 0: [ 7] 78 69 0c 0e 11 2c 2c
end_of_block_dump
End dump data blocks tsn: 10 file#: 13 minblk 13 maxblk 13 SQL 9I>commit; Commit complete. SQL 9I>alter system dump datafile 13 block 13; System altered. commit后我們來(lái)dump block Start dump data blocks tsn: 10 file#: 13 minblk 13 maxblk 13
buffer tsn: 10 rdba: 0x0340000d (13/13)
scn: 0x0005.10262467 seq: 0x01 flg: 0x02 tail: 0x24670601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0340000d
Object id on Block? Y
seg/obj: 0x12b22 csc: 0x05.102623f0 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3400009 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.018.0001ce00 0x00000000.0000.00 C--- 0 scn 0x0005.102623e5
0x02 0x0008.023.0001c861 0x0080007c.1dfa.02 --U- 1 fsc 0x0000.10262467 0x02的lck標志依然是1，但是flag已經(jīng)是U,表示事務(wù)已經(jīng)遞交，lock已經(jīng)被釋放
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xad7ec7c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0ad7ec7c
bdba: 0x0340000d
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f75
avsp=0x1f61
tosp=0x1f61
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f75
block_row_dump:
tab 0, row 0, @0x1f75
tl: 11 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 7] 78 69 0c 0e 11 2c 2c
end_of_block_dump
End dump data blocks tsn: 10 file#: 13 minblk 13 maxblk 13 就像上面所提到的，當一個(gè)session請求lock時(shí)，它先去觀(guān)察block內記錄的lb標志，然后回到itl判斷有沒(méi)有未遞交事務(wù)，如果有它就開(kāi)始等待，如果沒(méi)有就去更新lb,itl。 另外，等待lock的進(jìn)程將會(huì )產(chǎn)生更多的consistent gets，db block gets session 1： SQL 9I>set autotrace trace;
SQL 9I>update test set a=sysdate; 1 row updated.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF ‘TEST‘
2 1 TABLE ACCESS (FULL) OF ‘TEST‘
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
3 consistent gets
0 physical reads
376 redo size
619 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed session 2：
SQL 9I>set autotrace trace;
SQL 9I>update test set a=sysdate; waiting...... session 1: SQL 9I>commit; Commit complete.
session 2: SQL 9I>update test set a=sysdate; 1 row updated.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF ‘TEST‘
2 1 TABLE ACCESS (FULL) OF ‘TEST‘
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
6 consistent gets
0 physical reads
492 redo size
613 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
這是因為等待的進(jìn)程需要做兩次全表掃描，第一次讀的時(shí)候發(fā)現被其他進(jìn)程鎖定，其他進(jìn)程釋放鎖后又重新讀了一次，db block gets也是同樣道理。 還有一個(gè)實(shí)驗能證明enqueue是按請求時(shí)的時(shí)間順序排列的 session 1: SQL 9I>lock table test in share mode; Table(s) Locked. session 2: SQL 9I>lock table test in exclusive mode;
waiting...... session 3: SQL 9I>lock table test in share mode;
waiting...... 因為session 1和session 3 lock mode并不排斥，但是由于session 3請求時(shí)間比session 2晚，而session 2和session 1的lock mode沖突，所以導致session 3也不能獲得lock; oracle lock 機制還是挺有意思的一個(gè)東西，tom和steve分別從不同角度很好的展示了oracle的lock機制，希望對這方面有興趣的oracle fans好好看看tom的expert one-on-one和steve的oracle8i internal services for waits, latches, locks.
