Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vineet.

Asked: November 27, 2015 - 2:20 pm UTC

Last updated: November 28, 2015 - 4:38 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,

I have been facing issues of deadlocks in my Production database. I dig around and checked the trace file that got generated. Here is the snippet from the trace files:-

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0020001e-0030afd8 171 638 X 169 521 S
TX-0027001f-0025e8dd 169 521 X 171 638 S

session 638: DID 0001-00AB-0000651D session 521: DID 0001-00A9-000079A8
session 521: DID 0001-00A9-000079A8 session 638: DID 0001-00AB-0000651D

There are no bitmap indexes on the table. Also there are no referential constraints on this table. I am suspecting it to be due to shortage of ITL slots. I checked v$segment_statistics and the table has the value greater than 0 for ITL waits.

Please confirm whether this whether the deadlock is because of an ITL wait

Thanks
Vineet

and Connor said...

The share mode "S" suggests either ITL, or competition for a key in an IOT. In my experience, when both are "S" its normally the latter, eg

Session 1:
- lock PK=1
- lock PK=2

Session 2:
- lock PK=2
- lock PK=1

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Vineet Kurrewar, November 28, 2015 - 9:38 am UTC

Hello Connor,

Thank you for the response.

The table in question in this case is not an IOT table.

So can I consider this deadlock occurrence because of an ITL slot shortage

Thanks
Vineet


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.