Is it possible to lock on a value of a column in SQL Server?

user_1671558 0
Viewed 792

I have a table that looks like that:

Id   GroupId
1      G1
2      G1
3      G2
4      G2
5      G2

It should at any time be possible to read all of the rows (committed only). When there will be an update I want to have a transaction that will lock on group id, i.e. there should at any given time be only one transaction that attempts to update per GroupId.

It should ideally be still possible to read all committed rows (i.e. other transaction/ordinary reads that will not try to acquire the "update per group lock" should be still able to read).

The reason I want to do this is that an update can not rely on "outdated" data. I.e. I do make some calculations in a transaction and another transaction cannot edit row with id 1 or add a new row with the same GroupId after these rows were read by the first transaction (even though the first transaction would never modify the row itself it will be dependent on it's value).

Another "nice to have" requirement is that sometimes I would need the same requirement "cross group", i.e. the update transaction would have to lock 2 groups at the same time. (This is not a dynamic number of groups, but rather just 2)

concurrency locking transactions sql-server sql
1 Answers

Here are some ideas. I don't think any of them are perfect - I think you will need to give yourself a set of use-cases and try them. Some of the situations I tried after applying locks

  • SELECTs with the WHERE filter as another group
  • SELECTs with the WHERE filter as the locked group
  • UPDATES on the table with the WHERE clause as another group
  • UPDATEs on the table where ID (not GrpID!) was not locked
  • UPDATEs on the table where the row was locked (e.g., IDs 1 and 2)
  • INSERTs into the table with that GrpId

I have the funny feeling that none of these will be 100%, but the most likely answer is the second one (setting the transaction isolation level). It will probably lock more than desired, but will give you the isolation you need.

Also one thing to remember: if you lock many rows (e.g., there are thousands of rows with the GrpId you want) then SQL Server can escalate the lock to be a full-table lock. (I believe the tipping point is 5000 locks, but not sure).

Old-school hackjob

At the start of your transaction, update all the relevant rows somehow e.g.,

BEGIN TRAN

UPDATE YourTable 
  SET GrpId = GrpId
  WHERE GrpId = N'G1';

-- Do other stuff

COMMIT TRAN;

Nothing else can use them because (bravo!) they are a write within a transaction.

Convenient - set isolation level

See https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#isolation-levels-in-the-

Before your transaction, set the isolation level high e.g., SERIALIZABLE.

You may want to read all the relevant rows at the start of your transaction (e.g., SELECT Grp FROM YourTable WHERE Grp = N'Grp1') to lock them from being updated.

Flexible but requires a lot of coding

Use resource locking with sp_getapplock and sp_releaseapplock.

These are used to lock resources, not tables or rows.

What is a resource? Well, anything you want it to be. In this case, I'd suggest 'Grp1', 'Grp2' etc. It doesn't actually lock rows. Instead, you ask (via sp_getapplock, or APPLOCK_TEST) whether you can get the resource lock. If so, continue. If not, then stop.

Anything code referring to these tables needs to be reviewed and potentially modified to ask if it's allowed to run or not. If something doesn't ask for permission and just does it, there's no actual real locks stopping it (except via any transactions you've explicity specified).

You also need to ensure that errors are handled appropriately (e.g., still releasing the app_lock) and that processes that are blocked are re-tried.

user_142674250
Related