CODEWITHSUNDEEP
sql-serverlocking
IvanH
IvanH

Reputation: 5159

Is TABLOCK, HOLDLOCK enough to prevent INSERT?

I have a transaction including several tables. I need to prevent inserts to TABLE_TO_BE_LOCKED after I have deleted some records from EDITED_TABLE. Currently I am using TABLOCKX

DELETE EDITED_TABLE FROM EDITED_TABLE
left join TABLE_TO_BE_LOCKED  with (TABLOCKX) on ....
 WHERE ...

I need to prevent inserting new records to TABLE_TO_BE_LOCKED but I would like to preserve possibility to read it. After the DELETE TABLE_TO_BE_LOCKED is still not changed and this is the reason for explicit locking.

Can I use TABLOCK, HOLDLOCK instead of TABLOCKX?

Note:

I know about How to lock a table for inserting in sql?. However there is result don't do it to prevent duplicities in primary key.

Upvotes: 3

Views: 20175

Answers (1)

gbn
gbn

Reputation: 432521

If the table has writes to in a transaction, then exclusive locks will be held until the end of the transaction. You can't enable SELECT in this case unless you use NOLOCK on the SELECT, but then you'll see deleted rows.

TABLOCK, HOLDLOCK will simply locks the whole table exclusively until the end of the transaction

It sounds like you're trying to fix your chosen solution: with more information we may be able to offer a better one that does what you want without lock hints. For example, if your transaction needs a consistent view of deleted data not affected by other transactions doing INSERTS then you could try one or more of

  • OUTPUT clause to work on the set of data you started with
  • Use SERIALIZABLE only to preserve the range (which is HOLDLOCK BTW)
  • Use SNAPSHOT isolation modes

After comments...

BEGIN TRAN

SELECT *
INTO #temp
FROM TABLE_TO_BE_LOCKED
WHERE .. -- or JOIN to EDITED_TABLE

DELETE EDITED_TABLE FROM EDITED_TABLE
left join #temp  with (TABLOCKX) on ....
 WHERE ...

-- do stuff with #temp

COMMIT

Upvotes: 5

Related Questions