Coder Perfect

In mysql, what’s the difference between SET autocommit=1 and START TRANSACTION? (Have I forgotten something?)


I’m reading up on MySQL transactions and I’m not sure if I understand something, so here goes. I understand what a transaction is supposed to achieve, but I’m not sure whether I grasped the statement semantics.

So, my question is, is there anything wrong with the following (and, if so, what is wrong):

MySQL’s autocommit feature is enabled by default.

SET autocommit=0; will start a transaction, while SET autocommit=1; will commit it implicitly. It is possible to both COMMIT and ROLLBACK, in which case autocommit is still set to 0 afterward (and a new transaction is implicitly started).

START TRANSACTION; effectively sets autocommit=0; until a COMMIT; or ROLLBACK; occurs.

In other words, START TRANSACTION; and SET autocommit=0; are interchangeable, with the exception that START TRANSACTION; implicitly adds a SET autocommit=0; following COMMIT; or ROLLBACK;.

If that’s the case, serializable makes no sense to me. – Given that having an isolation level indicates that there is a transaction, shouldn’t autocommit be disabled by default?

And, if there is a difference between starting a transaction and setting autocommit (apart from the one explained above), what is it?

Asked by ralokt

Solution #1

Knowing how your database handles transactions (autocommit, explicit and implicit) can prevent you from having to restore data from a backup.

Data manipulation statements are controlled by transactions, which ensure that they are atomic. The term “atomic” refers to a transaction that either occurs or does not. Only a COMMIT or ROLLBACK statement (per ANSI-92, which tragically did not provide syntax for creating/beginning a transaction, therefore it is vendor specific) can communicate the transaction’s completion to the database. The changes (if any) made within the transaction are applied via COMMIT. ROLLBACK ignores any activities that occurred during the transaction, which is useful when a UPDATE/DELETE command causes an unexpected result.

Individual DML (Insert, Update, Delete) statements are often executed in an autocommit transaction, which means they are committed as soon as the statement completes correctly. In circumstances like yours, this implies there’s no way to restore the database to its previous state before the statement was run. When something goes wrong, the only way to get your data back is to restore it from a backup (providing one exists). InnoDB autocommit is enabled by default in MySQL because MyISAM does not support transactions. It can be turned off by typing:

SET autocommit = 0

When statement(s) are contained within an expressly specified transaction code block – for MySQL, that’s START TRANSACTION – and an explicitly made COMMIT or ROLLBACK statement at the end of the transaction, it’s called an explicit transaction. This article does not include nested transactions.

Implicit transactions differ from explicit transactions in a few ways. Implicit transactions do not necessitate a transaction’s explicit definition. However, like explicit transactions they require a COMMIT or ROLLBACK statement to be supplied.

Explicit transactions are the best option since they require a statement to complete the transaction, such as COMMIT or ROLLBACK, and what is happening is explicitly specified for others to read if necessary. When dealing with the database interactively, implicit transactions are fine, but COMMIT commands should only be used after the results have been thoroughly checked and determined to be valid.

As a result, you should use:

SET autocommit = 0;

  UPDATE ...;

…and only when the results are correct, use COMMIT.

UPDATE and DELETE commands, on the other hand, usually only return the total number of rows affected, not individual data. Before attempting the UPDATE/DELETE operation, convert such statements into SELECT statements and verify the results to confirm accuracy.

DDL (Data Definition Language) statements are committed automatically and do not require the use of a COMMIT statement. Creating or altering tables, indexes, stored procedures, databases, and views, for example.

Answered by OMG Ponies

Solution #2

Instead of SET AUTOCOMMIT = 0;, InnoDB has START TRANSACTION;, which is the officialy suggested way to do transactions in this engine (don’t use SET AUTOCOMMIT = 0; for transactions in InnoDB unless it’s for optimizing read-only transactions). COMMIT is a verb that means “to commit.”

In InnoDB, you might want to use SET AUTOCOMMIT = 0; for testing purposes rather than for transactions.

START TRANSACTION; is not available in MyISAM. For transactions, use SET AUTOCOMMIT = 0; in this engine. SET AUTOCOMMIT = 1; COMMIT; SET AUTOCOMMIT = 1; COMMIT; COMMIT; COMMIT; COMMIT; COMMIT (Difference explained in MyISAM example commentary below). You can do transactions in InnoDB in the same way.


Typical general-purpose transactions include:

/* InnoDB */

INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');

COMMIT; /* SET AUTOCOMMIT = 1 might not set AUTOCOMMIT to its previous state */

/* MyISAM */

INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');

SET AUTOCOMMIT = 1; /* COMMIT statement instead would not restore AUTOCOMMIT to 1 */

Answered by mikl

Solution #3

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. If you need to write to table t1 and read from table t2, for example, you can do so as follows:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...

Answered by Marco Aurelio Curado

Solution #4

If you want to utilize rollback, use start transaction instead, and ignore the rest.

By default, MySQL automatically commits the changes to the database.

To prevent MySQL from automatically committing these changes, run the command:

SET autocommit = 0;
SET autocommit = OFF

To specifically enable the autocommit mode, type:

SET autocommit = 1;
SET autocommit = ON;

Answered by RINSON KE

Post is based on