【数据库】事务与锁

参考： https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html

 事务ACID：bytebytego 彩图 https://mp.weixin.qq.com/s/8Q9Ze82cJx-BzRxchkbiIQ

 

autocommit, Commit, and Rollback
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. 

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. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

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.

If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

Some statements implicitly end a transaction, as if you had done a COMMIT before executing the statement. For details, see Section 13.3.3, “Statements That Cause an Implicit Commit”.

A COMMIT means that the changes made in the current transaction are made permanent and become visible to other sessions. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction. Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.

 

SQL 行锁类型 说明

INSERT ... 排他锁 自动加锁
UPDATE ... 排他锁 自动加锁
DELETE ... 排他锁 自动加锁
SELECT（正常） 不加任何锁
SELECT ... LOCK IN SHARE MODE 共享锁 需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE 排他锁 需要手动在SELECT之后加FOR UPDATE