Rails + MySQL + Transactions + Locking, how do I prevent opening a transaction from unlocking the table?

Question

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.

score 2 · Answer 1 · answered Jun 11 '13 at 08:50

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

score 0 · Answer 2 · answered Jun 12 '13 at 16:58

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

Hope it helps

Rails + MySQL + Transactions + Locking, how do I prevent opening a transaction from unlocking the table?

2 Answers2