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.