InnoDB - roll back all transactions for connection

Question

Is it possible to rollback all statements that have been executed in the same connection thread? Instead of ROLLBACK just reverting the last executed statement.

Answer 1

you cannot rollback things that are not in a transaction to begin with - although with most dbs you can have autocommit mode which makes each statement a transaction.

so to get what you want, you need to start a transaction, do whatever processing you need, and then rollback to the start, or commit.

Answer 2

Depends on autocommit which is disabled by default for each connection. From the MySql Manual

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error. See Section 13.2.12, “InnoDB Error Handling”.

A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK statement.

autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

You can't rollback statements if autocommit is enabled as the commit is implicit.

Answer 3

Beside usual COMMIT and ROLLBACK statements, InnoDB supports savepoints. Savepoints do allow you to rollback only several last statements within the transaction.