sybperl-l Archive

From: "Arne Claassen" <arne at mp3 dot com>
Subject: sybase deadlocks
Date: Jul 11 2001 9:31PM

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();