事务表结构大小探究_事务表和格结构-CSDN博客

本文链接：https://blog.csdn.net/u013169075/article/details/52851817

数据块最前有一个开销空间，这里会存放该块的一个事务表。对于锁定了该块中某些数据的各个事务，在这个事务表里都有一个相对应的条目。

事务表的结构大小由创建对象时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编程艺术深入理解数据库体系结构（第三版）》