Search code examples
sql-serverinserttable-lock

Adding table lock manually to specified table in SQL Server

I want to INSERT into one tables but prevent INSERTING to another one. It is possible to LOCK for example table a for INSERTING, INSERT to table b and then UNLOCK table a?

TABLOCK can lock only the table I am INSERTING in.

Thanks

Martin Pilch

Solution

  • SQL Server does not allow locking objects like you would do semaphors. Also, locking a table will not make it read-only; it will make it locked out for everybody.

    You can place a lock by using a table hint such as SELECT * FROM MyTable WITH (LOCKNAME) but that is not a good programming practice.