Do INSERTs get auto-committed?

Our application fires an INSERT query to the MySQL Database to add records. I want to know whether or not the records get auto-committed. If I run the ROLLBACK command, when does the database perform a rollback ? Is a ROLLBACK possible after a COMMIT ?

The answer to your question depends on whether-or-not you're within a transaction that will span over more than one statement. (You've tagged the question with InnoDB, the answer would be different with MyISAM.)

From the reference manual: http://dev.mysql.com/doc/refman/5.1/en/commit.html


By default, MySQL runs with autocommit mode enabled. This means that
as soon as you execute a statement that updates (modifies) a table,
MySQL stores the update on disk to make it permanent.

So yes, by default, if you're just using INSERT, the records you insert will be committed, and there is no point trying to roll them back. (This is effectively the same as wrapping each statement between BEGIN and COMMIT.)

However, if you're dealing with transactions explicitly, you will have to use COMMIT to commit store the records, but you will also be able to use ROLLBACK.

You can start a transaction explicitly by using START TRANSACTION (or BEGIN). This is independent of the autocommit setting (on by default):


With START TRANSACTION, autocommit remains disabled until you end the
transaction with COMMIT or ROLLBACK. The autocommit mode then reverts
to its previous state.

Alternatively, if autocommit=0, I think any statement following another end of transaction, will start a transaction (but you can still use START TRANSACTION explicitly); that's at least the way I interpret this:


The autocommit mode. If set to 1, all changes to a table take effect
immediately. If set to 0, you must use COMMIT to accept a transaction
or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1,
MySQL performs an automatic COMMIT of any open transaction. Another
way to begin a transaction is to use a START TRANSACTION or BEGIN
statement. See Section 12.3.1, “START TRANSACTION, COMMIT, and
ROLLBACK Syntax”.

More specifically "another way to begin a transaction" seems to imply that setting "autocommit=0" is enough to start a transaction (at least just before each statement at the start a session or that follows a COMMIT/ROLLBACK).
I would suggest to use BEGIN or START TRANSACTION explicitly anyway even if autocommit=0, as it can make it clearer to see when the transaction starts or ends.

(How you start a transaction may depend on the way your application uses MySQL.)

StackExchange Database Administrators Q#4252, answer score: 10