Oracle & Starcraft

谈到oracle的锁机制，这是oracle和其他数据库区别比较大的地方，为了更好的解释其中的原来，我们在这篇文章中主要介绍tx lock,所以以下提到的lock均代表tx lock。

在很多其他数据库中，lock实际上是通过一个in-meory lock list来实现的，当有session请求一个lock时，它会锁定lock list，然后去搜索lock list看是否这条记录上有别的lock，如果没有就创建一个lock list entry，然后unlock lock list。

在oracle中，我们先会定位到修改的记录在哪个block，哪条记录，如果有别的active transaction也是修改这条记录，那么会在enqueue lock fixed array里面创建一个对象（按请求lock的时间顺序），排队等待前一个事务commit或rollback，同时设置timeout时间，如果发生timeout则再去检查请求的lock是否已经可用。如果没有别的active transaction占有lock,那么它会在enqueue resource fixed array里面创建一个对象,并修改block的itll Lck标志位，修改记录lb标志位指向事务所在的itl。如果事务结束，将会去检查enqueue lock array,enqueue conversion array，并通知等待最久的那个事务可以请求lock。
关于enqueue lock，enqueue resource,enqueue resource以及和这些结构相关的一些初始化参数请详见steve adams的《oracle8i internal services for waits, latches, locks》。

可以看到oracle其实是把row-level lock直接在block里面实现了，不像其他数据库要为每一条需要修改的记录创建一个lock list对象，oracle只需要针对每个transaction来创建一些结构。所以在oracle里面，lock并不是惜缺资源。

下面来看一下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: 0×0340000d (13/13)
scn: 0×0005.102623f0 seq: 0×01 flg: 0×00 tail: 0×23f00601
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
Block header dump: 0×0340000d
Object id on Block? Y
seg/obj: 0×12b22 csc: 0×05.102623f0 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0×3400009 ver: 0×01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0004.018.0001ce00 0×00000000.0000.00 C— 0 scn 0×0005.102623e5
0×02 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×03 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000

data_block_dump,data header at 0xad7ec7c
===============
tsiz: 0×1f80
hsiz: 0×14
pbl: 0×0ad7ec7c
bdba: 0×0340000d
76543210
flag=——–
ntab=1
nrow=1
frre=-1
fsbo=0×14
fseo=0×1f75
avsp=0×1f61
tosp=0×1f61
0xe:pti[0] nrow=1 offs=0
0×12:pri[0] offs=0×1f75
block_row_dump:
tab 0, row 0, @0×1f75
tl: 11 fb: –H-FL– lb: 0×0 cc: 1 –lb指向0×0的itl，表示这条记录没有被修改过，所以指向一个空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来看

Start dump data blocks tsn: 10 file#: 13 minblk 13 maxblk 13
buffer tsn: 10 rdba: 0×0340000d (13/13)
scn: 0×0005.1026243b seq: 0×01 flg: 0×00 tail: 0×243b0601
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
Block header dump: 0×0340000d
Object id on Block? Y
seg/obj: 0×12b22 csc: 0×05.102623f0 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0×3400009 ver: 0×01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0004.018.0001ce00 0×00000000.0000.00 C— 0 scn 0×0005.102623e5
0×02 0×0008.023.0001c861 0×0080007c.1dfa.02 —- 1 fsc 0×0000.00000000
0×02的itl的lck标志位为1，表示锁定了一条记录，flag表示是未递交的。
0×03 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000

data_block_dump,data header at 0xad7ec7c
===============
tsiz: 0×1f80
hsiz: 0×14
pbl: 0×0ad7ec7c
bdba: 0×0340000d
76543210
flag=——–
ntab=1
nrow=1
frre=-1
fsbo=0×14
fseo=0×1f75
avsp=0×1f61
tosp=0×1f61
0xe:pti[0] nrow=1 offs=0
0×12:pri[0] offs=0×1f75
block_row_dump:
tab 0, row 0, @0×1f75
tl: 11 fb: –H-FL– lb: 0×2 cc: 1 –指向了0×2的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后我们来dump block

Start dump data blocks tsn: 10 file#: 13 minblk 13 maxblk 13
buffer tsn: 10 rdba: 0×0340000d (13/13)
scn: 0×0005.10262467 seq: 0×01 flg: 0×02 tail: 0×24670601
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
Block header dump: 0×0340000d
Object id on Block? Y
seg/obj: 0×12b22 csc: 0×05.102623f0 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0×3400009 ver: 0×01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0004.018.0001ce00 0×00000000.0000.00 C— 0 scn 0×0005.102623e5
0×02 0×0008.023.0001c861 0×0080007c.1dfa.02 –U- 1 fsc 0×0000.10262467
0×02的lck标志依然是1，但是flag已经是U,表示事务已经递交，lock已经被释放
0×03 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000

data_block_dump,data header at 0xad7ec7c
===============
tsiz: 0×1f80
hsiz: 0×14
pbl: 0×0ad7ec7c
bdba: 0×0340000d
76543210
flag=——–
ntab=1
nrow=1
frre=-1
fsbo=0×14
fseo=0×1f75
avsp=0×1f61
tosp=0×1f61
0xe:pti[0] nrow=1 offs=0
0×12:pri[0] offs=0×1f75
block_row_dump:
tab 0, row 0, @0×1f75
tl: 11 fb: –H-FL– lb: 0×2 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

就像上面所提到的，当一个session请求lock时，它先去观察block内记录的lb标志，然后回到itl判断有没有未递交事务，如果有它就开始等待，如果没有就去更新lb,itl。

另外，等待lock的进程将会产生更多的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

这是因为等待的进程需要做两次全表扫描，第一次读的时候发现被其他进程锁定，其他进程释放锁后又重新读了一次，db block gets也是同样道理。

还有一个实验能证明enqueue是按请求时的时间顺序排列的

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请求时间比session 2晚，而session 2和session 1的lock mode冲突，所以导致session 3也不能获得lock;

oracle lock 机制还是挺有意思的一个东西，tom和steve分别从不同角度很好的展示了oracle的lock机制，希望对这方面有兴趣的oracle fans好好看看tom的expert one-on-one和steve的oracle8i internal services for waits, latches, locks.