Deadlock within select?

[jw_guildford]

My SQL Server has kicked out a deadlocked process, which should only be
running a select statement, though there is another select on one of
the tables in the WHERE clause (see code below). Can anyone tell me
whether this is possible, or is it that my system, which is re-using
connections, is trying to complete an earlier statement? I've looked
through the system and think I'm committing all transactions.

The query I'm running (simplified, I don't use daft names like 'table1'
or 'date_col', honest :) is:

SELECT table1.*, table2.*, table3.*
FROM table1, table2, table3
WHERE table2.col1 = table1.col1 AND table3.col1 = table1.col2
AND table1.col3 = 'xyz'
AND (table1.date_col >= '2006-02-01' OR table1.date_col IN
(SELECT date_col FROM table1 WHERE (col4 = 'A' OR col4 = 'B') AND col3
= 'xyz'))
ORDER BY table1.date_col

Thanks

J

[Erland Sommarskog]

(jw**********@yahoo.co.uk) writes:

My SQL Server has kicked out a deadlocked process, which should only be
running a select statement, though there is another select on one of
the tables in the WHERE clause (see code below). Can anyone tell me
whether this is possible, or is it that my system, which is re-using
connections, is trying to complete an earlier statement? I've looked
through the system and think I'm committing all transactions.

Presumably, there was an insert/update/delete operation that your SELECT
clashed with.

Have you looked at the deadlock trace?

If you don't have deadlock trace enabled, open Enterprise Manager, and
edit the startup parameters to include "-T 1204 -T 3605", and restart
the server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jw_guildford]

Thanks Erland, I've added those parameters, I'm afraid I don't have a
way of reproducing it at the moment so I'll just have to wait until it
happens again.

J