PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Stather <statherm at home dot com>
Subject: Re: sybase deadlocks
Date: Jul 11 2001 10:21PM

Arne,
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
period).
Let me know if you have further questions.
Regards,

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'
> )
> go
>
> CREATE PROCEDURE nextval @name varchar(25) AS
>  begin
>  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
>  end
> go
>
> 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,
> right?
>
> 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
> $dbh->err();