MySQL: autocommit flag is on but transaction still can rollback

Related searches

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

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.

14.7.2.2 autocommit, Commit, and Rollback, Transactions in Client-Side Languages. In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control� In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as COMMIT to the MySQL server as strings just like any other SQL statements such as SELECT or INSERT. Some APIs also offer separate special transaction commit and rollback functions or methods.

BEGIN is an explicit start of transaction that disables the effect of autocommit.

Autocommit applies to SQL that isn't explicitly transactional.

Transaction has set autocommit = 0, but still the update happens , In MySQL most DDL statements, including ALTER TABLE , implicitly issue a to your transaction, any of the updates that fail will rollback previous updates. You need to create a flag that checks whether or not the transaction� This restriction makes it only possible to do XA COMMIT/ROLLBACK to end another prepared XA transaction branch when the current session is in AUTOCOMMIT mode, in non-AUTOCOMMIT mode, one can't do so even if there is no active transaction at all. Such a restriction is not necessary and inconvenient in my opinion.

While the previous Answers are correct, let me point out another angle.

If you run a billion-row UPDATE (which will take hours), and pull the plug on the computer, the UPDATE will be partially finished. Upon restarting the computer (and MySQL), what happens? In MyISAM, some rows would be updated, some would not. But with InnoDB, it will ROLLBACK the partially finished UPDATE (and may take some more hours to do so).

So, the quote from the manual was not only ambiguous (as pointed out by the other Answers), but it was 'literally' incorrect. In my example, the change can and was rolled back.

I like to phrase it thus:

Autocommit=ON, when not otherwise in a transaction, is equivalent to wrapping the statement in BEGIN and COMMIT. That is, the statement is performed atomically.

I filed http://bugs.mysql.com/95414 .

MySQL Workbench, start with auto commit off, Because it worked for me before (WB 5?), but doesn't work now on WB 6.3. First, we need to find out where does WB is looking for the file. There are several� Press CTRL+C to copy. 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.

autocomit and rollback problems with MySQL, MySQLNonTransientConnectionException: Can't call rollback when autocommit= true. But, I've set the autocommit flag to false before calling rollback() Problem still exists even after using the following code. Mean Does the table in question support transactions? aka this isn't a MyISAM table is it? (If mixed-engine transactions are infrequent, you can use SET TRANSACTION ISOLATION LEVEL to set the isolation level to SERIALIZABLE on a per-transaction basis as necessary.) If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode.

Manage Transactions in MySQL – Lesson 2, But before I get to specifics surrounding each table, I need to revisit the notion of … MySQL contains an environment variable called autocommit . By default During a transaction, MySQL will apply a lock to a page worth of data. All other Note the use of the BEGIN WORK-COMMIT/ROLLBACK syntax. With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state. START TRANSACTION permits several modifiers that control transaction characteristics. To specify multiple modifiers, separate them by commas.

Once the START TRANSACTION; command is executed, autocommit remains disabled until either a COMMIT (called at line 20) or ROLLBACK command is executed. Had ROLLBACK instead of COMMIT been used on line 20, all changes would have been reverted back to the state prior to attempting the first UPDATE at line 8.

Comments
  • If you run a billion-row UPDATE: so you mean we will run this query inside a transaction block ? Thanks.
  • @TrầnKimDự - Logically, yes. It will be run as if you had added BEGIN and COMMIT around it.