In my Rails code I need to confirm that an action is allowed only if there is more than 1 of a certain record remaining. For this reason I need to lock updates and then perform a read. My rails code looks like this:
PaymentProfile.transaction do
profiles = PaymentProfile.lock("LOCK IN SHARE MODE").where(user_id: xxx)
if profiles.count > 1
#allow
else
#do not allow
end
end
In theory this works well and does lock the rows correctly. HOWEVER, if another request traverses the same code path opening the transaction removes the lock I took out in the other process, thus defeating the purpose of the lock.
From the MySQL docs:
Beginning a transaction also causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES WITH READ LOCK.
I will assume that another request will be treated by another process, or at least with another connection (to MySQL) (sorry I don't know anything about Ruby-on-rails).
A lock acquired by a given transaction cannot be released by another transaction. This is the very purpose of a lock. As the manual puts it:
UNLOCK TABLES explicitly releases any table locks held by the current session
If my assumption is correct, there is nothing to worry about. Otherwise, if two requests may use the same connection at the same time, there is something really fishy in this architecture...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With