Ira Gladnick (IraGladnick@yahoo.com) writes:
Presumably, the SELECT conflicts with an INSERT, DELETE or UPDATE
statement. Assume for instance that the SELECT access the table
through an NC-index and bookmark lookups. The SELECT statement will
then need to lock the index row while it's retrieving the data row.
At the same time, an UPDATE statement runs that updates a column in
the NC index. The UPDATE statment finds the row to update through some
other index, and updates the data row, and then goes on to update
the index. But the UPDATE is blocked by the SELECT. Which in its turn
is blocked when trying to retrieve the data page.
If you have trace flag 1204 enabled on your server, the deadlock
trace may confirm my theory.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at