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”.
Is all sql will ececute in the transaction ,no matter whether do you use begin and commit explicitly.
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
autocommitmode. If set to1, all changes to a table take effect immediately. If set to0, you must useCOMMITto accept a transaction orROLLBACKto cancel it. Ifautocommitis0and you change it to1, MySQL performs an automaticCOMMITof any open transaction. Another way to begin a transaction is to use aSTART TRANSACTIONorBEGINstatement.