Would the following SQL Statements be the cause of a deadlock?

Question

Hi,

I'm looking for a way to explain a deadlocking issue. I think I know what is causing it, but I'm not sure of the exact events.

We have a long-running view (several seconds). We are updating one of the tables that is used in that view. The updates can also take several seconds. The update statements that are running when the deadlock error is thrown join to the view. For example:

UPDATE t1 SET
    Field1 = 'someValue'
FROM Table1 t1
JOIN TheView v ON v.TableId = t1.TableId
WHERE v.Condition = 'TheCondition'

The statement that appears to be getting shut down due to the deadlock is like:

SELECT * FROM TheView

Where the view is defined as:

CREATE VIEW TheView AS
    SELECT *
    FROM Table1 t1
    JOIN Table2 t2 ON t2.foo = t1.foo

I'm pretty sure that the deadlock is occurring because both the view and the update statement depend on Table1. Is this scenario possible?

Thanks

Answer 1

Have you tried using SQL Profiler? Profiler will tell you exactly what statements are involved in a deadlock and include the resources each process has locked that the other process needs etc.

Answer 2

Is it possible? Sure. You'll need to do some work to find out for sure. See: How to Track Down Deadlocks Using SQL Server 2005 Profiler

Answer 3

It is definitely possible. I posted several similar repro scripts here: Reproducing deadlocks involving only one table

One way around is to use snapshot isolation.