ActiveRecord, MySQL, and nested transactions -- what's the behavior?

Question

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?

Answer 1

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.

Answer 2

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).