Deadlock situtation (deadlock victim) - help needed!!

Deadlock situtation (deadlock victim) - help needed!!

Post by Bryan Althau » Thu, 23 Oct 1997 04:00:00



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

 
 
 

Deadlock situtation (deadlock victim) - help needed!!

Post by luc van der veurs » Fri, 24 Oct 1997 04:00:00


: 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 :


 
 
 

Deadlock situtation (deadlock victim) - help needed!!

Post by Teresa Lars » Sat, 25 Oct 1997 04:00:00


: 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>

 
 
 

Deadlock situtation (deadlock victim) - help needed!!

Post by Bryan Althau » Tue, 28 Oct 1997 04:00:00



: : 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

 
 
 

1. Deadlock Victim

I keep getting two errors from SQL Server:

One is the dreaded deadlock error:
ODBC Error Code = 40001 (Serialization failure)<P> [Microsoft][ODBC SQL
Server Driver][SQL Server]Transaction (Process ID 65) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.

The second one is related to full text indexing:

[Microsoft][ODBC SQL Server Driver][SQL Server]Your search generated too
many results. Please perform a more specific search.

If anyone can tell me how to avoid these two errors or what they are most
commonly caused by, I'd be very appreciative.  We are running SQL Server 2k
with full text indexing on our products table.  The sql server is on
failover on two machines with central storage on a disk array.  The web
application platform is Coldfusion 5.0.

Please email me if possible.

Thanks,
Craig

2. 'bcp' doesn't accept NULL into columns..

3. Deadlock Victim ODBC Error

4. SQL join

5. I am a deadlock victim!!!!

6. Slow To Connect

7. Deadlock Victim Problem

8. decompiling

9. Choosing deadlock victim

10. Deadlock Victim/Performance

11. Deadlock Victim

12. Deadlock Victim Error

13. deadlock victim