Rails + MySQL + transactions + Lock, how can I prevent a transaction from opening from unlocking a table?

Question

In my Rails code, I need to confirm that an action is only allowed if more than one specific entry is left. For this reason, I need to block updates and then perform a read. My rails code is as follows:

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 blocks lines correctly. HOWEVER, if another request crosses the same code path that opens the transaction, removes the lock that I took out in another process, thereby defeating the lock target.

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

Answer 1

I assume that another request will be handled by another process, or at least with a different connection (with MySQL) (sorry, I don't know anything about Ruby-on-rails).

The lock obtained by this transaction cannot be released by another transaction. This is the purpose of the castle. How the manual sets:

UNLOCK TABLES explicitly publishes any table locks held by the current session

If my assumption is correct, there is nothing to worry about. Otherwise, if two requests can use the same connection at the same time, there is something really dangerous in this architecture ...

Answer 2

Perhaps in this case you should use the mutex semaphore ( http://www.ruby-doc.org/core-2.0/Mutex.html ) to avoid concurrent access to this shared resource (which, in this case, is your PaymentProfile processor ) By doing so, you guarantee that two parallel processes will not simultaneously access the synchronized code block.

Hope this helps