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.