4

Rails uses savepoints to achieve nested transactions with MySQL, and to my understanding, the semantics of this are identical to actual nested transactions, in terms of atomic data changes.

  1. Is this true?
  2. What about calling "save" at arbitrary times within the code? The transaction still stays open until the end of the block, right? Are there any differences in behavior when using nested transactions/savepoints?
  3. Anything else to be aware of?
  4. [intentionally inciting flame-war] Should I switch to PostgresSQL?
John Bachir
  • 22,495
  • 29
  • 154
  • 227

2 Answers2

4

  1. Yes this is true, the only DB with true nested transactions is MS SQL Server

  2. Yes, the transaction stays open even if you call save at arbitrary times, however, if a rollback exception is raised inside the nested transaction then it will not cause a global rollback of the outer transaction (see #3 for savepoint management as well).

  3. You can pass Model.transaction(:requires_new => true) to a create a sub-transaction, this is probably the behavior you are expecting as otherwise you won't have control over the nested transaction as it will not obey nested rollbacks. Also, people sometimes forget that model callbacks are all executed in 1 transaction so any transaction inside of a callback is a nested transaction.

  4. You aren't really inciting a flame-war, PostgresSQL doesn't have nested transactions either (it uses savepoints as well), they are both great databases.

Brandon
  • 2,574
  • 19
  • 17
  • could you elaborate a bit more on point 3? what do you mean by "have control over the nested transaction" – John Bachir Jun 10 '11 at 22:59
  • 1
    if you don't use "requires_new" then the rollback point is the beginning of the parent transaction, not the "sub-transaction", because of how savepoints work with AR. http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html – Brandon Jun 11 '11 at 21:38
  • I see. So how does behavior differ if (a)saving or (b)raising an exception, with or without "requires_new" in the nested transaction? – John Bachir Jun 13 '11 at 02:01
  • 1
    If you don't use "requires_new" then any exception raised (even an exception that is raised 10 sub-transactions deep) will cause the transaction to revert to the first transaction created. If you use "requires_new => true" then it will create a new savepoint which will then allow any exception to revert back to the point of the last true savepoint (or when you called "requires_new" on a sub-transaction) – Brandon Jun 19 '11 at 22:09
1

As far as I know, nested transactions for Mysql rely on the Savepoints feature in MySQL 5+. Looks like it should work correctly if you're Rails 2.3.2+ and Mysql 5+.

However, nested transactions can be very messy to manage. You may want to consider your design choices if you're relying on this to cleanup what you're doing and decompose the work-flow into something simpler (IMHO).

Winfield
  • 18,985
  • 3
  • 52
  • 65