Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)

mysql 5.7 transaction
mysql autocommit best practices
mysql check autocommit
start transaction sql
mysql autocommit off
auto commit commands in sql
mysql begin end
mysql atomic transaction

I am reading up on transactions in MySQL and am not sure whether I have grasped something specific correctly, and I want to be sure I understood that correctly, so here goes. I know what a transaction is supposed to do, I'm just not sure whether I understood the statement semantics or not.

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

By default, autocommit mode is enabled in MySQL.

Now, SET autocommit=0; will begin a transaction, SET autocommit=1; will implicitly commit. It is possible to COMMIT; as well as ROLLBACK;, in both of which cases autocommit is still set to 0 afterwards (and a new transaction is implicitly started).

START TRANSACTION; will basically SET autocommit=0; until a COMMIT; or ROLLBACK; takes place.

In other words, START TRANSACTION; and SET autocommit=0; are equivalent, except for the fact that START TRANSACTION; does the equivalent of implicitly adding a SET autocommit=0; after COMMIT; or ROLLBACK;

If that is the case, I don't understand http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable - seeing as having an isolation level implies that there is a transaction, meaning that autocommit should be off anyway?

And if there is another difference (other than the one described above) between beginning a transaction and setting autocommit, what is it?

Being aware of the transaction (autocommit, explicit and implicit) handling for your database can save you from having to restore data from a backup.

Transactions control data manipulation statement(s) to ensure they are atomic. Being "atomic" means the transaction either occurs, or it does not. The only way to signal the completion of the transaction to database is by using either a COMMIT or ROLLBACK statement (per ANSI-92, which sadly did not include syntax for creating/beginning a transaction so it is vendor specific). COMMIT applies the changes (if any) made within the transaction. ROLLBACK disregards whatever actions took place within the transaction - highly desirable when an UPDATE/DELETE statement does something unintended.

Typically individual DML (Insert, Update, Delete) statements are performed in an autocommit transaction - they are committed as soon as the statement successfully completes. Which means there's no opportunity to roll back the database to the state prior to the statement having been run in cases like yours. When something goes wrong, the only restoration option available is to reconstruct the data from a backup (providing one exists). In MySQL, autocommit is on by default for InnoDB - MyISAM doesn't support transactions. It can be disabled by using:

SET autocommit = 0

An explicit transaction is when statement(s) are wrapped within an explicitly defined transaction code block - for MySQL, that's START TRANSACTION. It also requires an explicitly made COMMIT or ROLLBACK statement at the end of the transaction. Nested transactions is beyond the scope of this topic.

Implicit transactions are slightly different from explicit ones. Implicit transactions do not require explicity defining a transaction. However, like explicit transactions they require a COMMIT or ROLLBACK statement to be supplied.

Conclusion

Explicit transactions are the most ideal solution - they require a statement, COMMIT or ROLLBACK, to finalize the transaction, and what is happening is clearly stated for others to read should there be a need. Implicit transactions are OK if working with the database interactively, but COMMIT statements should only be specified once results have been tested & thoroughly determined to be valid.

That means you should use:

SET autocommit = 0;

START TRANSACTION;
  UPDATE ...;

...and only use COMMIT; when the results are correct.

That said, UPDATE and DELETE statements typically only return the number of rows affected, not specific details. Convert such statements into SELECT statements & review the results to ensure correctness prior to attempting the UPDATE/DELETE statement.

Addendum

DDL (Data Definition Language) statements are automatically committed - they do not require a COMMIT statement. IE: Table, index, stored procedure, database, and view creation or alteration statements.

MySQL 8.0 Reference Manual :: 13.3.1 START , See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”. If autocommit mode is disabled within a session with SET autocommit = 0 This mode of operation might be unfamiliar if you have experience with other� @CMCDragonkai: SET autocommit=0, START TRANSACTION and BEGIN are all equivalent—except for the state in which MySQL will be when the transaction ends (because it is committed or rolled back): if you have autocommit=0, then MySQL will automatically commence a new transaction (as though START TRANSACTION or BEGIN had been issued again); whereas if you have autocommit=1 (because one of the

In InnoDB you have START TRANSACTION;, which in this engine is the officialy recommended way to do transactions, instead of SET AUTOCOMMIT = 0; (don't use SET AUTOCOMMIT = 0; for transactions in InnoDB unless it is for optimizing read only transactions). Commit with COMMIT;.

You might want to use SET AUTOCOMMIT = 0; in InnoDB for testing purposes, and not precisely for transactions.

In MyISAM you do not have START TRANSACTION;. In this engine, use SET AUTOCOMMIT = 0; for transactions. Commit with COMMIT; or SET AUTOCOMMIT = 1; (Difference explained in MyISAM example commentary below). You can do transactions this way in InnoDB too.

Source: http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_autocommit

Examples of general use transactions:

/* InnoDB */
START TRANSACTION;

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 */
SET AUTOCOMMIT = 0;

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 */

MySQL 5.6 Reference Manual :: 14.7.2.2 autocommit , The 1 means that autocommit is currently enabled. Let's leave it enabled so we can test the difference with not using transactions. make sure the proper mode is set when starting the server by including it in Please tell me more about your execution environment (mysql command line? query browser?) SET AUTOCOMMIT=1; The autocommit variable is local to a single session, so changing the mode will affect only queries run from your session and only for as long as your session is connected. If you turn autocommit off, you will not need to call START TRANSACTION to start a transaction.

If you want to use rollback, then use start transaction and otherwise forget all those things, since MySQL sets autocommit to 1 by default.

Transactions and Autocommit, MySQL Architecture and History MySQL is very different from other database major differences between the storage engines, and why those differences 1 START TRANSACTION; 2 SELECT balance FROM checking WHERE You can enable or disable AUTOCOMMIT for the current connection by setting a variable: MySQL automatically commits statements that are not part of a transaction. The results of any UPDATE,DELETE or INSERT statement not preceded with a BEGIN or START TRANSACTION will immediately be visible to all connections. The AUTOCOMMIT variable is set true by default. This can be changed in the following way,

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

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. For example, if you need to write to table t1 and read from table t2, you can do this:

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

1. MySQL Architecture and History, This is a short tutorial on how to use transactions with PDO object in PHP. In certain scenarios, you might want to ensure that all queries have executed We begin our transaction by calling the beginTransaction function. as this function is called, the autocommit mode for MySQL is set to 0 (by default, this is set to 1). To use multiple-statement transactions, switch autocommit off with the SQL statement SET autocommit = 0 and end each transaction with COMMIT or ROLLBACK as appropriate. To leave autocommit on, begin each transaction with START TRANSACTION and end it with COMMIT or ROLLBACK .

PHP: PDO transaction example., execute(SQL_TABLE_CREATE); // start transaction block conn.setAutoCommit( false); // default true // Run list of insert commands psInsert� MySQL transaction statements. MySQL provides us with the following important statement to control transactions: To start a transaction, you use the START TRANSACTION statement. The BEGIN or BEGIN WORK are the aliases of the START TRANSACTION. To commit the current transaction and make its changes permanent, you use the COMMIT statement.

JDBC Transaction example, And you'll save a ton of time with Spring's @Transactional annotation You need a connection to the database to start transactions. If you already played with Spring's @Transactional annotation you might have encountered something setAutoCommit(false); // (1) // execute some SQL that e.g. // inserts� Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 12.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”. ----</Q>---- I don't see where MySQL official manual promises value of autocommit would be changed after START TRANSACTION, COMMIT or ROLLBACK statement.

Spring Transaction Management: @Transactional In-Depth, This statement handle can be used for multiple things. This option is for older mysql databases that don't have secure auth set. Starting with 4.043, DBD:: mysql with mysql_ssl=1 could refuse connection to MySQL However, this behaviour is disabled when AutoCommit is off: Otherwise the transaction state would be� In MySQL, LOCK TABLES commits any open transactions, but turning off autocommit isn't actually starting a transaction. MySQL is funny that way. In PDO, starting a transaction using beginTransaction doesn't actually start a new transaction, it just turns off autocommit. In most databases, this is sane, but it can have side effects with MySQL's

Comments
  • Wow, that was fast :-) Thanks a lot! What I don't quite understand is why I need to SET autocommit = 0; in the example above; doesn't starting a transaction imply that? And if it doesn't, what is the difference?
  • @tkolar: Disabling autocommit forces everyone to use START TRANSACTION; not everyone is aware they should be using it. 'Course, another DBA might turn it back on too...
  • I guess having SET autocommit = 0; is just a preference as to not forget to use trasactions
  • So when i explicitly start a transaction by START TRANSACTION , i must also explicitly specify COMMIT even autocommit is ON. Is it?
  • In nested Stored procedure Call . What should i use @OMGPonies
  • Does someone know why exactly table locks only work with autocommit=0 but not with START TRANSACTION? It seems totally arbitrary to me. Is there a technical reason?