CODEWITHSUNDEEP
mysqlsqltransactionsautocommit
Whirlwind
Whirlwind

Reputation: 13675

Why does START TRANSACTION doesn't affect on autocommit implicitly as it should

So this is from the docs:

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;

So from my understanding, unlike using BEGIN; command which shouldn't set autocommit to 0, START TRANSACTION should set it to 0.

Now if I do this (after I start a transaction):

select @@autocommit;

I get the value of 1.

Why autocommit is still enabled even if I use START TRANSACTION command? I thought that the autocommit variable is local to a single session. Or maybe even if it says 1, it is actually set to 0 within a transaction but just can't get that info by running a SELECT @@autocommit; query?

Upvotes: 3

Views: 2507

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562553

https://dev.mysql.com/doc/refman/5.7/en/commit.html says:

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 value of the autocommit variable is where MySQL stores the "previous state" so that it can revert to it after your transaction completes.

You can experimentally confirm that this behavior is followed. Make a change in a transaction, and then roll back the transaction. 

CREATE TABLE test.MyTable (mycolumn TEXT);

START TRANSACTION;
INSERT INTO test.MyTable (mycolumn) VALUES ('Thing 1');
ROLLBACK;

SELECT * FROM test.MyTable; -- finds the row is gone

Notice that the change you did is rolled back. If autocommit had been in effect, then rollback would never work, because each statement would commit as soon as it is executed.

Upvotes: 3

Rick James
Rick James

Reputation: 142356

I suggest that @@autocommit is irrelevant. The engine knows that it is in a transaction (START or BEGIN), so it ignores the setting of autocommit. Instead it hangs onto changes until COMMIT (or ROLLBACK).

Or do you have some reason to believe that the value of autocommit is relevant? (Aside from SELECT @@autocommit.)

Upvotes: 1

Related Questions