History of deadlock
Poster asked whether Informix keeps a history of deadlocks so he could find the SQL that caused one days earlier. Answers: no historical record exists; you can only enable a trap in advance (onmode -I 143) and analyse the resulting AF file (disabling shared-memory dumps), or, if the session is still connected, check sysmaster:syssesprof for deadlks > 0. For live cases, Marcus Haarmann gave a procedure using onstat -u (L flag), onstat -k, onstat -g sql <sesid>, plus a sysmaster query joining syslocks/syssessions/syssqlcurses to show locked tables and both waiter's and owner's SQL. The poster was satisfied and planned to test it.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Is there any way to see history of deadlock? I need to see what sql made a deadlock in my db few day ago.
No...
The only way to track a deadlock is active a trap : onmode -I 143
wait for next deadlock and analyse the AF generated
(don't forget to configure your instance to *not* dump your shared
memory)
On terça-feira, 29 de maio de 2012 06:53:30, NOS PIOTR wrote:
> Hi,
> Is there any way to see history of deadlock?
> I need to see what sql made a deadlock in my db few day ago.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
If the session is still connected then its sysmaster:syssesprof record will show the deadlock so you could search for records in that table with deadlks > 0. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 29, 2012 at 5:53 AM, <> wrote: > Hi, > Is there any way to see history of deadlock? > I need to see what sql made a deadlock in my db few day ago. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340a6f70ed3504c12ad4d6
Ok, thank you for your help. I will try to think about that earlier.
Hi, can you help me one more time with deadlock?
Can you tell me howto find specific sql command that made deadlock?
I know command onstat -g ppf but i cant find the way howto change hex addr to
more readable text.
Hi,
to find out which session holds the lock, you have to go through the following
steps:
onstat -u tells you which session is waiting (L flagged).
onstat -k | grep address (address from first col in onstat -u) will tell youwhich lock is it waiting for
onstat -u | grep owner (owner from second col in onstat -k output) will tellyou which session is holding the lock.
Then you can take a look with onstat -g sql sesid what the owner of the lock
is currently doing.
A sysmaster query can tell you which tables are locked:
select dbsname ,tabname ,rowidlk ,type ,owner ,waiter sid_waiter ,b.hostname ,
(select trim (scs_sqlstatement::lvarchar (10000)) from syssqlcurses where
scs_sessionid = waiter),
a.sid sid_owner ,a.hostname ,
(select trim (scs_sqlstatement::lvarchar (10000)) from syssqlcurses where
scs_sessionid = a.sid)
from syslocks, syssessions a, syssessions b
where owner = a.sid
and waiter = b.sid
Hope this helps.
Marcus
----- Ursprüngliche Mail -----
Von: "NOS PIOTR"
An: ids@iiug.org
Gesendet: Mittwoch, 6. Juni 2012 10:23:08
Betreff: Re: History of deadlock [27298]
Hi, can you help me one more time with deadlock?
Can you tell me howto find specific sql command that made deadlock?
I know command onstat -g ppf but i cant find the way howto change hex addr to
more readable text.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Ok, thank you for a realy fast answer. I think this is it. No i will just try to make a deadlock to test it.