CODEWITHSUNDEEP
sqlsql-servert-sql
Axel Fontaine
Axel Fontaine

Reputation: 35169

How to lock and unlock a table exclusively outside of a transaction

In SQL Server, how can a table be locked and unlocked (exclusively) outside of a transaction?

Reason: multiple instances of my app need to interact with one table without stepping on each other's toes, while at the same time executing a statement that cannot run within a transaction

Upvotes: 1

Views: 1463

Answers (1)

Chris Mack
Chris Mack

Reputation: 5208

One option might be to look into sp_getapplock and sp_releaseapplock.

This would not lock the table, but an arbitrary named resource. You could then configure your application to only work with the table in question after the lock has been acquired, e.g., through stored procedures.

An example of this would be something like:

EXEC sp_getapplock @Resource = 'ResourceName', @LockMode = 'Exclusive', @LockOwner = 'Session'

-- UPDATE table, etc.

EXEC sp_releaseapplock @Resource = 'ResourceName', @LockOwner = 'Session'

Specifying @LockOwner = 'Session' means you can use this locking mechanism outside of a transaction.

There's also the option of extracting the locking and releasing statements into their own stored procedures, so the logic is only stated once; these stored procedures could return a value to the calling procedure with a result specifying whether or not the lock has been acquired/released.

At that point it's just a question of ensuring that this mechanism is put into place for every procedure/table/etc. where there may be contention.

Upvotes: 3

Related Questions