Up Prev Next
From: Michael Stather <statherm at home dot com>
Subject: Re: sybase deadlocks
Date: Jul 11 2001 10:21PM
Deadlocks are also more likely to occur with the HOLDLOCK.
You might consider a cursor fetching the rows, then reissuing your update
statement, or of course wrap your begin tran and end tran around the update
statement as opposed to both the select and the update statement.
Check your indexes... You may be referencing multiple indexes or the same index
in revers order causing the deadlock (or an index plus a table scan).
Performing some things in serial help a lot, but your locking scheme can have a
significant impact on deadlocks in addition to I/O (i.e. 16K vs 2K in other
words Larger I/O decreases deadlocks in AllPages locked table, but is less
relevant on a DOL row level lock table) .
Usually the best way to address this type of issue is to resolve the coding
problems, then consider I/O or indexing, and finally your locking scheme. There
are other Sybase parms which can affect this as well (i.e. deadlock checking
Let me know if you have further questions.
J. Michael Stather
Arne Claassen wrote:
> Ok, can anyone point me at a good Sybase mailing list for Sybase specific
> issues, since i'm sure i shouldn't be spamming this list with them, if they
> are not sybase and perl related.
> Anyway, we're handling sequence numbers with a stored procedure like:
> CREATE TABLE serials (
> name varchar(25) PRIMARY KEY NOT NULL,
> lastnum int default '1'
> CREATE PROCEDURE nextval @name varchar(25) AS
> DECLARE @Bob int
> BEGIN TRANSACTION
> SELECT @Bob=(lastnum+1) FROM serials HOLDLOCK WHERE name=@name
> UPDATE serials SET lastnum=@Bob WHERE name=@name
> COMMIT TRANSACTION
> RETURN @Bob
> and of all the strange things, we keep getting deadlock errors. But we don't
> even have a single transaction that relies on data from a secondary source,
> so i don't see how a deadlock would occur. If two scripts do the same
> nextval call they should just queue and wait for the table, not deadlock,
> Obligatory Perl content. Is AutoCommit on by default in DBD::Sybase? Could i
> be getting deadlocks because my scripts don't commit after each $dbh->do()?
> Also, the sybase error numer in their error message, is that returned as