In InnoDB,is all sql in the transaction?

1
votes

I seems　have a wrong idea that :

update table_name set id=222 where id >333;

my old opinion is above single sql without begin and commit will not start a transaction. but it seems wrong.

But when i read the mysql doc,i found that seems that all sql will in a transaction.if you do not explicit use begin and commit,it will start transaction implicit in InnoDB.

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error. See Section 14.21.4, “InnoDB Error Handling”.

my question :

Is all sql will ececute in the transaction ,no matter whether do you use begin and commit explicitly.

1
mysqltransactionsinnodb

1 Answers

4
votes

it will be executed in single transactions - unless grouping statements with START TRANSACTION & COMMIT (the autocommit configuration will then be ignored, no matter it's value). SET autocommit = 0/1 can be used to control the level of isolation on-the-fly. there's also a configuration option for that, in order to change the behavior globally:

[mysqld]
autocommit=0

the documentation for "Server System Variables" explains it in detail:

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. If autocommit is 0 and you change it 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.