Home > Archive > ASE Database forum > December 2005 > monDeadLock









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author monDeadLock
Luciano

2005-11-16, 11:24 am

Hi,
Im trying to solve some deadlock problems in my
applications. I want to programatically log this situation
getting usefull data without the need of read errorlog, im
want to use something like that (i put this on the attach):

select *
into #dead
from master..monDeadLock

select object_name(d
.HeldProcedureID, d.HeldProcDBID) as
HeldProcedure,
d.ObjectName,
d.HeldCommand,
d.HeldLockType,
d.WaitLockType,
d.ResolveTime,
d.DeadlockID,
d.VictimKPID,
d.ObjectDBID,
d.PageNumber,
d.RowNumber,
d.HeldFamilyID,
d.HeldSPID,
d.HeldKPID,
d.HeldProcDBID,
d.HeldProcedureID,
d.HeldBatchID,
d.HeldContextID,
d.HeldLineNumber,
d.WaitFamilyID,
d.WaitSPID,
d.WaitKPID,
d.WaitTime,
d.HeldUserName,
d.HeldApplName,
d.HeldTranName,
d.WaitUserName
from #dead d

I get:

HeldProcedure ObjectName
HeldCommand HeldLockType
WaitLockType ResolveTime
DeadlockID VictimKPID ObjectDBID PageNumber RowNumber
HeldFamilyID HeldSPID HeldKPID HeldProcDBID
HeldProcedureID HeldBatchID HeldContextID HeldLineNumber
WaitFamilyID WaitSPID WaitKPID WaitTime
HeldUserName HeldApplName
HeldTranName



WaitUserName
------------- ----------
----------- ------------
------------ -----------
----------- ----------- ----------- ----------- -----------
------------ ----------- ----------- ------------
--------------- ----------- ------------- --------------
------------ ----------- ----------- -----------
------------ ------------
------------



------------
marcar_movimientos movimientos_no_aplic
ados
UPDATE exclusive page update
page Nov 16 2005 11:55AM 2255
1789592618 6 8955195 0 1366
1366 1789592618 6 1330416109
39164 1 0 34 34
1874462096 5986 swm_robertop SIA
$upd



NataliaSC

And in the errorlog i get:

Deadlock Id 2255: detected. 1 deadlock chain(s) involved.

Deadlock Id 2255: Process (Familyid 0, Spid 1366, Suid 1557)
was executing a UPDATE command in the procedure
'marcar_movimientos'
.
SQL Text: o.marcar_movimientos
Deadlock Id 2255: Process (Familyid 0, Spid 34, Suid 860)
was executing a UPDATE command at line 1.
SQL Text: update dbo. movimientos_no_aplic
ados SET
nro_proceso =null WHERE nro_proceso =168155 AND borrado
IS NULL
Deadlock Id 2255: Process (Familyid 0, Spid 34) was waiting
for a 'update page' lock on page 8955195 of the
'movimientos_no_apli
cados' table in database 6 but process
(Familyid 0, Spid 1366) already held a 'exclusive page' lock
on it.
Deadlock Id 2255: Process (Familyid 0, Spid 1366) was
waiting for a 'exclusive page' lock on page 5838676 of the
'movimientos_no_apli
cados' table in database 6 but process
(Familyid 0, Spid 34) already held a 'exclusive page' lock
on it.

Deadlock Id 2255: Process (Familyid 0, Spid 1366) was chosen
as the victim. End of deadlock information.


How can i get, from montables, for victim of the deadlock,
the sp name or SQL text??

Im using:
Adaptive Server Enterprise/12.5.1/EBF 11658
ESD#2/P/Sun_svr4/OS 5.8/ase1251/1838/64-bit/FBO/Fri Feb 20
12:35:43 2004


Thanks a lot!
Luciano

Rob Verschoor

2005-11-17, 8:25 pm

In principle you should be able to relate this information to monSysSQLText.
Problem 1: unless you've sampled everything in monSysSQLText, you may not
have the actual SQL text anymore by the time you look at monDeadlock.
Problem 2: I believe there is a bug which sometimes has the effect that you
cannot find the corresponding monSYsSQLText records even you if would have
sampled everything. Not sure about the status of this issue....

BTW -- 2255 dealocks is a lot....

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

<Luciano> wrote in message news:437b5f2d.4bd4.1681692777@sybase.com...
Hi,
Im trying to solve some deadlock problems in my
applications. I want to programatically log this situation
getting usefull data without the need of read errorlog, im
want to use something like that (i put this on the attach):

select *
into #dead
from master..monDeadLock

select object_name(d.HeldProcedureID, d.HeldProcDBID) as
HeldProcedure,
d.ObjectName,
d.HeldCommand,
d.HeldLockType,
d.WaitLockType,
d.ResolveTime,
d.DeadlockID,
d.VictimKPID,
d.ObjectDBID,
d.PageNumber,
d.RowNumber,
d.HeldFamilyID,
d.HeldSPID,
d.HeldKPID,
d.HeldProcDBID,
d.HeldProcedureID,
d.HeldBatchID,
d.HeldContextID,
d.HeldLineNumber,
d.WaitFamilyID,
d.WaitSPID,
d.WaitKPID,
d.WaitTime,
d.HeldUserName,
d.HeldApplName,
d.HeldTranName,
d.WaitUserName
from #dead d

I get:

HeldProcedure ObjectName
HeldCommand HeldLockType
WaitLockType ResolveTime
DeadlockID VictimKPID ObjectDBID PageNumber RowNumber
HeldFamilyID HeldSPID HeldKPID HeldProcDBID
HeldProcedureID HeldBatchID HeldContextID HeldLineNumber
WaitFamilyID WaitSPID WaitKPID WaitTime
HeldUserName HeldApplName
HeldTranName



WaitUserName
------------- ----------
----------- ------------
------------ -----------
----------- ----------- ----------- ----------- -----------
------------ ----------- ----------- ------------
--------------- ----------- ------------- --------------
------------ ----------- ----------- -----------
------------ ------------
------------



------------
marcar_movimientos movimientos_no_aplic
ados
UPDATE exclusive page update
page Nov 16 2005 11:55AM 2255
1789592618 6 8955195 0 1366
1366 1789592618 6 1330416109
39164 1 0 34 34
1874462096 5986 swm_robertop SIA
$upd



NataliaSC

And in the errorlog i get:

Deadlock Id 2255: detected. 1 deadlock chain(s) involved.

Deadlock Id 2255: Process (Familyid 0, Spid 1366, Suid 1557)
was executing a UPDATE command in the procedure
'marcar_movimientos'
.
SQL Text: o.marcar_movimientos
Deadlock Id 2255: Process (Familyid 0, Spid 34, Suid 860)
was executing a UPDATE command at line 1.
SQL Text: update dbo. movimientos_no_aplic
ados SET
nro_proceso =null WHERE nro_proceso =168155 AND borrado
IS NULL
Deadlock Id 2255: Process (Familyid 0, Spid 34) was waiting
for a 'update page' lock on page 8955195 of the
'movimientos_no_apli
cados' table in database 6 but process
(Familyid 0, Spid 1366) already held a 'exclusive page' lock
on it.
Deadlock Id 2255: Process (Familyid 0, Spid 1366) was
waiting for a 'exclusive page' lock on page 5838676 of the
'movimientos_no_apli
cados' table in database 6 but process
(Familyid 0, Spid 34) already held a 'exclusive page' lock
on it.

Deadlock Id 2255: Process (Familyid 0, Spid 1366) was chosen
as the victim. End of deadlock information.


How can i get, from montables, for victim of the deadlock,
the sp name or SQL text??

Im using:
Adaptive Server Enterprise/12.5.1/EBF 11658
ESD#2/P/Sun_svr4/OS 5.8/ase1251/1838/64-bit/FBO/Fri Feb 20
12:35:43 2004


Thanks a lot!
Luciano


Ollie

2005-12-01, 1:24 pm

> In principle you should be able to relate this information
> to monSysSQLText. Problem 1: unless you've sampled
> everything in monSysSQLText, you may not have the actual
> SQL text anymore by the time you look at monDeadlock.
> Problem 2: I believe there is a bug which sometimes has
> the effect that you cannot find the corresponding
> monSYsSQLText records even you if would have sampled
> everything. Not sure about the status of this issue....
>
> BTW -- 2255 dealocks is a lot....
>
> HTH,
>
> Rob
> ----------------------------------------------------------
> --- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 12.5/12.0/11.5/11.0 and Replication Server 12.5 /
> TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase Replication Server Quick
> Reference Guide" "The Complete Sybase ASE Quick Reference
> Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The
> Netherlands
> ----------------------------------------------------------
> ---
>
> <Luciano> wrote in message
> news:437b5f2d.4bd4.1681692777@sybase.com... Hi,
> Im trying to solve some deadlock problems in my
> applications. I want to programatically log this situation
> getting usefull data without the need of read errorlog, im
> want to use something like that (i put this on the
> attach):
>
> select *
> into #dead
> from master..monDeadLock
>
> select object_name(d.HeldProcedureID, d.HeldProcDBID) as
> HeldProcedure,
> d.ObjectName,
> d.HeldCommand,
> d.HeldLockType,
> d.WaitLockType,
> d.ResolveTime,
> d.DeadlockID,
> d.VictimKPID,
> d.ObjectDBID,
> d.PageNumber,
> d.RowNumber,
> d.HeldFamilyID,
> d.HeldSPID,
> d.HeldKPID,
> d.HeldProcDBID,
> d.HeldProcedureID,
> d.HeldBatchID,
> d.HeldContextID,
> d.HeldLineNumber,
> d.WaitFamilyID,
> d.WaitSPID,
> d.WaitKPID,
> d.WaitTime,
> d.HeldUserName,
> d.HeldApplName,
> d.HeldTranName,
> d.WaitUserName
> from #dead d
>
> I get:
>
> HeldProcedure ObjectName
> HeldCommand HeldLockType
> WaitLockType ResolveTime
> DeadlockID VictimKPID ObjectDBID PageNumber RowNumber
> HeldFamilyID HeldSPID HeldKPID HeldProcDBID
> HeldProcedureID HeldBatchID HeldContextID HeldLineNumber
> WaitFamilyID WaitSPID WaitKPID WaitTime
> HeldUserName HeldApplName
> HeldTranName
>
>
>
> WaitUserName
> ------------- ----------
> ----------- ------------
> ------------ -----------
> ----------- ----------- ----------- -----------
> ----------- ------------ ----------- -----------
> ------------ --------------- ----------- -------------
> -------------- ------------ ----------- -----------
> ----------- ------------ ------------
> ------------
>
>
>
> ------------
> marcar_movimientos movimientos_no_aplic
ados
> UPDATE exclusive page
> update page Nov 16 2005 11:55AM
> 2255 1789592618 6 8955195 0
> 1366
> 1366 1789592618 6 1330416109
> 39164 1 0 34
> 34 1874462096 5986 swm_robertop
> SIA
> $upd
>
>
>
> NataliaSC
>
> And in the errorlog i get:
>
> Deadlock Id 2255: detected. 1 deadlock chain(s) involved.
>
> Deadlock Id 2255: Process (Familyid 0, Spid 1366, Suid
> 1557) was executing a UPDATE command in the procedure
> 'marcar_movimientos'
.
> SQL Text: o. marcar_movimientos=0
2
> Deadlock Id 2255: Process (Familyid 0, Spid 34, Suid 860)
> was executing a UPDATE command at line 1.
> SQL Text: update dbo. movimientos_no_aplic
ados SET
> nro_proceso =3dnull WHERE nro_proceso =3d168155 AND
borrado
> IS NULL
> Deadlock Id 2255: Process (Familyid 0, Spid 34) was
> waiting for a 'update page' lock on page 8955195 of the
> 'movimientos_no_apli
cados' table in database 6 but process
> (Familyid 0, Spid 1366) already held a 'exclusive page'
> lock on it.
> Deadlock Id 2255: Process (Familyid 0, Spid 1366) was
> waiting for a 'exclusive page' lock on page 5838676 of the
> 'movimientos_no_apli
cados' table in database 6 but process
> (Familyid 0, Spid 34) already held a 'exclusive page' lock
> on it.
>
> Deadlock Id 2255: Process (Familyid 0, Spid 1366) was
> chosen as the victim. End of deadlock information.
>
>
> How can i get, from montables, for victim of the deadlock,
> the sp name or SQL text??
>
> Im using:
> Adaptive Server Enterprise/12.5.1/EBF 11658
> ESD#2/P/Sun_svr4/OS 5.8/ase1251/1838/64-bit/FBO/Fri Feb 20
> 12:35:43 2004
>
>
> Thanks a lot!
> Luciano
>
>

Have you looked into using monErrorLog instead of
monDeadLock or maybe the combination of both?
Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2008 droptable.com