Fun MySQL fact of the day: no nested transactions

Nested transactions allow a client to begin and end multiple transactions within an outer transaction (within an outer transaction (within an outer transaction (within an outer transaction ...))). When would you use it? This type of mechanism is necessary to accomplish a pure Component Based Architecture. However, excepting BerkleyDB, nearly no other databases support transaction nesting.

But there are options, like

• transaction interleaving, which simulates nesting by using multiple transactions in multiple connections, and
• transaction SAVEPOINTS.

Most databases, including MySQL (when using InnoDB), offer Transaction Savepoints, which allows for a linearized alternative to transaction nesting. To accomplish this, a "savepoint" is created with a SAVEPOINT my_savepoint call, allowing for partial rollbacks with ROLLBACK TO my_savepoint. This simulates, but does not replace, nested transactions.

Of course, MySQL/InnoDB is always full of surprises: when you ROLLBACK TO [savepoint], InnoDB will not release any locks it had taken inside the now-rolled-back changes. This makes about as much sense as it doesn't.