Lock a SQL server Table for a given time and check if it is locked

Question

I want to lock a table for a given amount of time in SQL Server . I am using C# at code level. How can I achieve it, I also need to verify that the table is locked or not. I do not have much knowledge of locking tables in SQL Server. I want to achieve it using entity framework. Any help is much appreciated.

http://stackoverflow.com/questions/25273157/t-sql-lock-a-table-manually-for-some-minutes — Vincent, Oct 18 '16 at 03:47
It cannot be done with pure entity-framework. You'll **have** to use dao. — Erik Philips, Oct 18 '16 at 04:40

Sampath · Accepted Answer · 2016-10-18T07:09:24.447

2

You can try as shown below.

using (var context = new YourContext())
    {
     using (var dbContextTransaction = context.Database.BeginTransaction())
     {
       //Lock the table during this transaction
       context.Database.ExecuteSqlCommand("SELECT 1 FROM YourTable WITH (TABLOCKX)
    WAITFOR DELAY '00:03:00'");

            //your work here

            dbContextTransaction.Commit();
      }
}

Note : The table lock will be released after it exits the BeginTransaction block

edited Oct 18 '16 at 07:09

answered Oct 18 '16 at 04:35

Sampath

63,341
64
307
441

1

a small edit. I should be `dbContextTransaction.Commit()` instead of `scope.Commit()` and Table in SQL command to be replaced by actual Table name – Madhur Maurya Oct 18 '16 at 07:03

Lock a SQL server Table for a given time and check if it is locked

1 Answers1