Ask The Real Tom

October 12, 2009



Oracle Deadlock ORA-00060 – Wait for Shared Lock

Filed under: oracle — Tags: , , — admin @ 2:11 pm

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.

2 Comments »

  1. Hi,

    The information in this site is very useful. Thanks for your service to oracle community.

    Could you please elaborate on this?

    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.

    Questions:
    1. initrans has been changed from 2 to ??

    2. Why did you moved the table ??

    This statement is totally confusing ” Since I changed that and move the table”
    Thanks in advance
    Richard

    Comment by Richard — June 27, 2011 @ 7:00 am

  2. The initrans is a storage parameter, just changing the definition on a table will not have any effect on existing block. If the table is move all blocks are touched an the parameter is applied.

    Comment by admin — August 3, 2011 @ 8:11 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress