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
"ThunderMusic" <No*************************@NoSpAm.comwrote in message
news:ew**************@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