deadlock on a single Select not in a transaction

[ThunderMusic]

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

 

[Kevin Spencer]

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

--
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

 

[Russell Fields]

You might enjoy reading the three articles starting at:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-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

 

[Goran Sliskovic]

Kevin Spencer wrote:
....

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