Transactions and AUTOCOMMIT in Mysql

Question

To my knowledge for InnoDB tables in MySQL, all transactions are wrapped with a START TRANSACTION; and end with a COMMIT; unless explicitly stated not to.

If I were to explictly define the transaction block, would it be correct to do something like this:

SET AUTOCOMMIT = 0;
START TRANSACTION;
[SQL STATEMENTS]
COMMIT;
SET AUTOCOMMIT = 1;

Would the next transaction after this go back to the way InnoDb handles transactions by default? My intention is to only sometimes have explicitly defined transactions in my application but all other transactions will be handled by the engine.

Nonos Nonos · Accepted Answer · 2013-03-17T15:16:29

You do not need to set autocommit to 0 in your case. Defining a transaction implicitly does that for you. From MySQL documentation: "To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:"

Transactions and AUTOCOMMIT in Mysql

1 Answers