CODEWITHSUNDEEP
sql-serverlinqdeadlock
user826840
user826840

Reputation: 1273

SQL Server deadlock LINQ to SQL

I'm trying to find a definitive answer for this. If you select and then update, using Linq on SQL Server, in a serializable transaction, is there a deadlock risk? eg:

using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable, Timeout = new TimeSpan(0, 0, 10) }))
{
    using (var con = new MyContext())
    {
        var record = con.MyTable.Single(t => t.id == 1);    // share lock?

        record.field = 99;
        con.SubmitChanges();                                // upgraded to exclusive lock? possible deadlock

        trans.Complete();
    }
}

And since this is such a common pattern, is the only way to avoid it something like:

var record = con.ExecuteQuery<MyTable>("select * from MyTable with (updlock) where id = {0}", 1).Single();

Upvotes: 1

Views: 1136

Answers (1)

usr
usr

Reputation: 171226

Yes, this is a classic deadlock. Two transactions both read, then both attempt to update. Both must wait for the other to release the shared lock.

With an optimistic concurrency model this would result in a write conflict.

The UPDLOCK, HOLDLOCK, ROWLOCK hint combo is what I usually use in these cases. Note, that for convenience reasons you can execute this locking query using ExecuteQuery, discard the results and then use LINQ normally. You don't need to switch everything into SQL.

L2S and EF do not allow you to set table hints. This is unfortunate. You have to resort to SQL on some level. Either manually or using a function or a wrapper view.

Upvotes: 1

Related Questions