深入浅出MySQL事务和锁定语句

https://dev.mysql.com/doc/refman/8.0/en/sql-transactional-statements.html

13.3事务和锁定语句

13.3.1启动事务、提交和回滚语句

开启事务

begin
START TRANSACTION
1
2

提交事务

COMMIT
1

回滚事务

ROLLBACK
1

查询自动提交

show SESSION VARIABLES where variable_name ="autocommit"
show GLOBAL VARIABLES where variable_name ="autocommit"
1
2

自动提交设置，0关闭，1开启

SET autocommit = 0
1

默认情况下，MySQL开启自动提交。没有显示开启事务的时候，每条语句都是原子的。

当显示开启事务时候，在提交或者回滚之前自动提交模式会被关闭。

13.3.2不能回滚的语句

13.3.3导致隐式提交的语句

13.3.4保存点、回滚到保存点和释放保存点语句

13.3.5锁定备份实例和解锁实例语句

13.3.6锁定表和解锁表语句

13.3.7设置事务语句

13.3.8 XA事务

隔离级别

From highest amount of consistency and protection to the least, the isolation levels supported by InnoDB are: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

REPEATABLE READ 可重复读是 InnoDB 默认隔离级别。

READ UNCOMMITTED 读未提交

The isolation level that provides the least amount of protection between transactions. Queries employ a locking strategy that allows them to proceed in situations where they would normally wait for another transaction. However, this extra performance comes at the cost of less reliable results, including data that has been changed by other transactions and not committed yet (known as dirty read). Use this isolation level with great caution, and be aware that the results might not be consistent or reproducible, depending on what other transactions are doing at the same time. Typically, transactions with this isolation level only do queries, not insert, update, or delete operations.

See Also ACID, dirty read, isolation level, locking, transaction.

总结：读未提交会产生脏读（别的事务修改未提交的数据），隔离级别最低，性能最高，适用于只读场景。

READ COMMITTED 读已提交

An isolation level that uses a locking strategy that relaxes some of the protection between transactions, in the interest of performance. Transactions cannot see uncommitted data from other transactions, but they can see data that is committed by another transaction after the current transaction started. Thus, a transaction never sees any bad data, but the data that it does see may depend to some extent on the timing of other transactions.

When a transaction with this isolation level performs UPDATE … WHERE or DELETE … WHERE operations, other transactions might have to wait. The transaction can perform SELECT … FOR UPDATE, and LOCK IN SHARE MODE operations without making other transactions wait.

SELECT … FOR SHARE replaces SELECT … LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.

See Also ACID, isolation level, locking, REPEATABLE READ, SERIALIZABLE, transaction.

总结：这个隔离接别的事务看不到其他事务未提交的数据。可以看到事务开始后别的事务已经提交的修改。

REPEATABLE READ 可重复读

The default isolation level（默认隔离级别） for InnoDB. It prevents any rows（阻止任何行） that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.（事务开启的时候）

When a transaction with this isolation level performs UPDATE … WHERE, DELETE … WHERE, SELECT … FOR UPDATE, and LOCK IN SHARE MODE operations, other transactions might have to wait.

SELECT … FOR SHARE replaces SELECT … LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.

可重复读：简单说事务开始的时候数据是什么样，结束的时候就是什么样。

SERIALIZABLE

The isolation level that uses the most conservative locking strategy, to prevent any other transactions from inserting or changing data that was read by this transaction, until it is finished. This way, the same query can be run over and over within a transaction, and be certain to retrieve the same set of results each time. Any attempt to change data that was committed by another transaction since the start of the current transaction, cause the current transaction to wait.

This is the default isolation level specified by the SQL standard. In practice, this degree of strictness is rarely needed, so the default isolation level for InnoDB is the next most strict, REPEATABLE READ.

See Also ACID, consistent read, isolation level, locking, REPEATABLE READ, transaction.

总结：隔离级别最高，事务开启后，其他事务的修改操作必须排队，可以防止一切问题，但是效率最低。

phantom 幻读

A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE clause of the query.

This occurrence is known as a phantom read. It is harder to guard against than a non-repeatable read, because locking all the rows from the first query result set does not prevent the changes that cause the phantom to appear.

Among different isolation levels, phantom reads are prevented by the serializable read level, and allowed by the repeatable read, consistent read, and read uncommitted levels.

See Also consistent read, isolation level, non-repeatable read, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, transaction.

MVCC

Acronym for “Multi Version Concurrency Control”. This technique lets InnoDB transactions with certain isolation levels perform consistent read operations; that is, to query rows that are being updated by other transactions, and see the values from before those updates occurred. This is a powerful technique to increase concurrency（增加并发）, by allowing queries to proceed without waiting due to locks held by the other transactions.

This technique is not universal in the database world. Some other database products, and some other MySQL storage engines, do not support it.

See Also ACID, concurrency, consistent read, isolation level, lock, transaction.

consistent read

A read operation that uses snapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time. If queried data has been changed by another transaction, the original data is reconstructed based on the contents of the undo log. This technique avoids some of the locking issues that can reduce concurrency by forcing transactions to wait for other transactions to finish.

With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed. With READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. Because a consistent read does not set any locks on the tables it accesses, other sessions are free to modify those tables while a consistent read is being performed on the table.

For technical details about the applicable isolation levels, see Section 15.7.2.3, “Consistent Nonlocking Reads”.

See Also concurrency, isolation level, locking, READ COMMITTED, REPEATABLE READ, snapshot, transaction, undo log.

snapshot 快照

A representation of data at a particular time, which remains the same even as changes are committed by other transactions. Used by certain isolation levels to allow consistent reads.

See Also commit, consistent read, isolation level, transaction.