Select-locking in Rails with MySQL

Question

Looking for a best-practice advice:

Let's suppose I have a Account object with limit attribute. Each day there can be n Payments, with sum of their amounts up to the account limit. When creating a new payment, it checks to see if it's amount + amounts of other payments of the day are still within the account limit, and either saves the record or displays error.

Now, let's assume I have account with limit 100$, and simultaneously two payments of 99$ are being created. Each would do a select, see that nothing is there, and proceed to save itself, resulting in 198$ total being saved.

What would you do about this? I was thinking about issuing a write lock on the payments table at the start of transaction, but that seems quite heavy-handed, seeing that I only really care about not allowing payments belonging to specific account not being read by other transactions. Are there any other options, better ways of handling this situation?

Answer 1

Use transactions:

http://en.wikipedia.org/wiki/Database_transaction

http://dev.mysql.com/doc/refman/5.0/en/commit.html

Answer 2

I was thinking about issuing a write lock on the payments table at the start of transaction, but that seems quit eheavy-handed

Assuming you're talking about transactions already, I'm assuming you're not using MyISAM, but instead InnoDB or some other engine that supports transactions already.

Locking for read or write on transactions to preserve atomicity of operations is not something that should be done manually. It's the job of your transaction's isolation level to do that. This will also preserve atomicity of operations between the opening of the transaction and the locking of your table or records, so that no reads slip through in the meanwhile.

For the use case you describe, what you want are transactions with the 'SERIALIZABLE' isolation level, which will lock for read and writes. Also, it'll automatically lock only the records' you've read from (even if you query for ranges), therefore leaving the remaining records free for manipulation.

Through the 'SERIALIZABLE' isolation level, the moment you read the information from the table, any attempts to update or read those records will have to wait until that transaction is finished. Also, make sure you read before you update on the same transaction so you're sure to be working with the correct value.

You can read more about isolation levels here: http://en.wikipedia.org/wiki/Isolation_(database_systems) http://www.databasejournal.com/features/mysql/article.php/3393161/MySQL-Transactions-Part-II---Transaction-Isolation-Levels.htm

You can learn how to setup the isolation level of your transactions here: http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html