Deadlock-TX locks wait S

Here’s an example of a slightly less common data deadlock graph (dumped from 11gR2, in this case):

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-00010006-00002ade        16      34     X             12      50           S

TX-00050029-000037ab        12      50     X             16      34           S

session 34: DID 0001-0010-00000021  session 50: DID 0001-000C-00000024

session 50: DID 0001-000C-00000024  session 34: DID 0001-0010-00000021

Rows waited on:

Session 50: no row

Session 34: no row

Information on the OTHER waiting sessions:

Session 50:

  pid=12 serial=71 audsid=1560855 user: 52/TEST_USER

  O/S info: user: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

            program: sqlplus.exe

  application name: SQL*Plus, hash value=3669949024

  Current SQL Statement:

  update t1 set n3 = 99 where id = 100

End of information on OTHER waiting sessions.

Current SQL statement for this session:

update t1 set n3 = 99 where id = 200

The anomaly is that the waiters are both waiting on S (share) mode locks for a TX enqueue.

It’s fairly well known that Share (and Share sub exclusive, SSX) lock waits for TM locks are almost a guarantee of a missing “foreign key index”; and it’s also fairly well known that Share lock waits for TX locks can be due to bitmap collisions, issues with ITL (interested transaction list) waits, various RI (referential integrity) collisions including simultaneous inserts of the same primary key.

A cause for TX/4 waits that is less well known or overlooked (because a feature is less-well used) is simple data collisions on IOTs (index organized tables). In the example above t1 is an IOT with a primary key of id. Session 34 and 50 have both tried to update the rows with ids 100 and 200 – in the opposite order. If this were a normal heap table the deadlock graph would be showing waits for eXclusive TX locks, because it’s an IOT (and therefore similar in some respects to a primary key wait) we see waits for Share TX locks.