Skip to Content

After we had a look at the most straight forward occurrences of deadlocks (caused by row level locks), we will now have a look at a nastier problem: block level locks.

Test case

SQL> select * from sapbut.ztesting;

AAA       BBB
—–    ——-
aaa       bbb
ccc        ddd
eee       fff
ggg       hhh

SQL> select * from sapbut.ztesting1;

AAA       BBB
—–    ——-
aaa       bbb
ccc        ddd
eee       fff
ggg       hhh

SQL> select * from sapbut.ztesting2;

AAA       BBB
—–    ——-
aaa       bbb
ccc        ddd
eee       fff
ggg       hhh

Session 7:

SQL> update sapbut.ztesting1 set aaa = ‘…’ where bbb = ‘bbb’;

1 row updated.

SQL> update sapbut.ztesting set aaa = ‘…’ where bbb = ‘bbb’;

session 19:

SQL> update sapbut.ztesting set aaa = ‘…’ where bbb = ‘ddd’;

1 row updated.

SQL> update sapbut.ztesting1 set aaa = ‘…’ where bbb = ‘ddd’;

1 row updated.

Session 20:

SQL> update sapbut.ztesting2 set aaa = ‘…’ where bbb = ‘bbb’;

1 row updated.

SQL> update sapbut.ztesting set aaa = ‘…’ where bbb = ‘fff’;

-> the statement now hangs

This looks a little bit strange. Why is session 20 hanging? Obviously, there is
no other transaction that locks the row that contains ‘fff’ in the bbb column.
Let’s have a look at v$lock:

select sid, type, lmode, request from v$lock where type = ‘TX’

       SID  TYPE    LMODE    REQUEST
     —— —— ———- ———-

         7    TX          6          0
        19   TX          6          0
        20   TX          0          4
        20   TX          6          0

We can see that all 3 Sessions hold an exclusive TX lock. This is what we expect since
each session exclusively locks rows. But we can also see that session 20 is currently
requesting a shared lock and therefore hanging. Why a shared lock? We want to
lock the row in question exclusively…
This S lock has a different meaning here. We do not request a row lock, but a free entry in the so called ITL (Interested Transaction List) that is part of the block header of the database blocks.
The ITL is a data structure that contains slots. Whenever a transaction locks a certain row (which is contained in a block), this transaction occupies a slot in this list.
When Session 20 has to wait for a slot, we know that there are already two entries in the ITL list of the block for which a row lock was requested.
Please note: Since the involved tables in this example are very small, all rows of one table fit into one DB block.

Session 7:

SQL> update sapbut.ztesting2 set aaa = ‘…’ where bbb = ‘bbb’;

-> This causes the following error in Session 20:

SQL> update sapbut.ztesting set aaa = ‘…’ where bbb = ‘fff’;
update sapbut.ztesting set aaa = ‘…’ where bbb = ‘fff’
              *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Now we can see a classic deadlock situation:

Session 7 requests an exclusive lock for a row that is already locked by session 20.
Unfortunately, session 20 is stuck because it waits for an ITL slot.
BUT: This ITL slot is hold be session 7…

How does the deadlock graph looks like?

Deadlock graph:                                                               
                                Blocker(s)                                   Waiter(s)        
Resource Name          process session holds waits  process session holds waits
TX-00090027-0000498a        20      20    X              8       7                    X
TX-00020028-00004b2d         8       7     X             20      20                   S

Please not that one of the sessions has the S flag set. This does mean that we now have a real Oracle deadlock that is NOT related to the application logic.

What can be done now?

As already mentioned, the problem is caused by the insufficient number of slots in the ITL list. This number is determined by 2 parameters: initrans and maxtrans. Starting with initrans, maxtrans slots can be allocated in the block header.
These parameters are usually set when the database object is created:

create table sapbut.ztesting(aaa varchar(20), bbb varchar(20)) initrans 1 maxtrans 2

After object creation, these parameters can be queried via dba_tables:

select table_name, ini_trans, max_trans
from dba_tabes
where table_name = ‘ZTESTING’

TABLE_NAME  INITRANS   MAXTRANS
————-   ———-     ———-

ZTESTING           1                2

You should now follow the recommendations from note #84348:

  • As described in the previous block, determine the affected object
  • Increase initrans, maxtrans for tables / partitions / indices

IMPORTANT I:
If you change initrans and maxtrans after object creation, these settings are only applied to newly allocated blocks. For allocated blocks, you would need to reorganize the table so that these settings are applied.

IMPORTANT II:
With Oracle 10,  maxtrans is set to 255 by default. Of course, this only applies to newly created objects, after your DB upgrade you could still run into the problem for all objects that were created pre Oracle 10.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Stefan Koehler
    Hello Michael,
    a really good blog article with some nice proof.
    I really appreciate your engagement to the customers and the community.

    But let me just add some additional information to your article.

    – If you specify INITRANS 1 – Oracle will also allocate 2 ITL slots per default
    – If there aren’t enough ITL slots for the new transaction and PCTFREE is not reached oracle allocates new ITL slots automatically in the block and no ITL wait is shown.
    – MAXTRANS is deprecated in Oracle 10g (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses007.htm#g1058547)

    Here is just my little proof for that on Oracle 10.2.0.2.0:
    ===============================================
    SQL> create table ztesting(aaa varchar(20), bbb varchar(20)) initrans 1 maxtrans 2;

    SQL> select table_name, ini_trans, max_trans from user_tables where table_name = ‘ZTESTING’;
    TABLE_NAME                      INI_TRANS  MAX_TRANS
    —————————— ———- ———-
    ZTESTING                                1        255

    SQL> select SEGMENT_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS from DBA_EXTENTS where SEGMENT_NAME = ‘ZTESTING’;
    SEGMENT_NA  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
    ———- ———- ———- ———- ———-
    ZTESTING            0          6     372497          8

    SQL> alter system dump datafile 6 block 372503;
    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
    0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
    0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

    SQL> insert into ZTESTING values (‘aaa’,’bbb’);
    SQL> insert into ZTESTING values (‘ccc’,’ddd’);
    SQL> insert into ZTESTING values (‘eee’,’eee’);
    SQL> commit;

    SQL> SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), aaa, bbb FROM ZTESTING;
    DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AAA                  BBB
    ———————————— ——————– ——————–
                                  372503 aaa                  bbb
                                  372503 ccc                  ddd
                                  372503 eee                  eee

    SQL> alter system dump datafile 6 block 372503;
    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
    0x01   0x000b.017.0004f134  0x3fc02d81.3e61.28  –U-    3  fsc 0x0000.e1476bd1
    0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

    Session 1:
    SQL> update ZTESTING set aaa=’…’ where bbb = ‘bbb’;
    Session 2:
    SQL> update ZTESTING set aaa=’…’ where bbb = ‘ddd’;
    Session 3:
    SQL> update ZTESTING set aaa=’…’ where bbb = ‘eee’;

    SQL> alter system dump datafile 6 block 372503;
    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
    0x01   0x000b.006.0004efc8  0x4000051e.3e62.04  —-    1  fsc 0x0000.00000000
    0x02   0x0003.01b.00045a44  0x3f80949b.3da5.02  —-    1  fsc 0x0000.00000000
    0x03   0x0002.003.0004601d  0x40808a9a.3ca7.0e  —-    1  fsc 0x0000.00000000
    ===============================================

    – At the creation the MAXTRANS parameter was ignored
    – After the first 3 inserts (one transaction) the first ITL slot was used for it (see Lck 3) and the row entries are pointing to ITL slot lb: 0x1.
    – After updating the 3 rows in 3 different sessions (3 transactions and each ITL Lck 1) a new ITL slot was allocated dynamically in the block

    Regards
    Stefan

    (0) 

Leave a Reply