Locking tables in Entity Framework

Question

I have an SQL Database with a table that I would like to lock. I'm using Entity Framework. Basically, there are a number of processes that each want to write to the database concurrently. Each of them wants to update a row in some table. However, I want only one of them to be able to do this at the same time.

Is there a way of locking an entire table, such as to prevent anyone from putting new rows or updating rows?

Thanks, Christian

That's the default behaviour. You shouldn't have to do anything, aside from using transactions, if you are changing several tables in one operation — Tony Hopkinson, Sep 17 '12 at 13:18

score 2 · Accepted Answer · edited May 23 '17 at 12:00

It's not clear to me why you would want this, but if you really want to lock the whole table you could:

Turn off row and page locking so that everything escalates to a table lock

Example adapted from here:

ALTER INDEX [MyIndexName] ON [dbo].[MyTableName] SET ( ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)

Note: this assumes your application solely "owns" these tables -- wouldn't want to apply this and break some other app

Set your queries to use Serializable isolation level

Example adapted from here:

TransactionOptions topt = new TransactionOptions();   
topt.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
using (var tran = new TransactionScope(TransactionScopeOption.Required, topt)) {
   //do stuff
}

score 0 · Answer 2 · edited May 23 '17 at 12:00

From the short description, it is doubtful that a table lock is really what is needed to solve this issue. Some common solutions that are much more scalable include:

1) Create a serializable transaction that reads or updates the record of interest as the first statement. All updates should try to follow this pattern.

2) Create a read_committed transaction like (1) but include a re-read/retry ability on an concurrency exception.

It is rare that one should ever need to lock a table. One case it may be necessary when there is some calculation involved for a manually determined identity field value and a chance of collision with another session.

For just that call, a transaction can be created and the table locked at the beginning. The lock can be "X" (exclusive) to prevent reads and writes or non-exclusive to just prevent writes. This answer uses an SP:

SO #3662766

As an alternative, an application lock could also be used.

sp_getapplock

Locking tables in Entity Framework

2 Answers2

Linked