数据块最前有一个开销空间,这里会存放该块的一个事务表。对于锁定了该块中某些数据的各个事务,在这个事务表里都有一个相对应的条目。
事务表的结构大小由创建对象时CREATE语句参数决定 INITRANS:结构初始的预分配大小。对于索引和表默认为2
EODA@PROD1> create table t( x int primary key, y varchar2(4000));
Table created.
EODA@PROD1> insert into t(x,y) select rownum, rpad('*',148,'*') from dual connect by level <= 46; --保证所有字符都在一个块上。
46 rows created.
EODA@PROD1> select length(y),
2 dbms_rowid.rowid_block_number(rowid) blk,
3 count(*), min(x), max(x)
4 from t
5 group by length(y), dbms_rowid.rowid_block_number(rowid);
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
---------- ---------- ---------- ---------- ----------
148 194595 46 1 46
EODA@PROD1> create or replace procedure do_update( p_n in number ) --使用自治事务,利用反复递归锁定每条记录,如果出现等待则会出现ORA-54错误
2 as
3 pragma autonomous_transaction;
4 l_rec t%rowtype;
5 resource_busy exception;
6 pragma exception_init( resource_busy, -54 );
7 begin
8 select *
9 into l_rec
10 from t
11 where x = p_n
12 for update NOWAIT;
13
14 do_update( p_n+1 );
15 commit;
16 exception
17 when resource_busy
18 then
19 dbms_output.put_line( 'locked out trying to select row ' || p_n );
20 commit;
21 when no_data_found
22 then
23 dbms_output.put_line( 'we finished - no problems' );
24 commit;
25 end;
26 /
Procedure created.
EODA@PROD1> exec do_update(1); --在执行第38行时事务槽用完。
locked out trying to select row 38
PL/SQL procedure successfully completed.
EODA@PROD1> truncate table t;
Table truncated.
EODA@PROD1> insert into t (x,y) select rownum, rpad('*',147,'*') from dual connect by level <= 46; --尝试比上一次小一个字符的字符串
46 rows created.
EODA@PROD1> select length(y),
2 dbms_rowid.rowid_block_number(rowid) blk,
3 count(*), min(x), max(x)
4 from t
5 group by length(y), dbms_rowid.rowid_block_number(rowid);
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
---------- ---------- ---------- ---------- ----------
147 194595 46 1 46
EODA@PROD1> exec do_update(1); --成功完成
we finished - no problems
PL/SQL procedure successfully completed.
从这个例子可以看出,如果多个事务试图同时访问同一块时会发生什么情况。如果并发事务数过多,可能会出现等待事务表的情况。如果INITRANS设置过低,而且块上又没有足够的空间动态扩展事务,也会出现阻塞。大多数情况下,INITRANS默认设为2就足够了,因为事务表会动态扩大。
EODA@PROD1> select ini_trans from user_tables where table_name= 'T'; --虽然查询看到INITRANS为1,但是下面通过bbed工具查看结果并不一样
INI_TRANS
----------
1
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00014c6b
ub4 ktbbhod1 @24 0x00014c6b
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x0021a993
ub2 kscnwrp @32 0x0000
sb2 ktbbhict @36 3
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01014c02
struct ktbbhitl[0], 24 bytes @44 --第一个ITL
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0011
ub2 kxidslt @46 0x0001
ub4 kxidsqn @48 0x000000da
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00000000
ub2 kubaseq @56 0x0000
ub1 kubarec @58 0x00
ub2 ktbitflg @60 0x0000 (NONE)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x00000000
struct ktbbhitl[1], 24 bytes @68 --第二个ITL
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
struct ktbbhitl[2], 24 bytes @92 --第三个ITL
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x0000
ub2 kxidslt @94 0x0000
ub4 kxidsqn @96 0x00000000
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00000000
ub2 kubaseq @104 0x0000
ub1 kubarec @106 0x00
ub2 ktbitflg @108 0x0000 (NONE)
union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
显而易见这个值应该是3
--参考来源《Oracle编程艺术深入理解数据库体系结构(第三版)》