Entity Framework Dead Lock Read-Only Message Victim

Question

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

All this service is data selection. (There is no EVER record.)

When I run my OData queries, I sometimes get these errors:

A transaction (process ID 95) was blocked when resources were locked by another process and was selected as a victim of a deadlock. Restart the transaction

Can a select statement fall prey to a deadlock? Or is the Entity Framework trying to block material that it should not block?

If it blocks where it should not be, is there a way to say that the Entity Framework is never blocked? (For this service, it will always and always be read-only.)

Answer 1

Do not use only ReadUncommitted or NOLOCK. They will a) easily return inconsistent results and b) cause false errors due to "data movement". Do not!

It would be best 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 accept any locks and not be blocked by existing locks. This will probably help solve the problem.

Answer 2

You did not specify which database is behind your OData service, so this may not be relevant. In SQL Server, you can bind to SELECT WITH nolock statements to prevent table locking. The presence of this tells me that SELECTS are definitely candidates for blocking problems.

Unfortunately, in EF there is no way to specify WITH nolock in your queries. The closest I could find was to wrap your connection in a transaction and set the isolation level for reading without changing (as indicated in the answer to the question I linked in the comments).

Here is a sample code from the corresponding question to show how you do it:

 //declare the transaction options var transactionOptions = new System.Transactions.TransactionOptions(); //set it to read uncommited transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted; //create the transaction scope, passing our options in using (var transactionScope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, transactionOptions)) { //declare our context using (var context = new MyEntityConnection()) { //any reads we do here will also read uncomitted data //... //... } //don't forget to complete the transaction scope transactionScope.Complete(); }