Locking when inserting in SQL Server Express 2014

Question

I have a table with a column named Time of type datetime2.

I need to run this entire query without anyone else reading or altering data in the table while its executing.

IF NOT EXISTS (SELECT Id FROM Bookings WHERE Time >= '2016-02-02 09:00:00' AND Time < '2016-02-02 11:00:00') BEGIN -- Insert some data END

The query is inside a transaction in an asp.NET application, but i am not sure if that is enough. Do i need to apply locking hints or set an isolation level? If so, how would I do that?

I've read about TABLOCK, and it seems to be doing what I want, but is it overkill? And where would I specify that a TABLOCK is wanted for the entire query and not just the select statement?

score 0 · Answer 1 · answered Apr 14 '16 at 08:40

So you do not want any other transaction to insert rows having Time value between '2016-02-02 09:00:00' and '2016-02-02 11:00:00'. You should not use TABLOCK as this will put shared lock on the whole table . Doing this will not allow inserts,update and delete from other transaction. You just want to block the time range. use table hint - Holdlock in your select statement -

    IF NOT EXISTS (SELECT Id FROM Bookings with (holdlock)
    WHERE Time >= '2016-02-02 09:00:00' AND Time < '2016-02-02 11:00:00') 
      BEGIN 
      -- Insert some data 
      END

Locking when inserting in SQL Server Express 2014

1 Answers1