I'm adapting my application to use the transaction feature of Innodb (I use a SQL abstraction layer over MySQL). The problem I have is how to handle cases when one transaction is started within another transaction as MySQL doesn't support nested transactions.

What I do now is only send the first BEGIN to MySQL and then silently ignore all other BEGIN (but count them) - then when transactions are finished ignore all COMMIT except the last one. This effectively gives me one big atomic transaction. But when a rollback comes, I have to rollback everything to keep stuff consistent.

Now Innodb supports savepoints - so I could simulate nested transactions by replacing all BEGIN within an already opened transaction by savepoints, and then if a ROLLBACK comes, just rollback to the last previously created savepoint.

So my question is - would this actually work, or is there something else about nested transactions that I missed ?


Some example on what I'm trying to do :

What the application does :
begin transaction 1
begin transaction 2
commit transaction 2
commit transaction 1

Would be translated by the database abstraction layer as :
BEGIN
SAVEPOINT 1
(nothing)
COMMIT

Now if transaction 2 is rolledback :
BEGIN
SAVEPOINT 1
ROLLBACK TO 1
COMMIT