事务隔离级别

事务隔离是数据库处理的基础之一。隔离是I的首字母缩写ACID;
隔离级别是在多个事务进行更改并同时执行查询时，调节性能和可靠性，一致性和可重现性进行微调的设置。

InnoDB提供了SQL：1992标准描述的所有四个事务隔离级别：READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ和SERIALIZABLE。
InnoDB的默认隔离级别是REPEATABLE READ。用户可以使用SET TRANSACTION语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，请在命令行或选项文件中使用--transaction-isolation选项。

InnoDB使用不同的锁定策略支持此处描述的每个事务隔离级别。您可以使用默认的REPEATABLE READ级别强制执行高度一致性，以便对ACID合规性很重要的关键数据进行操作。或者您可以放松使用READ COMMITTED甚至READ UNCOMMITTED的一致性规则，例如批量报告，其中精确一致性和可重复结果不如最小化锁定开销量重要。SERIALIZABLE强制执行甚至比REPEATABLE READ更严格的规则，主要用于特殊情况，例如XA事务以及并发和死锁的故障排除问题

以下列表描述了MySQL如何支持不同的事务级别。
该列表从最常用的级别变为最少使用的级别。

REPEATABLE READ

这是InnoDB的默认隔离级别。
同一事务中的一致读取读取第一次读取建立的快照。
这意味着如果在同一事务中发出多个普通（非锁定）SELECT语句，则这些SELECT语句也相互一致。
对于锁定读取（使用FOR UPDATE或FOR SHARE的SELECT），UPDATE和DELETE语句，锁定取决于语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。
对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不是之前的间隙。
对于其他搜索条件，InnoDB锁定扫描的索引范围，使用间隙锁或下一键锁来阻止其他会话插入范围所覆盖的间隙。

READ COMMITTED

即使在同一事务中，每个一致的读取也会设置和读取自己的新快照。
对于锁定读取（使用FOR UPDATE或FOR SHARE的SELECT），UPDATE语句和DELETE语句，InnoDB仅锁定索引记录，而不锁定它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。

由于禁用了间隙锁定，因此可能会出现幻像问题，因为其他会话可以在间隙中插入新行。

READ COMMITTED隔离级别仅支持row-based binary logging。
如果对binlog_format = MIXED使用READ COMMITTED，则服务器会自动使用基于行的日志记录。

使用READ COMMITTED还有其他影响：
对于UPDATE或DELETE语句，InnoDB仅为其更新或删除的行保留锁定。MySQL评估WHERE条件后，将释放不匹配行的记录锁。这大大降低了死锁的可能性，但它们仍然可以发生。

对于UPDATE语句，如果一行已被锁定，InnoDB将执行“半一致”读取，将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。如果行匹配（必须更新），MySQL再次读取该行，这次InnoDB将其锁定或等待锁定。

READ UNCOMMITTED

SELECT语句以非锁定方式执行，但可能使用行的早期版本。因此，使用此隔离级别，此类读取不一致。
这也称为脏读。否则，此隔离级别与READ COMMITTED类似。

SERIALIZABLE

这个级别就像REPEATABLE READ，但InnoDB隐式地将所有普通SELECT语句转换为SELECT ... FOR SHARE如果禁用自动提交。如果启用了自动提交，则SELECT是其自己的事务。因此，已知它是只读的，并且如果作为一致（非锁定）读取执行则可以序列化，并且不需要阻止其他事务。（要强制普通SELECT阻止其他事务已修改所选行，请禁用自动提交。）

autocommit，Commit和Rollback

在InnoDB中，所有用户活动都发生在事务中。如果启用了自动提交模式，则每个SQL语句自己形成一个事务。默认情况下，MySQL为启用了自动提交的每个新连接启动会话，因此如果该语句没有返回错误，MySQL会在每个SQL语句后执行提交。如果语句返回错误，则提交或回滚行为取决于错误。

启用了自动提交的会话可以通过使用显式START TRANSACTION或BEGIN语句启动它并以COMMIT或ROLLBACK语句结束它来执行多语句事务。

如果在SET autocommit = 0的会话中禁用了自动提交模式，则会话始终打开一个事务。COMMIT或ROLLBACK语句结束当前事务并启动新事务。
如果已禁用自动提交的会话在没有显式提交最终事务的情况下结束，则MySQL将回滚该事务。
某些语句隐式结束事务，就像您在执行语句之前完成了一个COMMIT一样。

COMMIT表示当前事务中所做的更改是永久性的，并且对其他会话可见。另一方面，ROLLBACK语句取消当前事务所做的所有修改。COMMIT和ROLLBACK都释放在当前事务期间设置的所有InnoDB锁

使用事务对DML操作进行分组
默认情况下，与MySQL服务器的连接始于启用自动提交模式，该模式会在您执行时自动提交每个SQL语句。如果您具有其他数据库系统的经验，则可能不熟悉此操作模式，其中标准做法是发出一系列DML语句并将它们提交或一起回滚。

要使用多语句事务，请使用SQL语句SET autocommit = 0切换autocommit off，并根据需要使用COMMIT或ROLLBACK结束每个事务。
要保留自动提交启用，请使用START TRANSACTION开始每个事务，并使用COMMIT或ROLLBACK结束它。

一致的非锁定读取

一致的读取意味着InnoDB使用多版本控制向查询提供在某个时间点数据库的快照。查询将查看在该时间点之前提交的事务所做的更改，并且不会对以后或未提交的事务所做的更改进行更改。此规则的例外是查询查看同一事务中早期语句所做的更改。此异常导致以下异常：如果更新表中的某些行，SELECT将查看更新行的最新版本，但它也可能会看到任何行的旧版本。如果其他会话同时更新同一个表，则异常意味着您可能会看到该表处于从未存在于数据库中的状态。

如果事务隔离级别是REPEATABLE READ（默认级别），则同一事务中的所有一致性读取将读取该事务中第一次此类读取所建立的快照。您可以通过提交当前事务并在发出新查询之后为查询获取更新的快照。

使用READ COMMITTED隔离级别，事务中的每个一致读取都会设置并读取其自己的新快照。

一致性读取是InnoDB在READ COMMITTED和REPEATABLE READ隔离级别中处理SELECT语句的默认模式。一致读取不会对其访问的表设置任何锁定，因此其他会话可以在对表执行一致读取的同时自由修改这些表。

假设您正在以默认的REPEATABLE READ隔离级别运行。当您发出一致读取（即普通SELECT语句）时，InnoDB会为您的事务提供一个时间点，您的查询将根据该时间点查看数据库。如果另一个事务删除了一行并在分配了您的时间点后提交，则您不会将该行视为已删除。插入和更新的处理方式类似。

数据库状态的快照适用于事务中的SELECT语句，不一定适用于DML语句。如果插入或修改某些行然后提交该事务，则从另一个并发REPEATABLE READ事务发出的DELETE或UPDATE语句可能会影响那些刚刚提交的行，即使会话无法查询它们。如果事务确实更新或删除了由其他事务提交的行，则这些更改将对当前事务可见。

您可以通过提交事务然后再执行另一个SELECT或START WITH ACACENT WITH SNAPSHOT来提高您的时间点。

如果要查看数据库的“最新”状态，请使用READ COMMITTED隔离级别或锁定读取：

使用READ COMMITTED隔离级别，事务中的每个一致读取都会设置并读取其自己的新快照。
对于FOR SHARE，会发生锁定读取：SELECT阻塞，直到包含最新行的事务结束
一致的读取对某些DDL语句不起作用：
一致读取不适用于DROP TABLE，因为MySQL无法使用已删除的表并且InnoDB会破坏该表。
一致性读取不适用于ALTER TABLE，因为该语句生成原始表的临时副本，并在构建临时副本时删除原始表。在事务中重新发出一致读取时，新表中的行不可见，因为在执行事务快照时这些行不存在。在这种情况下，事务返回错误：ER_TABLE_DEF_CHANGED，“表定义已更改，请重试事务”。

读取类型因INSERT INTO ... SELECT，UPDATE ...（SELECT）和CREATE TABLE ...SELECT等子句中的选择而异，不指定FOR UPDATE或FOR SHARE：
默认情况下，InnoDB使用更强的锁定，SELECT部分​​的作用类似于READ COMMITTED，即使在同一事务中，每个一致的读取也会设置和读取自己的新快照。

要在这种情况下使用一致读取，请将事务的隔离级别设置为READ UNCOMMITTED，READ COMMITTED或REPEATABLE READ（即SERIALIZABLE以外的任何其他内容）。
在这种情况下，不会对从所选表中读取的行设置锁定。

锁定读取

如果查询数据然后在同一事务中插入或更新相关数据，则常规SELECT语句不会提供足够的保护。其他事务可以更新或删除您刚查询的相同行。InnoDB支持两种类型的锁定读取，提供额外的安全性：
SELECT ... FOR SHARE
在读取的任何行上设置共享模式锁定。其他会话可以读取行，但在事务提交之前无法修改它们。如果其中任何行已被另一个尚未提交的事务更改，则查询将等待该事务结束，然后使用最新值。
SELECT ... FOR SHARE是SELECT ... LOCK IN SHARE MODE的替代品，但LOCK IN SHARE MODE仍可用于向后兼容。这些语句是等同的。但是，FOR SHARE支持 table_name，NOWAIT和SKIP LOCKED选项。

SELECT ... FOR UPDATE
对于搜索遇到的索引记录，锁定行和任何关联的索引条目，就像为这些行发出UPDATE语句一样。阻止其他事务更新这些行，从执行SELECT ... FOR SHARE，或从某些事务隔离级别读取数据。一致性读取将忽略在读取视图中存在的记录上设置的任何锁定（旧版本的记录无法锁定;它们通过在记录的内存副本上应用撤消日志来重建。）

这些子句在处理树形结构或图形结构数据时非常有用，无论是在单个表中还是分到多个表中。
您将边缘或树枝从一个地方遍历到另一个地方，同时保留返回并更改任何这些“指针”值的权限。

在提交或回滚事务时，将释放由FOR SHARE和FOR UPDATE查询设置的所有锁。

只有在禁用自动提交时（通过使用START TRANSACTION开始事务或将自动提交设置为0），才能锁定读取。

除非在子查询中指定了锁定读取子句，否则外部语句中的锁定读取子句不会锁定嵌套子查询中的表行。

使用NOWAIT和SKIP LOCKED锁定读取并发

如果行被事务锁定，则请求相同锁定行的SELECT ... FOR UPDATE或SELECT ... FOR SHARE事务必须等到阻塞事务释放行锁。此行为可防止事务更新或删除由其他事务查询以进行更新的行。但是，如果您希望在请求的行被锁定时立即返回查询，或者从结果集中排除锁定的行是可接受的，则无需等待释放行锁定。
为了避免等待其他事务释放行锁，NOWAIT和SKIP LOCKED选项可以与SELECT ... FOR UPDATE或SELECT ... FOR SHARE锁定读取语句一起使用。

NOWAIT
使用NOWAIT的锁定读取永远不会等待获取行锁定。查询立即执行，如果请求的行被锁定则失败并显示错误。

SKIP LOCKED
使用SKIP LOCKED的锁定读取永远不会等待获取行锁定。查询立即执行，从结果集中删除锁定的行。
SKIP LOCKED查询会返回数据的不一致视图。因此，SKIP LOCKED不适用于一般事务工作。
但是，当多个会话访问同一个类似队列的表时，它可用于避免锁争用。

NOWAIT和SKIP LOCKED仅适用于行级锁。
使用NOWAIT或SKIP LOCKED的语句对于 statement based replication是不安全的。