sybperl-l Archive

From: "Lee Wenzler" <leew at roanoketimes dot com>
Subject: RE: forcing a deadlock
Date: May 17 2002 6:01PM

Whoops again, the missing transactions were self-inflicted, it was the
way I had the call to ExecSql constructed. This was the first time I'd
used the begin & commit transactions inside the ExecSql calls, and
instead of putting the entire sql into on call to ExecSql, I had the
begin tran & commit as separate calls.

After I put the deadlock causing code all into one call to ExecSql, it
worked perfectly! I've tested it with several thousand inserts which
suffered hundreds of deadlocks and never lost a transaction!


In case anyone is interested in seeing the ExecSql deadlock handling in
action, here's the snippet of deadlock producing code:

 
# the junk1 & junk2 tables just have an int
# and a varchar(32) field 


$dbh = new Sybase::Simple $user, $password, $server, $PROGRAM_NAME;

$dbh->config(DeadlockRetry => 1);

$dbh->ExecSql("use $database");

my $i = 0;
for ($i = 0; $i < 1000; $i++) {

    print LOG "$i  ";
	$dbh->ExecSql("begin tran  
		insert junk2 values ($i, 'from perl')
		insert junk1 values ($i, 'from perl')
		commit tran");           

}

---------------------------------------------
 
/* and here's the isql code to run at the same time the perl script is
running to force a bunch of deadlocks:   */


declare @i int
select @i = 0
while (@i < 50)
begin 
begin tran
insert junk1 values (@i, 'from isql') 
insert junk2 values (@i, 'from isql') 
waitfor delay "00:00:01"
commit tran
select @i = @i + 1
end

----------------------------------------------



Thanks,

Lee



-----Original Message-----
From: owner-SYBPERL-L@list.cren.net
[mailto:owner-SYBPERL-L@list.cren.net] On Behalf Of Michael Peppler
Sent: Friday, May 17, 2002 11:03 AM
To: SybPerl Discussion List
Subject: RE: forcing a deadlock

On Fri, 2002-05-17 at 07:53, Lee Wenzler wrote:
> Whoops ... I thought I had stress tested it, but I did actually have
> some dropped transactions. I'm not doing any sort of retry in my code,
> but relying on the Sybase::Simple module to retry the batch.
> 
> Is that not the correct way to use it? 
> 
> What happens when the deadlocks occur, is the 1205 message comes up
and
> the script continues on, but it always drops the transaction it was
on.
> I changed the script to do inserts on two different test tables within
> the begin and end transactions in botht the isql session and the perl
> script.

What calls do you use in the Sybase::Simple script?

IIRC DeadlockRetry works only for ExecSql().

Michael


> -----Original Message-----
> From: owner-SYBPERL-L@list.cren.net
> [mailto:owner-SYBPERL-L@list.cren.net] On Behalf Of Michael Peppler
> Sent: Friday, May 17, 2002 10:19 AM
> To: SybPerl Discussion List
> Subject: RE: forcing a deadlock
> 
> On Fri, 2002-05-17 at 07:05, Lee Wenzler wrote:
> > Thanks Abe, I tried your while loop suggestion and it gave me a ton
of
> > deadlocks. I'm using the Sybase::Simple module and wanted to see how
> > well the $dbh->config(DeadlockRetry => 1) worked. I let about a
> hundred
> > deadlocks occur and the perl script never missed a transaction.
> 
> Cool.
> 
> I knew the basic code was correct, but I hadn't actually stress tested
> it.
> 
> Thanks.
> 
> Michael
> -- 
> Michael Peppler                              Data Migrations, Inc.
> mpeppler@peppler.org           *or*          mpeppler@mbay.net
> http://www.mbay.net/~mpeppler
> International Sybase User Group: http://www.isug.com
> 
> 
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org           *or*          mpeppler@mbay.net
http://www.mbay.net/~mpeppler
International Sybase User Group: http://www.isug.com