MySQL transactions in InnoDB

Question

I am an MySQL newbie who's learning about transactions, and I'm using the InnoDB engine.

In the MySQL reference manual, I see that they ask to set autocommit to 0 before starting a transaction, but in both ways (setting it to either 0 or 1) I see the same behavior: the transaction is validated after commit and invalidated with Rollback. What is the difference between setting autocommit to 0 or to 1??

Answer 1

There are 3 modes for transactions in InnoDB:

• autocommit=1 (or ON): Each statement is a transaction. (See Marc's answer)

• autocommit=0 (or OFF): You must eventually issue COMMIT, else changes will be lost. (I see this modes a too error-prone to ever use.)

• BEGIN (or START TRANSACTION) ... COMMIT (or ROLLBACK): This explicitly spells out the extent of the transaction. autocommit is ignored. I consider this to be 'best practice'

Answer 2

If autocommit is on, then every query you issue effectively runs like this:

start transaction;
...do a query ...
commit;
start transaction;
... do another query ...
commit
etc...

with autocommit off, there's no automatic transaction, and you start it yourself, which makes the code run like this:

start transaction
...do a query ...
...do another query ...
... etc...
commit;

If you only ever issue single command queries, then there's not much of a difference in behaviors. it's only when you start issuing multiple sequential queries that the new behavior really kicks in.

Answer 3

I think if you use MyISAM storage engine you must set autocommit to 0 but with InnoDB ints not necessary because start transaction sets autocommit to 0 LINK