5
$\begingroup$

I'm applying the Compare-and-Swap technique to a SQL database to create custom row-level locking in my dataset, allowing for safe READ UNCOMMITTED isolation at the database level.

The Resource table includes a LockOwner GUID and a IsLocked BIT field. To acquire a lock, a dirty-read query gets the ID, LockOwner, and LockStatus. If Unlocked, attempt to UPDATE the Resource by (ID, LockOwner) with a newly generated LockOwner and LockStatus of Locked. Abort and start again if no rows are updated - meaning someone else got there first. Otherwise, the Lock is held in the READ UNCOMMITTED transaction. The transaction is needed to allow rollback on client failure/abandon, but the dirty reads avoid locks.

This seems to me to work great for resources that are independent of each other. But what must I add to account for a new kind of lock, ResourceGroup?

ResourceGroup to Resource is a one-to-many relationship. Resources can be locked individually, but if the ResourceGroup needs to be locked, then all of the Resources must also be locked.

Locking a ResourceGroup is a far less frequent need than locking a Resource, so the scheme should be optimized for Resource queries, avoiding requiring joins to ResourceGroup if possible.

I am imagining a scenario where locking a ResourceGroup involves marking the member rows with some flag, but I'm not sure what scheme doesn't interfere with the original Resource-only scheme. Part of the problem comes from the UPDATE of a Resource while it is locked (and therefore already UPDATED in another transaction). I believe that even if the fields are different within the record, the UPDATE will place an UPDATE LOCK on the row, so any lock on ResourceGroup would introduce blocking that we are trying to avoid. Even if we could do this, how would the ResourceGroup lock acquisition mechanism know when all of the Resources (which may have had locks in process as we began locking their peers) have been released?

There may be differences in this locking granularity by RDBMS, I'm on MS SQL 2005+.

Improve this question
$\endgroup$

1 Answer 1

Reset to default
3
$\begingroup$

The Prevent race conditions across multiple rows on Stackoverflow seems to be relevant.

Also, I would urge you to use SQL server locks instead of your own lock column (which it sounds like you're doing).

And it looks like doing hierarchical locks is kind of clumsy in SQL, so if you're finding this to be a huge bottleneck you could try a locking service like zookeeper.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Yes. I do not at all recall asking this question but I now would confidently say that application locks such as those available in SQL Server through sp_getapplock are the way to go. Always take a SHARED lock on "RG_resourceGroupxyz" and then any lock on "R_resource789" to modify the Resource, or just an EXCLUSIVE lock on "RG_resourceGroupxyz" to modify the Resource Group. $\endgroup$
    – Jason Kleban
    Jun 16, 2015 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.