deadlock on a single Select not in a transaction

27 Apr 2007 7:03 PM

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

27 Apr 2007 7:44 PM

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

Show quote

"ThunderMusic" <NoSpAmdanlatathotmaildotcom@NoSpAm.com> wrote in message
news:ewkig6PiHHA.2028@TK2MSFTNGP03.phx.gbl...
> 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
>

27 Apr 2007 10:08 PM

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

27 Apr 2007 7:52 PM

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

"ThunderMusic" <NoSpAmdanlatathotmaildotcom@NoSpAm.com> wrote in message
news:ewkig6PiHHA.2028@TK2MSFTNGP03.phx.gbl...
> 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
>