Finding source of deadlock

Finding source of deadlock

Post by Rajib Sarka » Wed, 26 Jun 2002 05:44:27



Yes, that's true ..:-)

In order to find the deadlock monitoring the onstat -k output with onstat
-x and onstat -u output will help ...:-)

The owner of onstat -k will map to the session of onstat -u output.

Not much of an help ..:-) but you would require to do some combinations of
the above to track the session which is causing the DEADLOCK.

Thanx much,

Rajib Sarkar
Advisory Support Engineer(Wells Fargo Bank)
IBM Software Group -- Data Management
Ph: 602-2172100, Fax: 602-2172100

www.ibm.com/software

                      Doober                                                                                                            

                      Sent by:                  cc:                                                                                    
                      owner-informix-li         Subject: Finding source of deadlock                                                    

                      06/19/2002 06:44                                                                                                  
                      PM                                                                                                                
                      Please respond to                                                                                                
                      Doober                                                                                                            

It's the classic SQLCODE -243/ ISAM -143 deadlock error (I typed those
numbers from memory, so I hope they're correct).

We recently modified a program and it started sporadically getting this
error on an OPEN cursor statement.

The coder thinks the program is deadlocking against itself (meaning it's
own transaction - not another instance of the same program).

I don't even think that's possible.  (1st question:  Is it even
possible?)

Of course, this never showed up during QA on our non-production box, and
when we rerun it, it has always completed.

This is enough evidence for me to leap to the "wild" conclusion that it
is deadlocking against some other process.

2nd question:  Is there any way to trap information about who caused us
to deadlock?

If memory serves me, when Informix detects a deadlock situation, it send
an error to 1 process and allows the other process to go on its merry
way.

Thanks in advance,

 
 
 

1. Finding source of deadlock

Been swamped, not much extra time... This may be too late to help, but my
favorite tool when I suspect a deadlock condition, is to run my little
'deadlocks.sh' program...:

SleepFor=$1
while [ 1 = 1 ]
do
    echo " set isolation to dirty read;
    select c.username[1,8] as user,a.owner,
        a.waiter, b.username[1,8] as waiting,
        a.tabname, a.rowidlk as rowid, a.type
    from sysmaster:syslocks a,sysmaster:syssessions b,
        sysmaster:syssessions c
    where a.waiter = b.sid
    and   b.is_wlock > 0
    and   a.owner  = c.sid " | dbaccess sysmaster 2>/dev/null | sed '/^$/d'
    sleep $SleepFor
done

./deadlocks.sh 5

CTRL/C to interupt it.  Pass the sleep value length when you call the prog.

2. Instance Manager: SYS Login

3. Lost Connection

4. Suggestion for determining the source of a deadlock

5. Access sql Tsql

6. SOURCE for 2 PHASE locking and deadlock detection wanted

7. Avg function in MDX

8. How can I find deadlock?

9. Where to find deadlock trace information?

10. How to find Deadlocking SPIDs

11. Finding CXPACKET deadlock occurs in SQL 7.0

12. Finding Deadlocks cause