Chasing Oracle deadlocks part II – block level locks

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.