SQL Update causing deadlock

Question

We recently moved some code from C# to SQL (SQL Server 2005) to try and prevent concurrency issues. However in SQL we are now getting deadlocks. I'm not able to recreate the steps to get the deadlock, however I was able to capture it in SQL trace.

There are no triggers on the table, but there are a couple of indexes to support searching.

According to the trace, the deadlock is being caused by two people running the same update statement, on the same record:

UPDATE myTable
SET
  col2 = @var2 + col2
  ,col3 = CASE WHEN (@Var2 <= 0 OR @Var2 + Col2 <= 0)
            THEN Col3
          ELSE
            CONVERT
            (
               dbo.MoneyInfo,
               @var3 + ':' + @Var4 + ':' + @Var5
            )
          END
OUTPUT INSERTED.Col0,
       Inserted.Col2,
       Inserted.Col3
WHERE Col0 = @Var1

dbo.MoneyInfo is a custom CLR type. The table looks something like:

create table myTable
(
    col0 int,
    col1 int,
    col2 decimal(18,2),
    col3 dbo.MoneyInfo
)

Col0 is non-clustered primary key (PK_Stock), col1 is clustered index (IX_Item)

This is the deadlock graph from the trace:

I don't understand how 2 people running the exact same stored proc statement can end up in a deadlock on the same statement. Shouldn't the first connection lock the record, forcing the 2nd one to wait for it to become available? Is there anything else I can look into for the cause of this deadlock? Could it be because of the OUTPUT statement?

Answer 1

Yes there is a possibility that 2 people execute the same statement without locking out the first execution. I have also generated the same scenario. A SQL Dirty Read Problem. The solution to the same was provided by using ISOLATION LEVEL READ COMMITTED transactions. It specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in non-repeatable reads or phantom data. For more information refer Microsoft knowledge base [http://msdn.microsoft.com/en-us/library/ms173763.aspx]

Summary: - Just put your query in Transaction [prefer - ISOLATION LEVEL READ COMMITTED transactions]