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.
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