0

How to apply an exclusive lock on a table while an insert is being made? In my case two different threads are inserting values into same table and they cannot see other transaction's data being inserted.

I want that while one transaction is inserting the rows, other transaction waits till first transaction has finished inserting the values.

Piece of SQL Trigger:-

BEGIN
    insert into A(SETID,ACCTID,UPDATEDTM)
    select @setid, l.acctid, getdate() 
    from AccountTable l where --(conditions for where clause)

I want to lock table A for insert operations being done.

Serg
  • 22,285
  • 5
  • 21
  • 48
Sharda Prasad Jaiswal
  • 349
  • 1
  • 5
  • 14

1 Answers1

3

Give TABLOCKX a go this will give you exclusive locks on the data;

BEGIN
INSERT INTO A WITH (TABLOCKX) (SETID,ACCTID,UPDATEDTM) 
SELECT @setid, l.acctid, getdate() 
FROM AccountTable l WHERE (conditions for where clause)

Further reading;

https://msdn.microsoft.com/en-GB/library/ms187373.aspx

TABLOCK vs TABLOCKX

Community
  • 1
  • 1
Rich Benner
  • 7,873
  • 9
  • 33
  • 39