deadlock on a single Select not in a transaction

  • Thread starter Thread starter ThunderMusic
  • Start date Start date
T

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
 
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
 
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:
Click to expand...
....

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