sybperl-l Archive

From: Deepak Shah <shahdeepakj at yahoo dot com>
Subject: Re: sybase deadlocks
Date: Jul 11 2001 10:24PM

Row level locking will also help.

alter table serials
lock datarows


Deepak
--- Michael Peppler  wrote:
> 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
> 


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/