Locking a table with a select in Entity Framework

entity-framework transactionscope table-locking
Manuel Felício picture Manuel Felício · Oct 26, 2009 · Viewed 7.8k times · Source

I need to do something like this 

select * from myTable with (xlock,holdlock)

using Entity Framework. Is this possible? I've opened a TransactionScope with the Serializable isolation level but my selects are not locking the tables. I'd like them to lock until I complete the transaction scope.

Answer

Tom Peplow picture Tom Peplow · Jul 18, 2010

It is possible but you have to issue the SQL you can't add the locking hint when using LINQ (as far as I know):

ObjectContext.ExecuteStoreCommand(
                string.Format("select 1 from [{0}] with (tablockx, holdlock) where 0 = 1",
                              tableName));

If you do that in a transaction scope then you'll hold the lock until you complete the transaction.

A bit more information can be found here:

http://peplowdown.wordpress.com/2010/07/18/locking-across-servers-table-locks-with-entity-framework/

Related questions

Transaction scope timeout on 10 minutes

I have a long running TransactionScope in C#. I told the scope that it should have a long timespan, but still I get a timeout. What could cause this? TransactionOptions transactionOptions = new TransactionOptions(); transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted; transactionOptions.Timeout = TimeSpan.…

Read More
Why doesn't TransactionScope work with Entity Framework?

See the code below. If I initialize more than one entity context, then I get the following exception on the 2nd set of code only. If I comment out the second set it works. {"The underlying provider failed on Open."} …

Read More
Database.BeginTransaction vs Transactions.TransactionScope

What is the difference between System.Transactions.TransactionScope and EF6's Database.BeginTransaction? Could someone give a small example or just explain which one to use when with a clear difference? P.S: In my project, I'm using EF6. I've …

Read More