MySQL AUTOCOMMIT status while using BEGIN and START TRANSACTION

Question

I need to use a transaction in my project on MySQL. But I'm not sure if I have to use mysql_query("SET AUTOCOMMIT=0"); or not.
I know I have 2 options:

1. BEGIN
2. START TRANSACTION

Also I have heard that one of the both items does not need using AUTOCOMMIT = 0.
Please help me to know when I have to use AUTOCOMMIT = 0 actually, With BEGIN or with START TRANSACTION?

Thank you.

Unchain

@Random I have to do it unfortunately. I have a project with many lines of codes written in this way and it's so hard to change them. – Unchain

But you have to face this hard work to provide security to your users – Multilateral

@Random this was not my question, I just need to know basics about MySQL Transactions. Can you give me your answer about my question please? – Unchain

Answer 1

As explained in the manual:

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back.

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

The manual goes on to say:

To disable autocommit mode explicitly, use the following statement:

SET autocommit=0;

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

autocommit is a session variable and must be set for each session. To disable autocommit mode for each new connection, see the description of the autocommit system variable at Section 5.1.3, “Server System Variables”.

BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION is standard SQL syntax and is the recommended way to start an ad-hoc transaction.

Derk

Thank you eggyal. I'd seen this article in MySQL official website. But I saw some articles that had said when we use BEGIN, we don't need to set AUTOCOMMIT = 0, but when we use START TRANSACTION we must do it. Regarding to what I said, and to this article, what is your idea ? – Unchain

@Derk You mean you don't need to disable autocommit when using START TRANSACTION and/or BEGIN? It will be automatically disabled? – Epinephrine

@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 other commands was issued) then MySQL will return to automatically committing every statement, and another START TRANSACTION or BEGIN statement will be required to commence a new transaction – Derk

@CMCDragonkai: So the answer to your question is "yes, one doesn't need to disable autocommit when using START TRANSACTION or BEGIN". – Derk

That's what the manual says, but that's not the behavior I'm getting in my testing. I have to set autocommit back to false after the transaction, or else my other non-transaction queries don't execute. I'm using PHP 5.6.28 , mysql 5.1.73, and the function mysqli_autocommit – Sheedy

Answer 2

There is a small difference between Start Transaction and SET AUTOCOMMIT=0. If START TRANSACTION appears at the beginning of session and AUTOCOMMIT is set 1 (Mysql begins with AUTOCOMMIT enabled) after ROLLBACK, Autocommit is set silently to 1 again If I put SET AUTOCOMMIT=0, instead of START TRANSACTION, evidently a ROLLBACK let AUTOCOMMIT disabled

Jonjona

Please consider re-formatting the text. – Illlooking