MySQL Bugs: #43073: START TRANSACTION and COMMIT do NOT change AUTOCOMMIT

Bug #43073	START TRANSACTION and COMMIT do NOT change AUTOCOMMIT
Submitted:	21 Feb 2009 7:07	Modified:	25 Feb 2011 23:03
Reporter:	al baker	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.0.32-Debian_7etch1-log	OS:	Other
Assigned to:		CPU Architecture:	Any
Tags:	autocommit, commit

Description:
The manual states that the Command START TRANSACTION changes the value of 'autocommit' from ir Default value of '1' to a value of '0'   until a COMMIT command is issued at which time the value of 'autocommit' is set back to a value of '1';

This does not appear to happen based on the following commands:

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |                       <- Default Value
+--------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;              <- Transaction Started
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1                          <- Value is STILL '1' should be ZERO
+--------------+
1 row in set (0.00 sec)

mysql>
mysql> set autocommit=0;                <- Manually set Value
Query OK, 0 rows affected (0.00 sec)

mysql> COMMIT;                          <- Commit issued, should CHANGE value
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |                        <- Value is UN-Changed
+--------------+
1 row in set (0.00 sec)

This happened on MYSQL

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.32-Debian_7etch1-log |
+--------------------------+
1 row in set (0.00 sec)

How to repeat:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql>
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

Thank you for the report.

There is next text at http://dev.mysql.com/doc/refman/5.0/en/commit.html:

----<Q>----
To disable autocommit mode 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. 
----</Q>----

This means autocommit mode would be disabled after START TRANSACTION and before explicit or implicit commit or rollback, but there is no word about value of server variable autocommit would be changed.

Where did you find information about statements START TRANSACTION or COMMIT/ROLLBACK change value of the system variable autocommit?

Per "http://www.oreillynet.com/databases/blog/2007/02/mysql_transactions_and_autocom.html"

Quote" With autocommit enabled, every statement is wrapped within its own transaction. Successful execution of a statement is implicitly followed by a COMMIT, and the occurrence of an error aborts the transaction, rolling back any changes.

By default, autocommit is enabled in MySQL. You can check the current setting by executing the following statement:

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

The @@ prefix denotes a server variable. The 1 means that autocommit is currently enabled. Let’s leave it enabled so we can test the difference with not using transactions.
"

Is seems to me to be Totally Obvous - so I am likely missing something

1 - If this VARIABLE is suppose to show, at ANY TIME,, what stat your AUTOCOMMIT is in,
And
If the statements"START TRANSACTION " and "COMMIT" are ,per the manual, designed "To CHANGE THE STATE" of AUTOCOMMIT

THEN

it seems like Either there is a Big Freakin Bug  with the Variable @@AUTOCOMMUIT and what is repents,  OR Thers is a Big Freakin Bug with "START TRANSACTION and COMMIT" being broke.

Because I can see No Way fo everything to be workin OK, AND the documentation to be correct, AND for SELECT @@AUTOCOMMIT done Before a START TRANSACTION to be the  SANE as one done AFTER a START TRANSACTION

Thank you for the feedback.

But blog post is not official MySQL manual. And official MySQL manual described behavior of autocommit in clear way at http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#sysvar_autocommit:

----<Q>----
autocommit

The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. By default, client connections begin with autocommit set to 1. If you change autocommit mode from 0 to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 12.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”. 
----</Q>----

I don't see where MySQL official manual promises value of autocommit would be changed after START TRANSACTION, COMMIT or ROLLBACK statement.

Please correct me if you still think this is not so.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

THIS IS NOT AN ISSUE.

I have been looking at this problem and I have the answer for those who consider this a bug. The manual clearly indicates that running the "START TRANSACTION" command will set autocommit=0 for that scripted run. This is true. It DOES NOT set autocommit for the server though. autocommit for the server will remain in its previous state.

If you are having trouble running transactions, check your database table types. The default MySQL table type is MYISAM. This table type DOES NOT support transactions and will autocommit every time no matter what the script's transactional state is. Change the database table type to InnoDB and transaction will work per the manual.

MySQL really should throw a warning if it encounters non-transactional tables while executing transactions. This would end a lot of problems for people.

Please be aware that fulltext searching is NOT supported in InnoDB. If you need both fulltext searches and transactions, you will have to create duplicate MYISAM and InnoDB tables and create a software synchronization routine to "rollback" changes to the MYISAM table. This is not too difficult to do as MYISAM uses blocking table locks while InnoDB uses record locking. This means that the MYISAM table can be locked, transaction can be run against the InnoDB database and then those changes can be rolled back, if need be, manually on the MYISAM table and automatically using rollback on the InnoDB table. BE WARNED - you will take a MAJOR performance hit if you do this!!!

GOSTRATH

I agree with Al Baker. The MySQL manual is unclear about the interaction between START TRANSACTION and @@autocommit, using START and SET AUTOCOMMIT=0 interchangeably and suggesting that the value of @@autocommit reflects the internal state of the transaction.

I'm trying to use exception handling to detect a failed query that's inside a transaction that began with START TRANSACTION, and the @@autocommit value is still 1, which according to the essence of the documentation really should be 0. Arguing that the docs don't explicitly say that the value of the server variable doesn't follow the actual internal state of the transaction is silly...a developer (particularly a web developer) EXPECTS a server variable to reflect the current state of the machine.

So I guess I just have to force a rollback on exception, no matter what. Seems like a bug to me.

Tim,

thank you for the feedback. But for InnoDB tables it does not matter to which value autocommit set to. InnoDB treats every statement as transaction in case if autocommit=1. In case of MyISAM it does not matter too as it is not transactional. Therefore you need to fix logic in your application.

IMO the autocommit session variable and the autocommit server variable are easily confused. I ran into the same issues as Al Baker here, both lacking in knowledge obviously :)

Perhaps it would be beneficial to add a note about this to the manual?

http://dev.mysql.com/doc/refman/5.5/en/commit.html