3

I am reviewing code by another developer. He has a commit that explicitly adds set autocommit=0 at the start of a MySQL transaction. This is causing problems for other non-transactional queries in the session.

Does adding set autocommit=0 within a transaction do anything for that transaction? I assume it doesn't, since transactions implicitly do this anyway.

[The only rationale I can come up with for this commit is perhaps the db once used MyISAM (versus the current InnoDB), and this was maybe a way to mimic transactions in the former?]

Federico Razzoli
  • 4,901
  • 1
  • 19
  • 21
peter bray
  • 1,325
  • 1
  • 12
  • 22

2 Answers2

2

If a transaction is active, it is not affected by SET autocommit = 0.

Otherwise, if the former value of autocommit was 1, SET autocommit = 0 starts a new transaction.

If you are using MariaDB and you are in doubt, you can check the in_transaction variable.

Federico Razzoli
  • 4,901
  • 1
  • 19
  • 21
  • The code is as follows: `START TRANSACTION; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET autocommit = 0; [some queries] COMMIT;` What is the point of the `SET autocommit = 0` in this? – peter bray Feb 19 '15 at 23:06
  • 1
    No purpose. Also, the isolation level cannot be changed during the transaction. – Federico Razzoli Feb 19 '15 at 23:13
2

From the MySQL Reference:

After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB or NDB) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes.

Setting autocommit=0 will modify the entire session. Transactions and non-transactional commands will need to explictly committed to affect the DB.

David K-J
  • 930
  • 7
  • 14
  • 1
    Right, but you have to do explicit commits (or start a new transaction) for transactions anyway. So, in regards to transactions, what effect does disabling autocommit have? – peter bray Feb 19 '15 at 23:11