Very Computer

We have Sybase SQL Server 4.9.2 (HP-UX) where a deadlock situation is
occurring, but the only client output is that the process was chosen
as a deadlock victim (w/error number 1205).  Are there some trace
flags we can use (or other debugging steps) to determine what
processes were running, what table locks were in effect, or other
useful information at the time when the deadlock occurred?

Most of the DB activity is Read Only with limited writes at the end
of the program run. We have mulitple instances of this program
running in parallel and have no problems until now. We also have
Replication server running - but this shouldn't be a problem.

Any help will be greatly appreciated.

Regards,
Bryan Althaus

: We have Sybase SQL Server 4.9.2 (HP-UX) where a deadlock situation is
: occurring, but the only client output is that the process was chosen
: as a deadlock victim (w/error number 1205).  Are there some trace
: flags we can use (or other debugging steps) to determine what
: processes were running, what table locks were in effect, or other
: useful information at the time when the deadlock occurred?

traceflag: -T1205

You then get output like the following into your errorlog :

00:97/10/23 10:02:27.57 server  Deadlock Id 3 detected
Deadlock Id 3 detected. 1 deadlock chain(s) involved.

Process 33 was executing a UPDATE command in the procedure 'ordr_int_pat_P_U'.
Process 13 was executing a INSERT command in the procedure 'ordr_item_int_pat_I'.
Process 13 was involved in a multi-database transaction.
Process 13 was waiting for a 'exclusive page' lock on page 1304 of the 'ordr_item_int_pat' table in database 13 but process 33 already held a 'exclusive page' lock on it.
Process 33 was waiting for a 'exclusive page' lock on page 7763 of the 'ordr_int_pat' table in database 13 but process 13 already held a 'exclusive page' lock on it.

Process 33 was chosen as the victim. End of deadlock information.

Luc.

______________________________________________________________________
Luc Van der Veurst                          ISUG Enhancements Co-Chair
Academic Hospital, VUB                             http://www.isug.com
Laarbeeklaan 101, 1090 Brussels, Belgium
32 - 2 477 69 80                                Next ISUG Conference :

: We have Sybase SQL Server 4.9.2 (HP-UX) where a deadlock situation is
: occurring, but the only client output is that the process was chosen
: as a deadlock victim (w/error number 1205).  Are there some trace
: flags we can use (or other debugging steps) to determine what
: processes were running, what table locks were in effect, or other
: useful information at the time when the deadlock occurred?

There are some traceflags that will provide you with more information
regarding deadlocks (602, 603, 1204 and 1205).  Check out Q1.4 at the
Sybase FAQ http://reality.sgi.com/pablo/Sybase_FAQ/

As always, use of these traceflags is at our own risk as most of them
are not documented or supported by Sybase.  Enough folks have used 1204
that it's a pretty low risk.  :-)

                                Hope this helps,
                                Teresa Larson

     _________________________________________________________________
    /  Teresa A. Larson                 http://www2.ari.net/jmasino/ /
   /  Bell Atlantic                          Voice: (301) 282-0224  /
  /  13100 Columbia Pike, A-3-3              Fax:   (301) 282-9416 /

/________________________________________________________________/
                      #include <std_disclaimer>

: : We have Sybase SQL Server 4.9.2 (HP-UX) where a deadlock situation is
: : occurring, but the only client output is that the process was chosen
: : as a deadlock victim (w/error number 1205).  Are there some trace
: : flags we can use (or other debugging steps) to determine what
: : processes were running, what table locks were in effect, or other
: : useful information at the time when the deadlock occurred?

: There are some traceflags that will provide you with more information
: regarding deadlocks (602, 603, 1204 and 1205).  Check out Q1.4 at the
: Sybase FAQ http://reality.sgi.com/pablo/Sybase_FAQ/

Thanks to all the suggested using flag 1204 and how to set it. The
below logfile has narrowed the problem down for us.

We are experiencing a deadlock condition on the sysindexes table in
tempdb (Sybase 4.9.2), where it appears that simultaneously creating
indices on 2 separate #temp tables (2 diff spid's) causes this issue.  
Any easy way to avoid this, besides upgrading to Sybase XI?  errorlog
output as follows (from dbcc traceflag 1204):

---------------------------------------------------------------------
Deadlock Id 2 detected. 2 deadlock chain(s) involved.

Process 7 was executing a  command at line 1.
Process 6 was executing a CREATE INDEX command at line 1.
Process 6 was waiting for a 'shared page' lock on page 28 of the
'sysindexes' ta ble in database 2 but process 7 already held a
'exclusive page' lock on it.
Process 7 was waiting for a 'exclusive page' lock on page 25 of the
'sysindexes' table in database 2 but process 6 already held a 'shared
page' lock on it.

Process 7 was executing a  command at line 1.
Process 6 was executing a CREATE INDEX command at line 1.
Process 6 was waiting for a 'shared page' lock on page 28 of the
'sysindexes' ta ble in database 2 but process 7 already held a
'exclusive page' lock on it.
Process 7 was waiting for a 'exclusive page' lock on page 25 of the
'sysindexes' table in database 2 but process 6 already held a 'shared
page' lock on it.

Process 6 encountered multiple deadlocks.
Process 6 was chosen as the victim. End of deadlock information.
---------------------------------------------------------------------

Any insights/thoughts/comments/workarounds/fixes would be greatly appreciated!

Regards,
Bryan Althaus