We're currently experiencing deadlocks with a production application.
With the volume of transactions that we get, this behavior is not
completely unexpected. What we would like to do is trap the deadlock,
wait, and re-submit. We are having difficulty trapping the deadlock
from within the application, so what I'm looking for is a way to
create a deadlock using stored procedures to be able to simulate what
happens in our production code.

I'm somewhat of a newbie to ASE (it's been 9 years since I last worked
with Sybase). I need two procs where I can create a window large
enough to create the right sequence of events in order to trigger the
deadlock.

Currently I have the following sequence of events:

1. Proc A requests select-lock on page
2. Proc B requests exclusive-lock on same page for insert
3. Proc A requests exclusive-lock on page for update of previously
selected row.

Is there a way to "pause" a stored proc long enough to start my second
process to create the deadlock scenario? Is there a function that I
can invoke which effectively makes a procedure "sleep" for a while? I
know that usually code is written to be fast and efficient, but maybe
there's a way to induce this behavior through what otherwise might be
considered bad/sloppy coding practices.

Your help is greatly appreciated!

Evan

Evan Smith wrote:

> We're currently experiencing deadlocks with a production application.
> With the volume of transactions that we get, this behavior is not
> completely unexpected. What we would like to do is trap the deadlock,
> wait, and re-submit. We are having difficulty trapping the deadlock
> from within the application, so what I'm looking for is a way to
> create a deadlock using stored procedures to be able to simulate what
> happens in our production code.
>
> I'm somewhat of a newbie to ASE (it's been 9 years since I last worked
> with Sybase). I need two procs where I can create a window large
> enough to create the right sequence of events in order to trigger the
> deadlock.
>
> Currently I have the following sequence of events:
>
> 1. Proc A requests select-lock on page
> 2. Proc B requests exclusive-lock on same page for insert
> 3. Proc A requests exclusive-lock on page for update of previously
> selected row.
>
> Is there a way to "pause" a stored proc long enough to start my second
> process to create the deadlock scenario? Is there a function that I
> can invoke which effectively makes a procedure "sleep" for a while? I
> know that usually code is written to be fast and efficient, but maybe
> there's a way to induce this behavior through what otherwise might be
> considered bad/sloppy coding practices.
>
> Your help is greatly appreciated!
>
> Evan

Sure. Look up 'waitfor' in the T-SQL docs.

Joe Weinstein at BEA

Evan Smith wrote:

> [. . .] so what I'm looking for is a way to
> create a deadlock using stored procedures to be able to simulate what
> happens in our production code.

Something like this...

create table deadlock_test_1 (a int)
go
create table deadlock_test_2 (a int)
go

create procedure sp_deadlock_1 as
begin transaction
lock table deadlock_test_1 in exclusive mode
waitfor delay '00:00:10'
lock table deadlock_test_2 in exclusive mode
commit transaction
return
go

create procedure sp_deadlock_2 as
begin transaction
lock table deadlock_test_2 in exclusive mode
lock table deadlock_test_1 in exclusive mode
commit transaction
return
go

Then in one session, run sp_deadlock_1. In another
session, run sp_deadlock_2.


Larry Coon
University of California

Hi Evan,

You can use the WAITFOR command to put a delay between the two updates.

-bret

Thanks, all! I searched the online docs for both "wait" and "sleep"
and didn't find any relevant matches. This should do the trick.

Cheers!

Evan