I'm looking for a way to avoid race conditions on complicated
transactions. Some transactions I do need to update multiple values in
multiple tables, and create new objects.
It looks like the safest way to do this is to setup a single process
that would just implement these transactions and have the rest of the
application call it for services.
Where can I find some information on how to set it up?
- Create the transaction process
- Communicate to it from the application processes (mongrel cluster)
- Guarantee that this process is up after reboot
Nick, that does seem to be more what I need to use. When I use a transaction, does that mean nothing else gets inserted in the middle to the database?
I’m changing account, balance, creating account lines and creating invoices. Each, goes to a different table in the (same) database. If I just wrap it with a transaction, will that imply that all the read and write actions within the transaction happen without any other writes to the database by other Rails instances?
Amir Helzer wrote the following on 26.07.2007 20:29 :
Nick, that does seem to be more what I need to use. When I use a transaction, does that mean nothing else gets inserted in the middle to the database?
I'm changing account, balance, creating account lines and creating invoices. Each, goes to a different table in the (same) database. If I just wrap it with a transaction, will that imply that all the read and write actions within the transaction happen without any other writes to the database by other Rails instances?
No. You must use serialized transactions for that, ActiveRecord doesn't use them by default. Even with them you only get transactions semantics: either all of it succeeds (no incompatible concurrent writes) or it rollbacks all changes (and you are responsible to do it all again in the new context).
- If you use serialized transactions you also have to be prepared to
deal with conflicts and redo transactions.
- You should and probably can find a way to design your model so that
you only need to serialize access to a single table at the most. I
really can't think of a well designed schema off the top of my head
that requires rows in different tables to all be inserted together in
isolation. SELECT FOR UPDATE is another nice tool, at least in
postgresql.
And you also need to know how your database works. Postgresql, and
Oracle also from what I've heard, use multi version concurrency
control as opposed to explicit locking, which can change how you
structure your queries and transactions depending on what database you
use.
Some more information on your schema/model might help us give you some
more pointers on the simplest way to accomplish what you are after.
- Different users have accounts. Each account has a balance.
- There's a table for transactions. A transaction has an amount,
from_account_id and to_account_id.
- A separate table holds account_lines. For each transactions, there's
an account line per account, showing the balance after the
transaction.
- A final table is for invoices. Invoice has_many transactions. When
an invoice is paid, all transactions are executed, balance updates and
account lines are created.
Some transactions are created by user requests and some, by the
payment gateway notifying of payment clearance.
There are some accounts that will hardly update concurrently (because
they belong to users), while other accounts (like system account that
hold fees) will update very frequently.
That's it, more or less.
As I understand it, a separate process for executing transactions
would guarantee completely that things are always coherent. Overall,
money movements in the system should be a small part of all DB queries
(maybe 1%).
If it's just the accounts table that has the balance column, then
using select for update might be enough. Really depends on the
details of how you are updating everything. Here is a good place to
start:
Oops. It's only like that in test mode. Development and production
show the transactions.
Is there anything I can set so that I can have it in test too?
Transactional fixtures sometimes interfere with transactions (they wrap each test in its own transaction, which can actually make your own transactions noops).
In your test classes you may want to try:
self.use_transactional_fixtures = false
You can put it in Test::Unit::TestCase in test/test_helper.rb if you want it for all your test classes.
Thanks Lionel, Nick, Roderick and everyone who helped here.
I've started with an idea to make it overly complicated and now, I
think I got it right, so let me summarize:
- I use transaction blocks to make sure that any failure happens on
both the withdrawals and deposits (so money doesn't vanish into thin
air).
- To make sure that concurrent operations from different processes
don't spoil data, I use Optimistic Locking (Agile web development with
Rails, page 389).
The block of DB operations is wrapped in begin-rescue. In the rescue
section, I reload all written-to objects. This is a MUST if you want
to retry the operation, because, otherwise the optimistic locking will
just keep failing (to refresh the lock_version from the updated
database).
The application retries failed operations.
This is how it all looks like in my implementation now:
while !ok && (attempts < MAX)
begin
Account.transaction
move_money()
setup_stuff()
ok = true
end
rescue
reload_accounts()
attempt += 1
end