I am trying to implement a simple mutex lock in SQL. My tentative solution is the following:
query = 'UPDATE my_table SET lock_field = 1 WHERE lock_field = 0 AND mutex_id = 17'
if (query.execute() == 1){
// We own the lock, continue processing
// Finally: 'UPDATE my_table SET lock_field = 0 WHERE mutex_id = 17'
} else {
// Somebody else owns the lock, backoff and wait or throw exception etc
}
My question is: is this code safe? Does it guarantee that only a single 'client' can own the lock?
Edited in response to comments to clarify use as a mutex rather than normal DB processing
Such an approach is safe as it guarantees that no two clients can acquire the "lock" at the the same time. The problem is that this is not sufficient in practice, because this can't guarantee that at least one client will ever be able to acquire the lock; the database will stop doing useful work at some point. Notice that the database doing no work at all upholds the guarantee mentioned above.
The reason is that there is no way to guarantee that the lock will ever get unlocked. Consider the sequence of events
1. `SET lock_field = 1`
2. `query.execute() == 1` is true
3. OutOfMemoryException
4. Thread raised exception within exception, process aborted, destructor did not run
5. The row is now locked forever
There is no way to tell if a client that locked the row has gone away, aborted, had its network connection reset, is caught in an infinite loop, or simply didn't unlock due to a bug. Strictly speaking, the guarantee you mentioned is upheld, but no useful work can be performed.
The only way out of this would be to guess that the client is gone. Hopefully, the client is actually gone. But then we can't be sure if the data protected by the lock is in a consistent state.
Long story short: No, such schemes do not work in practice. Don't try to make them work; they don't. The database itself is fully equipped to guarantee transaction isolation while simultaneously providing a guarantee of forward progress.