Posted by dmarkle on 02/04/06 03:01

Joe-

Here's my attempt at an explanation.

1) Session 1 acquires a write lock on, say, a row in the table
2) Session 2 acquires a write lock on, say, a row in the table
3) Session 1 attempts to read the entire (committed) data in the table,
but is forced to
wait until Session 2 is done with its transaction because Session 2 has
a write lock on some portion of the table.
4) Session 2 attempts to read the entire (committed) data in the table,
but is forced to wait until Session 1 is done with its transaction
because Session 1 has a write lock on some portion of the table. BOOM,
you're dead in the water.

Joe Weinstein wrote:
> Hi.
>
> create table joe(c1 integer not null, c2 integer not null)
>
> Two sessions:
>
> Session 1:
> BEGIN TRAN
> insert into joe (c1,c2) values (1,2)
>
>
> Session 2:
> BEGIN TRAN
> insert into joe (c1,c2) values (3,4)
>
> Session 1:
> select * from joe
>
> Session 2:
> select * from joe
>
> One of the sessions gets a deadlock victim message.
> thanks,
> Joe

[Back to original message]