Entity Framework Dead Lock Victim on Read Only Statements

Question

I have an OData service (WCF Data Service using Entity Framework).

All this service does is select data. (No writes EVER.)

When I run my OData queries, I am occasionally getting errors like this:

Transaction (Process ID 95) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Can a select statement be a dead lock victim? Or is Entity Framework trying to lock stuff it should not be locking?

If it is locking where it should not be, is there a way to tell Entity Framework to NEVER lock? (For this service it is always and for ever going to be read-only.)

Pebble

Possibly related question: https://mcmap.net/q/157182/-entity-framework-with-nolock – Sheelagh

Answer 1

Don't just use ReadUncommitted or NOLOCK. Those will a) return inconsistent results easily and b) cause spurious errors due to "data movement". Don't!

A much better idea would be to enable snapshot isolation using the following SQL:

ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DB] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [DB] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [DB] SET MULTI_USER

This will cause read transactions to not take any locks and not be blocked by existing locks. This will likely fix your issue.

Prying

does the locks only affect by read or say a 1k inserts or updates on single table every minute would be affected by this as well ? – Attic

Reads under a snapshot model don't block. Writes can block. But writes will not block reads. You could delete an entire table of 1 billion rows and readers would not notice and still see the old data. – Prying

What about EF database-first Contexts where the programmer has no ability to modify the target database? For example, I develop a read-only service against another vendor's DB. What would the preferred solution be in this case? – Hither

Ideally, you'd get them to enable this. If not, you can get decent results with the other isolation levels. Use TransactionScope to control the level. At this point you have no choice but to understand what locks are taken by the various levels. No hard and fast rules, alas. (For read transactions with SI enabled there indeed is a fast rule: Always use SI.) – Prying