Rails 3. Nested transactions. Exception in a child

Question

Why doesn't ActiveRecord rollback changes in nested transactions after exception was risen in a child block?

Here are examples:

1.

>> Client.transaction do
?>   Client.create(:name => 'Pavel')
>>   Client.transaction do
?>     Client.create(:name => 'Elena')
>>     raise ActiveRecord::Rollback
>>   end
>> end
=> nil
>> Client.all.map(&:name)
=> ["Pavel", "Elena"] # instead of []

2.

>> Client.transaction do
?>   Client.create(:name => 'Pavel')
>>   Client.transaction(:requires_new => true) do
?>     Client.create(:name => 'Elena')
>>     raise ActiveRecord::Rollback
>>   end
>> end
=> nil
>> Client.all.map(&:name)
=> ["Pavel", "Elena"] # instead of ["Pavel"]

Thanks.

Debian GNU/Linux 5.0.6;

Ruby 1.9.2;

Ruby on Rails 3.0.1;

SQLite 3.7.3.

Answer 1

I'm having the same problem, and I can duplicate your result exactly. If I raise ActiveRecord::Rollback in the outer block, then the whole transaction rolls back, but otherwise, nothing gets rolled back.

Apparently, the current version of ActiveRecord does not know how to do nested transactions with SQLite3, even though ActiveRecord is supposed to implement nested transactions using savepoints, and SQLite has supported savepoints since 3.6.8.

As further evidence that this is simply not supported by ActiveRecord yet, try this...

> List.connection.supports_savepoints?
=> false

Ubuntu 11.04 - the Natty Narwhal;

ruby 1.8.7 (2010-04-19 patchlevel 253) [i486-linux], MBARI 0x8770, Ruby Enterprise Edition 2010.02;

Ruby on Rails 3.0.3;

sqlite3 gem 1.3.3

SQLite 3.7.2;

Answer 2

The rails transaction implementation does not make use of savepoints (or similar technologies) that are used by databases to support nested transactions. True nested transactions are not supoprted by the databases themselves.

example:

begin -- starts transaction 1
  begin -- start transaction 2

    insert into something (foo) values ('bar');

  commit -- ends transaction 1
rollback -- is ignored

The first commit or rollback always closes the out-most transaction.

There is a way how databases actually can do the nesting. this would use the before mentioned savepoints. example

begin -- starts transaction 1
  savepoint foo -- starts "transaction" 2

    insert into something (foo) values ('bar');

  release -- commit for transaction 2
rollback -- roll back the data of the savepoint and everything else within transaction 1

You can nest as many savepoints as you want within each other, as long as a transaction is open.

For rails itself there is a catch though: The functions create and similar wrap themselves within an transaction. so your first example produces the follwing sql

begin  -- transaction.do
  begin  -- Client.create
    insert into clients ( name ) values ('Pavel')  -- Client.create
  commit  -- Client.create, closes the out-most transaction
  begin -- transaction.do
    begin  -- Client.create
      insert into clients ( name ) values ('Elena')  -- Client.create
    commit  -- Client.create, closes the out-most transaction

So your Exception just arrives to late.

You can patch this issue, but you have to do it for every connection adapter.

PS: You might be confused by the -- within the sql. Those are single line comments in mysql..