InnoDB - roll back all transactions for connection

Is it possible to rollback all statements that have been exe开发者_如何转开发cuted in the same connection thread? Instead of ROLLBACK just reverting the last executed statement.

---

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.

---

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

---

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.