Results 1 to 1 of 1
Jun 24, 2004, 05:35 #1
- Join Date
- Feb 2004
- 0 Post(s)
- 0 Thread(s)
Savepoints vs nested transactions
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 :
Now if transaction 2 is rolledback :
ROLLBACK TO 1