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 Peppler <mpeppler at peppler dot org>
Subject: Re: sybase deadlocks
Date: Jul 11 2001 9:48PM

Arne Claassen writes:
 > 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

The problem is that you are doing the select first, which acquires a
shared lock, and then the update, which requires an exclusive lock. In
addition, you probably don't have row-level locking on, so two calls
to nextval for different @name values can deadlock.

Here's a slightly different version that I think will work better:

create proc nextval
( @name varchar(25) )
as 
declare @bob int

begin tran
update serials
   set lastnum = lastnum + 1
 where name = @name
-- add error checking here :-)
select @bob = lastnum
  from serials
 where name = @name

commit tran

select lastnum = @bob

go

(I'd avoid using return to return the value - that should be reserved
for returned status results - i.e. failure/success)

For a complete discussion on various surrogate key techniques, check
out http://my.sybase.com/detail?id=860

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

Default for DBD::Sybase is for autocommit to be on, so calling
$dbh->commit has no effect.

 > Also, the sybase error numer in their error message, is that returned as
 > $dbh->err();

Yes.

Michael
-- 
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
mpeppler@peppler.org - mpeppler@mbay.net
International Sybase User Group - http://www.isug.com