mysql 事务的一些资料整理

MYSQL TRANSACTION BASIC

https://dev.mysql.com/doc/refman/8.0/en/commit.html

MYSQL INODB

https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

 

定义

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_transaction

transaction

Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

Database transactions, as implemented by InnoDB, have properties that are collectively known by the acronym ACID, for atomicity, consistency, isolation, and durability.

See Also ACID, commit, isolation level, lock, rollback.

google 翻译：
事务是可以提交或回退的原子工作单元。 当事务对数据库进行多次更改时，要么在提交事务后所有更改成功，要么在回滚事务时撤消所有更改。

由InnoDB实现的数据库事务具有原子性，一致性，隔离性和持久性的首字母缩写ACID属性。

另请参见ACID，提交，隔离级别，锁定，回滚。

 

ACID

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_acid

An acronym standing for atomicity, consistency, isolation, and durability. These properties are all desirable in a database system, and are all closely tied to the notion of a transaction. The transactional features of InnoDB adhere to the ACID principles.

Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.

Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other's uncommitted data. This isolation is achieved through the locking mechanism. Experienced users can adjust the isolation level, trading off less protection in favor of increased performance and concurrency, when they can be sure that the transactions really do not interfere with each other.

The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to. Durability typically involves writing to disk storage, with a certain amount of redundancy to protect against power failures or software crashes during write operations. (In InnoDB, the doublewrite buffer assists with durability.)

See Also atomic, commit, concurrency, doublewrite buffer, isolation level, locking, rollback, transaction.

代表原子性，一致性，隔离性和持久性的首字母缩写。这些属性都是数据库系统所需要的，并且都与事务的概念紧密相关。 InnoDB的事务功能遵循ACID原则。

事务是可以提交或回滚的基本工作单元。当事务对数据库进行多次更改时，要么在提交事务后所有更改成功，要么在回滚事务时撤消所有更改。

在每次提交或回滚之后以及正在进行的事务处理期间，数据库始终保持一致状态。如果正在跨多个表更新相关数据，则查询将看到所有旧值或所有新值，而不是新旧值的混合。

事务在进行过程中相互保护（隔离）；他们不能互相干扰，也不能看到彼此的未提交数据。这种隔离是通过锁定机制实现的。有经验的用户可以确定交易确实不会相互干扰时，可以调整隔离级别，牺牲较少的保护以提高性能和并发性。

事务的结果是持久的：提交操作成功后，该事务所做的更改就不会出现电源故障，系统崩溃，竞争状况或许多非数据库应用程序容易受到的其他潜在危险。耐用性通常涉及写入磁盘存储，并具有一定数量的冗余以防止写入操作期间出现电源故障或软件崩溃。 （在InnoDB中，双写缓冲区具有持久性。）

另请参见原子，提交，并发，双写缓冲区，隔离级别，锁定，回滚和事务。

 

commit

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_commit

A SQL statement that ends a transaction, making permanent any changes made by the transaction. It is the opposite of rollback, which undoes any changes made in the transaction.

InnoDB uses an optimistic mechanism for commits, so that changes can be written to the data files before the commit actually occurs. This technique makes the commit itself faster, with the tradeoff that more work is required in case of a rollback.

By default, MySQL uses the autocommit setting, which automatically issues a commit following each SQL statement.

See Also autocommit, optimistic, rollback, SQL, transaction.

 

isolation level

One of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.

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.

With InnoDB tables, many users can keep the default isolation level (REPEATABLE READ) for all operations. Expert users might choose the READ COMMITTED level as they push the boundaries of scalability with OLTP processing, or during data warehousing operations where minor inconsistencies do not affect the aggregate results of large amounts of data. The levels on the edges (SERIALIZABLE and READ UNCOMMITTED) change the processing behavior to such an extent that they are rarely used.

See Also ACID, OLTP, READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, transaction.

隔离级别
数据库处理的基础之一。隔离是缩写ACID中的I；隔离级别是一种设置，用于在多个事务同时进行更改和执行查询时微调性能与结果的可靠性，一致性和可重复性之间的平衡。

从最高程度的一致性和保护到最低程度，InnoDB支持的隔离级别为：可SERIALIZABLE，可重复读取，READ COMMITTED和READ UNCOMMITTED。

使用InnoDB表，许多用户可以为所有操作保留默认的隔离级别（REPEATABLE READ）。专家用户可能会选择READ COMMITTED级别，因为他们通过OLTP处理或在数据仓库操作期间突破了可伸缩性的边界，在这些操作中，微小的不一致不会影响大量数据的合计结果。边缘上的级别（可序列化和未提交读）将处理行为更改为很少使用的程度。

另请参见ACID，OLTP，已提交读，未提交读，可重复读，可序列化事务。

 

dirty read

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_dirty_read

An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed. It is only possible with the isolation level known as read uncommitted.

This kind of operation does not adhere to the ACID principle of database design. It is considered very risky, because the data could be rolled back, or updated further before being committed; then, the transaction doing the dirty read would be using data that was never confirmed as accurate.

Its opposite is consistent read, where InnoDB ensures that a transaction does not read information updated by another transaction, even if the other transaction commits in the meantime.

一个操作（事务）读到了其它事务修改了但是还没有提交的数据。

consistent read

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_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.

一致性读，通过快照技术确保读到的是已提交的数据。如果已经有其它事务对数据做了修改，那么会通过undo log还原回最近一次提交的数据来做数据快照。

REPEATABLE READ级别，事务开始前做一个快照。可以确保整个事务过程中数据不变，多次重复读也是一样的数据。

READ COMMITTED级别，每一次读操作之前做快照。事务中假如有多次读，那么可能会读到不一样的数据，后一次读到的是最新的数据。

重复读提高读效率的原理：

没有锁定表，只是做了快照。因此其它事务不用等待锁，可以直接操作表。

 

phantom

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_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.

事务A两次读（查询）过程中，另一个事务插入了一条新数据，导致事务A两次读的结果集数量不同。