SQL的四种隔离级别

SQL中的四种隔离级别

1. 读未提交（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.

• 这是在事务之间提供保护最少的隔离级别。查询采取了一种锁策略，允许其在通常需要等待其他事务的场景下也可以继续执行。但是，额外的性能收益也造成了不可信赖的结果，如读到了已经被其他事务修改，但是还未提交的数据（脏读）。使用此隔离级别是需要特别小心，注意结果可能存在不一致和不可复制的情况，这取决于其他事务同时在做什么操作。通常此隔离级别的事务只用于查询，不做插入，更新，删除操作。

2. 读已提交（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.

• 此隔离级别选择了牺牲事务之间的部分保护以提高性能的一种锁策略。事务无法访问其他事务还未提交的数据，但可以看到当前事务启动以后，其他事务提交的数据。因此，事务不会看到坏数据，但是事务看到的数据某种程度上取决于其他事务的时间点。
   
  当此隔离级别的事务做UPDATE … WHERE 或 DELETE … WHERE 操作时，其他的事务需要等待。事务在执行SELECT … FOR UPDATE 和 LOCK IN SHARE MODE 操作时，其他事务无需等待。
   
  在MySQL 8.0.1中SELECT … FOR SHARE 取代了 SELECT … LOCK IN SHARE MODE，但是依旧支持LOCK IN SHARE MODE以向后兼容。

3. 可重复读（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.

• MySQL（InnoDB）的默认隔离级别。此隔离级别不允许其他事务修改查询的任一行，因此，不存在不可重复读（non-repeatable reads），但是存在幻读（phantom reads）的情况。它使用适度严格的锁定策略，使得事务中的所有查询都能看到来自同一快照的数据，即事务启动时的数据。
   
  此隔离级别下的事务在执行 UPDATE … WHERE, DELETE … WHERE，SELECT … FOR UPDATE 和 LOCK IN SHARE MODE操作时，其他事务需要等待。
   
  在MySQL 8.0.1中SELECT … FOR SHARE 取代了 SELECT … LOCK IN SHARE MODE ，但保留了LOCK IN SHARE MODE以向后兼容。

4. 串行化（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.

• 此隔离级别采用最为保守的锁定策略，以阻止其他事务插入数据或修改该事务读取的数据，直到该事务完成为止。同一查询可以在事务中反复执行，确保每次检索到相同的结果集。事务启动后，任何其他事务提交的数据变更，均会导致该事务的等待。
   
  这是SQL标准的默认隔离级别。在实际运用当中，很少需要用到这么严格的隔离级别，所以MySQL（InnoDB ）采用了次一级的隔离策略，可重复度。

几个概念：
脏读（dirty read）：读取到了其他事务修改后，但未提交的数据。

不可重复读：一个事务中，后一次读取到的数据与前一次读取到的数据不同，即第一次读之后，第二次读之前，该数据被其他事务修改并提交，重复读取的数据不一致。

幻读（phantom read）：读取到的结果集中包含了在前一次查询中没有的数据。例如，一个事务中前后执行了两次查询，同时，另一个事务提交了满足查询语句WHERE条件的新行或修改，此时就存在前后两次读取不一致的情况。幻读比不可重复度更难以防止，锁定所有第一次读取的结果，并不能预防引起幻读的修改，如提交新的满足WHERE条件的数据。
关于幻读的官方说明：

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.

隔离级别	脏读	不可重复度	幻读	加锁读
READ UNCOMMITED	YES	YES	YES	NO
READ COMMITED	NO	YES	YES	NO
REPEATABLE READ	NO	NO	YES	NO
SERIALIZABLE	NO	NO	NO	YES