MySQL Glossary - Transaction

MySQL官方文档 事务相关

你好！ 这算是我的第一篇博客，之前一直有用印象笔记做记录，今年(2020)秃然有写博客的想法，并立了一周一篇(平均)的flag。主要是想坚守opensource的原则，更多地技术分享与交流。
关于平台，CSDN + AdBlock 应该是我最好的选择，站点知名度高，搜索排名靠前。
最近在复习数据库相关知识，就写了这篇MySQL官方文档 事务相关博客

事务基础概念

事务(Transaction)：事务是提交操作或回滚操作的原子单位。当一个事务包含多条操作，要么在提交事务后所有操作都成功，要么在回滚事务时所有操作都撤销。
事务(Transaction)：是访问和更新数据库的程序执行单元；事务中可能包含一个或多个 sql 语句，这些语句要么都执行，要么都不执行。
由InnoDB实现的数据库事务具有原子性，一致性，隔离性和持久性（首字母缩写为ACID）统称为属性。
如下官方解释：

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.

ACID特性

ACID是事务的四个基本特性：

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

ACID代表Atomicity(原子性)，Consistency(一致性)，Isolation(隔离性)和Durability(持久性)的首字母缩写。
这些属性都是数据库系统所想要的，并且都与事务紧密相关。且具有事务特性的InnoDB存储引擎遵循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.

原子性（Atomicity）

事务是提交操作或回滚操作的原子单位。当一个事务包含多条操作，要么在提交事务后所有操作都成功，要么在回滚事务时所有操作都撤销。
如下官方解释：

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.

一致性（Consistency）

在每次提交或回滚之后以及正处于事务处理期间，数据库始终保持一致性状态。如果跨多个表更新了相关数据，则查询将看到所有旧值或所有新值，不可能存在既有新知又有新值的情况。
如下官方解释：

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.

隔离性（Isolation）

事务在进行过程中相互隔离，他们互不干扰，也不能看到彼此的未提交数据。这种隔离是通过锁机制实现的。有经验的用户可以在确保事务互不干扰的情况下，调整隔离级别，牺牲较少的隔离以提高性能和并发。
如下官方解释：

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.

持久性（Durability）

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

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.)

MySQL隔离级别

隔离级别(isolation level)是数据库处理的基础之一。隔离是缩写ACID中的I。隔离级别是一种平衡性能与可靠性的设置。
隔离级别由高到低为SERIALIZABLE, REPEATABLE READ , READ COMMITTED, READ UNCOMMITTED。
InnoDB的默认隔离级别是REPEATABLE READ。
一般情况，SERIALIZABLE 和 READ UNCOMMITTED很少使用。

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.

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.

REPEATABLE READ

是InnoDB的默认隔离级别，当其他事务改变的时候它可以防止任一行被查询，因此可以防止不可重复读，但不能防止幻读。它使用适当严格的lock策略，这样，一个事务的所有查询都是从相同的数据快照中查的，也就是事务开始时的数据。
如下官方解释：

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.

SERIALIZABLE

隔离级别使用最保守的lock策略，该级别可以避免“脏读”、“不可重复读”和“幻读”。代价是处理事务的吞吐量低，严重浪费数据库的性能。
这是SQL标准指定的默认隔离级别。
实际上，很少需要这种严格程度，因此InnoDB的默认隔离级别是REPEATABLE READ。
如下官方解释：

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.