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.)

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.