oracle lock mechanism internalPosted: December 14th, 2005, 4:35pm PSTTags: [edit]谈到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: 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,表示这条记录没有被修改过,所以指向一个空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: 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后我们来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已经是U,表示事务已经递交,lock已经被释放 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 就像上面所提到的,当一个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. |
|