Search code examples
mysqlmariadbautocommit

MySQL: autocommit flag is on but transaction still can rollback

I am using MariaDB version 10.3.13. The last time I checked, autocommit flag is on.

MariaDB> SHOW VARIABLES WHERE Variable_name='autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

As I read on document, when autocommit is on, you cannot rollback transaction as stated here

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.

So I write a little script for testing. Firstly, I start a transaction and update some data:

BEGIN;
UPDATE foo SET year = 2019  WHERE id = 1;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0

So It looks like I have done. Then I rollback:

ROLLBACK;
Query OK, 0 rows affected (0.005 sec)

Then I check again updated record, I see that data is not change. It's weird, because I think data will always change no matter what because autocommit flag is on.

Please explain for me why. Thanks

Solution

  • Even if autocommit is on, if you use BEGIN or START TRANSACTION, it temporarily suspends the autocommit of each statement until you finish the transaction.

    You cited the manual page https://dev.mysql.com/doc/refman/8.0/en/commit.html, which goes on to explain:

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

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

    (emphasis mine)

    In other words, the statements you execute after BEGIN or START TRANSACTION are not committed automatically. This is expected.