SEO

#1 (**permalink**) 02-29-2008, 06:17 PM

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

#2 (**permalink**) 02-29-2008, 06:17 PM

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

#3 (**permalink**) 02-29-2008, 06:18 PM

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

PS: This doesn't occur with Sybase, which is fairly closely
related...
Joe

#4 (**permalink**) 02-29-2008, 06:18 PM

Joe Weinstein (joeNOSPAM@bea.com) writes:
>> 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
>
> PS: This doesn't occur with Sybase, which is fairly closely
> related...

Or was. I don't know what Sybase is doing these days. But neither would
you get a deadlock on SQL 2005 if you run with snapshot isolation.

And when I think of it, neither would this deadlock on SQL 6.5, as
Session 2 would block already on the INSERT, as it would be on a locked
page...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#5 (**permalink**) 02-29-2008, 06:18 PM

Erland Sommarskog wrote:

> Joe Weinstein (joeNOSPAM@bea.com) writes:
>
>>>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
>>
>>PS: This doesn't occur with Sybase, which is fairly closely
>>related...
>
>
> Or was. I don't know what Sybase is doing these days. But neither would
> you get a deadlock on SQL 2005 if you run with snapshot isolation.

Ok. Thanks. However, by default, with SQL2005, why is this a deadlock?

>
> And when I think of it, neither would this deadlock on SQL 6.5, as
> Session 2 would block already on the INSERT, as it would be on a locked
> page...
>
>

#6 (**permalink**) 02-29-2008, 06:18 PM

if you want to do that
you need to ensure you obtain all the locks required for the whole
transaction in the first statement
for example:

Session 1:
BEGIN TRAN
insert into joe with (tablockx) (c1,c2) values (1,2)

Session 2:
BEGIN TRAN
insert into joe with (tablockx) (c1,c2) values (3,4)

Session 1:
select * from joe

Session 2:
select * from joe

deadlocks only occur when developers don't understand locking
and they can render a production system virtually unusable ...

#7 (**permalink**) 02-29-2008, 06:18 PM

John Rivers wrote:

> if you want to do that
> you need to ensure you obtain all the locks required for the whole
> transaction in the first statement

That is often impossible, eg. a tx that updates two
tables. The actual requirement is that all potentially
concurrent transactions should lock needed objects in
the same order, else deadlocking is a risk.

> for example:
>
> Session 1:
> BEGIN TRAN
> insert into joe with (tablockx) (c1,c2) values (1,2)
>
> Session 2:
> BEGIN TRAN
> insert into joe with (tablockx) (c1,c2) values (3,4)
>
> Session 1:
> select * from joe
>
> Session 2:
> select * from joe
>
> deadlocks only occur when developers don't understand locking
> and they can render a production system virtually unusable ...

Amen to that, but what I'm looking for is a description of
what two locks my original two transactions got in reverse
order.
thanks
Joe

#8 (**permalink**) 02-29-2008, 06:18 PM

Joe Weinstein (joeNOSPAM@bea.com) writes:
> Ok. Thanks. However, by default, with SQL2005, why is this a deadlock?

I didn't touch that part, as it was already explained in other post, but
in case you missed it.

First both processes inserts a row into the table, and maintain a lock
as the INSERTs are not committed. Next, they try to read the entire table.
But do that, session 1 needs to access the row inserted by session 2
and vice versa.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#9 (**permalink**) 02-29-2008, 06:18 PM

Erland Sommarskog wrote:

> Joe Weinstein (joeNOSPAM@bea.com) writes:
>
>>Ok. Thanks. However, by default, with SQL2005, why is this a deadlock?
>
>
> I didn't touch that part, as it was already explained in other post, but
> in case you missed it.
>
> First both processes inserts a row into the table, and maintain a lock
> as the INSERTs are not committed. Next, they try to read the entire table.
> But do that, session 1 needs to access the row inserted by session 2
> and vice versa.

Gotcha. And if it were 'snapshot' isolation, each read would only see
their own row, I suppose, and continue.
thanks
Joe

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

can you tell me why this causes a deadlock?