By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,022 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,022 IT Pros & Developers. It's quick & easy.

can you tell me why this causes a deadlock?

P: n/a
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

Feb 4 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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


Feb 4 '06 #2

P: n/a


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

Feb 4 '06 #3

P: n/a
Joe Weinstein (jo*******@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, es****@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
Feb 4 '06 #4

P: n/a


Erland Sommarskog wrote:
Joe Weinstein (jo*******@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...


Feb 4 '06 #5

P: n/a
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 ...

Feb 4 '06 #6

P: n/a


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

Feb 4 '06 #7

P: n/a
Joe Weinstein (jo*******@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, es****@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
Feb 4 '06 #8

P: n/a


Erland Sommarskog wrote:
Joe Weinstein (jo*******@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

Feb 5 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.