How to read trace file in oracle deadlock

When Oracle detects a deadlock, the current SQL in the session detecting the deadlock is cancelled and 'statement-level rollback' is performed so as to free up resources and not block all activity. The session that detected the deadlock is still 'alive' and the rest of the transaction is still active.

If you repeat the last cancelled operation in the session, then you will get the deadlock again. When such a deadlock is detected a trace file is produced containing a "Deadlock Graph" along with other useful information. By examination of numerous Service Requests, we have seen that the most common types of deadlock can be identified by a "signature" deadlock graph that can be used to identify the "type" of deadlock being encountered.

This article presents examples of each type so that investigation and resolution can continue along the right track. The second section is a message from Oracle telling you this is an application issue, not an Oracle error.

The third section lists the blocked SQL statements in the other waiting sessions. The SQL statements listed in the trace file should allow you to identify the application code that is causing the problem. To resolve the issue, make sure that rows in tables are always locked in the same order. No secondary indexes, triggers or anything???? The update statement is part of the code in that trigger. September 21, - pm UTC. I've told you exactly what I wanted to see but I'm not getting it.

I'll guess, since you won't give me what I ask for. You are using an autonomous transaction. You are deadlocking with yourself. I'm pretty sure this is it Are you ssing an autonomous transaction?

If so, you almost certainly have a bug nasty ones that I could show you!! Data integrity issues. Transactional Issues. Design flaws if you felt the need for them if you do, you must remove it -- I am now convinced that autonomous transactions as a feature was a huge mistake.

I've never seen one used in "the real world tm " in a manner that is safe. But, given what I see, I'm almost sure thats what you've done here, you are shooting yourself in the foot. In the future, please just post what is asked for or say "sorry, I cannot, I'll try some things myself".

A reader, September 21, - pm UTC. A reader, November 24, - am UTC. In following case how to find out objects involved in deadlock. It's showing no rows and problem is with delete, not with update. Also, what does it mean when you have 2 same Resource Name? I know that second package call first one and delete statement is in that first one. Is resource name name of that Stored Procedure? November 29, - pm UTC. That is last statement in the Stored Procedure. Before it, all child records are deleted.

November 30, - am UTC. Party table has 5 child tables. I find one thing, but I am not sure is that relevant I changed it any way. This is not table with index on foreign key. November 30, - pm UTC.

Can you tell me what does it mean when you have 2 same resource names, but sessions are different? What is resource name? December 01, - pm UTC. Tom, We give below the dead lock graph and the Cursor frame allocation dump. Appears to be due to unique indexes.. If so, how to correct this? July 07, - am UTC. Tom, What could be the inference drawn from this when not due to unique index? How to take this forward towards understanding and fixing it up?

July 12, - pm UTC. I always start by understanding the table and indexes and always being suspicious of unindexed foreign keys. Tom, I give below the table and index details. This is a stand alone table. Update is done using rowid. July 13, - am UTC. Tom, Could you please help me on this.

I wanted to know the reason behind getting two different object ids. July 29, - am UTC. The data object id and object id are different for it. Thank you tom for your prompt reply. This is the first time that i had asked you a question and i was thinking that i would be lucky if you answer it in a month. But you made my day. The deadlock trace file shows the cursors that are currently executing.

Is it possible to identify the session which executed that cursor. July 29, - pm UTC. Thanks Tom, I thought the cursor information was for all the sessions that were executing when the deadlock occurred.

Currently we are facing a deadlock situation in which two sessions are trying to update the same table. From the trace file i can find out which row were they exactly trying to update. The situation is like this. In such a situation is it safe to assume that session 1 is waiting on lock on employee 20 and session 2 is waiting on lock on employee 10, or could there be any other possibility.

Thanks a lot tom. July 30, - am UTC. No tom there isn't an issue of unindexed foreign key. I had run the script given in your book Expert One-On-One to detect unindexed foreign keys. The table is actually an error log table and not employees table as i had put in my previous post which stores information about errors that occur during the session. There are very frequent updates on this table. August 01, - am UTC. Hi, and another one on indexes.

Please have a look at : Note Regards, Virgile. Hi Tom, came across an deadlock issue. It was an index on a table which caused the problem. The table doesn't have any unindexed foreign key. It is a base table which has around 0.

The data size may grow large in future. Provide your suggestions. August 11, - am UTC. Tom, Is it possible to get some information relevant to deadlocks from statspack report? I have been asked to analyze the report 1 hour before the deadlock occurred and the hour during which the deadlock occurred. Thanks, Khalid. Tom, yes the deadlock occured in the index. It was bitmap index. Do i need to do any design changes? August 17, - am UTC. Or where you do single row modifications.

Tom, What other type index will avoid the reoccurance of deadlock.? Thanks, Senthil. August 30, - pm UTC. Tom, I am trying to understand a deadlock problem ,occuring at one of our production site.

In the thread i have seen locks on resources as TM or TX only, where as in this trace file i got UL but the resources are having exclusive locks and are waiting for exclusive locks as well. So why i am not getting the same index name via rowid for session Why its so.

Thanks Vivek. September 30, - am UTC. LOCKS you are creating your own. Dead locks on insert. Tom, Would appreciate if you could help us in analyzing the trace. Thx in advance. January 10, - pm UTC. Tom, thx for the prompt response.

The process can be summarized as follows. The data is loaded in a temporary table using sqlldr 2. Depending on one of the data column, the data is either inserted or updated in to this table i. These processes ksh with different parameter values run in parallel depending on the number of transactions pending for processing. There is only one commit in the stored procedure at the end of processing.

One of the session from the trace is waiting on for a shared lock on this table. The second session in the deadlock graph is waiting on for a similar lock on the Primary Key index of the same table i. If the status is set to 0, this means traces are not running.

You must start the trace using following command:. So one must Start the traces after instance restart. So, we must create a deadlock so that it appears in logs. For that we create two tables that will take part in deadlock. After table creation, in two separate sessions we must execute following update statements in sequence mentioned below in order for deadlock to occur. Notice one of the session executed successfully and one failed because it was declared as deadlock victim.

First thing to note here is that SQL Server logs are read bottom up, meaning last event is printed at the top and first event is printed at the bottom. Every deadlock starts with the message Deadlock encountered …. Printing deadlock information. Anything above this message is the information about the deadlock. Deadlocks are divided into different Nodes.

In our case there are only two nodes, but in different scenarios there could be many nodes. A node displays the object which is granted to one session and is being requested by another session.

Every node has a Grant List and Requested By list.