Ask The Real Tom

The last tree weeks i was hounting a ora-60 deadlock.

The Oracle Supoort claimed it was an application problem.

The Developer stated that it’s impossible an application problem.

Verfrifiend the developers statment proved me to believe him. A row lock was impossibel.

The Trace looked like this:


information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-001c0049-000614c5 92 196 X 85 413 S
TX-00140014-0006def8 85 413 X 92 196 S
session 196: DID 0001-005C-0000000E session 413: DID 0001-0055-00000015
session 413: DID 0001-0055-00000015 session 196: DID 0001-005C-0000000E
Rows waited on:
Session 413: obj - rowid = 000D1ED1 - AADR7RABFAAAxTmAAA
(dictionary objn - 859857, file - 69, block - 201958, slot - 0)
Session 196: obj - rowid = 000D1ED5 - AADR7YABQAAAn43AAA
(dictionary objn - 859861, file - 80, block - 163383, slot - 0)


This deadlock was caused by two update statements.

But if would be a row deadlock the session should wait for an X excluive row lock, not an S sahred lock.

If it would be a classic ITL lock the block would be in the undo tablespace.

On metalink (or the new bad my oracle support) did not help, oracle support could not help.

The nessesary infomation if found here:
http://pjsrandom.wordpress.com/2006/02/28/hunting-deadlocks-part-2/
http://oracledoug.com/serendipity/index.php?/archives/1014-Being-Open-minded.html



Solution:
The table had a initrans of 2 and pct_free of 0
Since I changed that and moved the table the deadlocks are gone.