|
|
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
|
