CODEWITHSUNDEEP
mysqlsqlitepostgresqltransactions
Zaiborg
Zaiborg

Reputation: 2522

Nested transaction rollback between two savepoints?

For the three SQL types (MySql, SQLite and PostgreSQL) I want / need to handle save points identically.

Now I have my application to change different entries in the database in one big transaction and need some nested transactions for special behavior of the program.

So the question is, if i create something like:

BEGIN TRANSACTION;
--random insert/update statements
SET SAVEPOINT sp1;
--more random inserts/updates
SET SAVEPOINT sp2;
--inserts n stuff

(yes the syntax may not be correct, its just an example)

So i want to know if it is possible to do a rollback between the two save points sp1 and sp2 without rolling back the inserts/updates after sp2?

Upvotes: 1

Views: 2826

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324355

Savepoints will not do what you want. When you roll back to a savepoint, everything after that savepoint is rolled back, irrespective of whether later savepoints were created.

Think of savepoints like a "stack". You can't pull something out of the middle of the stack, you have to remove everything down to the layer you want.

You are probably looking for autonomous transactions. None of the databases you want to use support them. In PostgreSQL you can work around this using the dblink module to make a new connection to the database and do work with it; see http://www.postgresql.org/docs/current/static/dblink.html . I don't know what solutions MySQL or SQLite offer, but Google will help now that you know the term you are looking for.

I recommend that you find a way to work around this application design requirement if possible. Have your application use two database connections and two transactions to do what you need, taking care of co-ordinating the two as required.

Upvotes: 5

Related Questions