Search

Top 60 Oracle Blogs

Recent comments

Knowing the trend of Deadlock occurrences from the Alert Log

Recently, my client deployed a new application and had this intermittent “Deadlock Storm” …

A trace file was sent and I was able to pinpoint the cause of the deadlock and the session that caused it.
The deadlock was a TX enqueue with mode of 4 (S – share) which could be verified by looking at the following lines of the Process State dump:

   last wait for 'enq: TX - row lock contention' blocking sess=0x 7000000cb239d60 seq=7849 wait_time=2929705 seconds since wait started=3
            name|mode=54580004, usn<<16 | slot=a0028, sequence=283f2

the "enqueue and lock mode" is explained as:
mode=54580004 (see above)
5458 (hex) = TX (ascii)
0004 (hex) = mode 4 (S – share)

The possible cause would be multiple concurrent sessions insert the same key value into a table that has one or more unique key. The table involved on the SQL was identified and has a unique key (on 7 columns) and 4 foreign keys (on different tables). But still I have to verify…

And I may have to look into the application logic, and may have to start with the insert statement causing the deadlock.
But, it could also be possible that there are more statements involved in this “Deadlock storm”.. and I’m just looking on one occurence..
hmm.. interesting..

Lately, I’ve been using this shell command I got from Kyle Hailey‘s site for quickly getting ORA- errors on the alert log.

See the code I’m using below:

Go to the bdump directory to run these shell commands

 Date and errors in alert.log

     cat alert_orcl.log | \
     awk 'BEGIN{buf=""}
          /[0-9]:[0-9][0-9]:[0-9]/{buf=$0}
          /ORA-/{print buf,$0}' > ORA-errors_orcl.txt

On the client site, I executed the command on the alert log. Then to my surprise.. there are so many ORA-00060 errors (total of 1,616 lines)! which means, there are more trace files that I have to look on. Great, Now I’m overwhelmed. </p />
</p></div>

    	  	<div class=