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+.