自动提交、提交、回滚
1.如果启用了自动提交的会话可以通过使用显式START TRANSACTION或BEGIN语句启动并使用COMMIT或ROLLBACK语句结束来执行多语句事务。
A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK statement.
2.如果在会话中设置SET autocommit = 0,则自动打开一个事务,执行commit或rollback结束事务再次打开一个新事务
If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.
注意:测试中发现,实际情况是,执行sql时会则自动打开一个事务,执行commit或rollback结束事务,再执行sql时会再次打开一个新事务,如果不执行sql,是没有新事务打开的,不知道是否是我理解可能错了.
3.如果一个session关闭了自动提交,在没有提交的情况下异常退出,mysql会回滚该事务。
If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.
注意:测试中发现,相反,如果开启了自动提交,在没有提交的情况下异常退出,mysql会提交该事务。
4.提交和回滚都会释放innodb锁(注意这里没有提到表锁,表锁释放必须通过unlock tables)
Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.
5.如果要在一个事务中执行多个sql,可以SET autocommit = 0,最后根据需要使用commit或rollback来结束事务。
To use multiple-statement transactions, switch autocommit off with the SQL statement SET autocommit = 0 and end each transaction with COMMIT or ROLLBACK as appropriate.