CODEWITHSUNDEEP
sqlentity-frameworklocking
Christian
Christian

Reputation: 4375

Locking tables in Entity Framework

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

Upvotes: 7

Views: 10421

Answers (2)

crokusek
crokusek

Reputation: 5654

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

Upvotes: 0

Steve P
Steve P

Reputation: 19397

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
}

Upvotes: 2

Related Questions