Why does this transaction not work in ActiveRecord?

Question

I am currently playing around with transactions and can not wrap my mind around the following scenario:

Given there is user with username "johnny" and full name "John Smith".

I start two rails consoles and perform the following commands in this order:

Console A:

ActiveRecord::Base.transaction { user = User.find_by_username("foo"); sleep 10; user.update_attribute(:full_name, "#{user.full_name}-1"); }

Console B:

ActiveRecord::Base.transaction { user = User.find_by_username("foo"); sleep 10; user.update_attribute(:full_name, "#{user.full_name}-2"); }

So the timing is the following:

A reads "John Smith"

B reads "John Smith"

A writes "John Smith-1"

B writes "John Smith-2"

According to my database class transaction B should fail to write "John Smith-2" because the data changed since it read it. So the transaction should be rollbacked and transaction A should win. I expect the username to be "John Smith-1", but the result is "John Smith-2".

Any ideas why this happens or how to get the expected behaviour?

Kind regards

Nils

Answer 1

As far as I understand transaction is not about locking, the main purpose of transaction is to ensure atomic changes. For example when you deduct money from your checking account and deposit them into your savings account you need to be sure either both INSERTs succeeded or both failed or you will be left with inconsistent state. What you need is locking, e.g. http://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html.

UPDATE: ACID is not meant to rollback transaction as I understand it. If there are no errors both transaction will succeed. What you get as a result does depend on isolation. If SERIALIZABLE level was used, you would get "John Smith-1-2", but InnoDB by default is using REPEATABLE READ level http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read , which means that if SELECT is non-locking (User.find_by...) it will not lock record for reading and you get original result from snapshot which was created at the time transaction A started (i.e. SELECT from B will not lock until A has finished as in case of SERIALIZABLE).

UPDATE: Meanwhile you can check http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html for pessimistic locking.