deadlock on a single Select not in a transaction

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ThunderMusic - 27 Apr 2007 20:03 GMT

Hi,
We have a web application developped in asp.net (I think it's not relevant,
but well, it's so you know)... Yesterday, we received the following message
"Transaction (Process ID 69) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction. "
The thing is, the query was a simple select with inner joins between 3
tables (like select fields from table1 inner join table2... inner join
table3....). This command is not in a transaction, so the deadlock seems
impossible. And moreover, the deadlock occurs on the DataReader.Read() not
on the Command.ExecuteReader(...).

Can someone explain why the deadlock can have occured and what could be the
cause and solution to it? Can it be a bug in SQL Server or in the .net
framework (I really doubt about it)?

thanks

ThunderMusic

Reply to this Message

Kevin Spencer - 27 Apr 2007 20:44 GMT

What you're seeing is not concerning a Transaction as you are imagining it.
In essence, every operation performed by SQL Server is a transaction.
However, a Transaction (capital T) is a grouping of transactions (or
operations) into a single atomic unit which either fails or succeeds as a
whole. In this case, the reference is to to a (small t) transaction, which
is the operation of your query.

The exception occurs when 2 transactions (or processes) are trying to access
the same database object (such as a row in a table) at the same time. Each
process tries to get a lock on the object, and only one can. The other is
therefore killed. Here's a good article about this, and how to deal with it.
Notice that the most common tactic is simply to try again:

http://www.sql-server-performance.com/deadlocks.asp

Signature

HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

> Hi,
> We have a web application developped in asp.net (I think it's not
[quoted text clipped - 14 lines]
>
> ThunderMusic

Reply to this Message

Goran Sliskovic - 27 Apr 2007 23:08 GMT

...

> The exception occurs when 2 transactions (or processes) are trying to access
> the same database object (such as a row in a table) at the same time. Each
> process tries to get a lock on the object, and only one can. The other is
> therefore killed. Here's a good article about this, and how to deal with it.
> Notice that the most common tactic is simply to try again:

...

It's bit simplified, what you describe is not a deadlock situation. One
transaction will happily wait for lock to be released. Typical deadlock
situation is:
Transaction1 holds lock on A
Transcation2 holds lock on B
Transaction1 wants lock on B
Transcation2 wants lock on A

This cannot be resolved by waiting, so one transaction has to be killed.
Just a clarification.

Regards,
Goran

Reply to this Message

Russell Fields - 27 Apr 2007 20:52 GMT

You might enjoy reading the three articles starting at:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Pa
rt-1.aspx

The bottom line is that a select is a single statement transaction that can
hold locks, and a conflicting desire for locks is what causes deadlocks.
(The Deadly Embrace, where each process is holding something the other
process wants.)

RLF

> Hi,
> We have a web application developped in asp.net (I think it's not
[quoted text clipped - 14 lines]
>
> ThunderMusic

Reply to this Message

Rate this thread:

deadlock on a single Select not in a transaction

Free Magazines