【MySQL精通之路】InnoDB(7)-锁和事务模型(2)-事务模型-易微帮

这意味着，如果在同一事务中发出多个纯（非锁定）SELECT语句，这些SELECT语句彼此之间也是一致的。参见第17.7.2.3节“一致非锁定读取”。
对于锁定读取（SELECT ... FOR UPDATE or FOR SHARE）、UPDATE和DELETE语句，锁取决于该语句是使用具有唯一搜索条件的唯一索引，还是使用范围类型的搜索条件。

2.1.1 唯一索引下的可重复读

对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定之前的间隙。

2.1.2 其他索引下的可重复读

对于其他搜索条件，InnoDB会锁定扫描索引范围，使用间隙锁或临键锁来阻止其他会话插入到范围覆盖的间隙中。有关间隙锁和临键锁的信息，请参阅“InnoDB锁”。

【MySQL精通之路】Innodb-锁和事务-CSDN博客

2.2 读已提交

即使在同一事务中，每次一致读取都会设置和读取自己的新快照。

有关一致读取的信息，请参阅“一致非锁定读取”。

对于锁定读取(SELECT with FOR UPDATE or FOR SHARE)、UPDATE语句和DELETE语句

InnoDB只锁定索引记录，而不锁定它们之前的间隙，因此允许在锁定行之后自由插入新记录。

间隙锁仅用于外键约束检查和重复key检查。

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

有关幻行的信息，请参阅“幻行”。

READ COMMITTED隔离级别仅支持基于行的binlog。

如果将READ COMMITTED与binlog_format=MIXED一起使用，则服务器会自动使用基于行的日志记录。

使用READ COMMITTED还有其他效果：

对于UPDATE或DELETE语句，InnoDB只为更新或删除的行保留锁。

MySQL评估WHERE条件后，将释放不匹配行的记录锁。这大大降低了死锁的概率，但它们仍然可能发生。

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

考虑以下示例，从下表开始：

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

在这种情况下，表没有索引，因此搜索和索引扫描使用隐藏的聚集索引来锁定记录

（请参见“聚集索引和辅助索引”），而不是索引列。

【MySQL精通之路】Innodb-索引-CSDN博客

假设一个会话使用以下语句执行UPDATE：

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

还假设第二个会话通过在第一个会话之后执行这些语句来执行UPDATE：

# Session B
UPDATE t SET b = 4 WHERE b = 2;

当InnoDB执行每个UPDATE时，它首先为每一行获取一个互斥锁，然后决定是否修改它。

如果InnoDB不修改行，它就会释放锁。否则，InnoDB将保留锁，直到事务结束。这会对事务处理产生以下影响。

当使用默认的REPEATABLE READ隔离级别时，第一个UPDATE会在它读取的每一行上获取一个x锁，并且不会释放任何一个：

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

第二个UPDATE在尝试获取任何锁时立即阻塞（因为第一个更新在所有行上都保留了锁），直到第一个UPDATE提交或回滚才继续：

x-lock(1,2); block and wait for first UPDATE to commit or roll back

如果改为使用READ COMMITTED，则第一个UPDATE会在其读取的每一行上获取一个x锁，并释放未修改的行的x锁：

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

对于第二个UPDATE，InnoDB进行“半一致”读取，将读取的每一行的最新提交版本返回给MySQL，以便MySQL确定该行是否符合UPDATE的WHERE条件：

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

但是，如果WHERE条件包括索引列，并且InnoDB使用索引，则在获取和保留记录锁时只考虑索引列。在下面的示例中，第一个UPDATE在b=2的每一行上获取并保留一个x锁。第二个UPDATE在尝试获取相同记录上的x锁时会阻塞，因为它还使用了在列b上定义的索引。

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

READ COMMITTED隔离级别可以在启动时设置，也可以在运行时更改。在运行时，它可以为所有会话全局设置，也可以为每个会话单独设置。

2.3 读取未提交

SELECT语句以无锁方式执行，但可能会使用行的早期版本。

因此，使用这种隔离级别，这样的读，多次读的数据是不一致的。这也被称为脏读。

此外，此隔离级别的工作方式与READ COMMITTED类似。

2.4 序列化读

这个级别类似于REPEATABLE READ

但InnoDB隐式地将所有纯SELECT语句转换为SELECT。。。FOR SHARE

（如果禁用了自动提交）。

如果启用了自动提交，SELECT就是它自己的事务。

因此，它是只读的，如果作为一致（非锁定）读取执行，则可以序列化，并且不需要为其他事务阻塞。（如果其他事务已经修改了所选行，要强制阻塞SELECT，请禁用自动提交。）

注意：

从MySQL 8.0.22开始，从MySQL 权限表读取数据（通过联表查询或子查询）但不修改数据的DML操作不会获取MySQL 权限表上的读锁，无论隔离级别如何。有关详细信息，请参阅权限表并发性。

2.自动提交、提交和回滚

在InnoDB中，所有用户活动都发生在事务内部。

如果启用了自动提交模式，则每条SQL语句将自己形成一个事务。

默认情况下，MySQL在启用自动提交的情况下为每个新连接启动会话，因此，如果每条SQL语句没有返回错误，MySQL会在该语句之后进行提交。

如果语句返回错误，则提交或回滚行为取决于错误。

参见“InnoDB错误处理”。

启用了自动提交的会话可以执行多语句事务，方法是以显式START TRANSACTION或BEGIN语句开始，然后以COMMIT或ROLLBACK语句结束。

参见“START TRANSACTION、COMMIT和ROLLBACK语句”。

如果在SET autocommit=0的会话中禁用了自动提交模式，则该会话始终打开一个事务。

COMMIT或ROLLBACK语句结束当前事务，并启动新事务。

如果禁用了自动提交的会话在没有显式提交最终事务的情况下结束，MySQL将回滚该事务。

有些语句隐式地结束事务，就好像在执行该语句之前执行了COMMIT一样。

有关详细信息，请参阅“导致隐式提交的声明”。

COMMIT意味着在当前事务中所做的更改是永久性的，并且对其他会话可见。

另一方面，ROLLBACK语句取消当前事务所做的所有修改。

COMMIT和ROLLBACK都会释放在当前事务期间设置的所有InnoDB锁。

Grouping DML Operations with Transactions

2.1 将DML操作以事务分组

默认情况下，与MySQL服务器的连接从启用自动提交模式开始，该模式会在您执行每一条SQL语句时自动提交。

如果您有其他数据库系统的经验，这种操作模式可能会不熟悉，因为在其他数据库系统中，标准做法是发布一系列DML语句，然后将它们一起提交或回滚。

要使用多个语句事务，请使用SQL语句SET autocommit=0关闭自动提交，并根据需要使用COMMIT或ROLLBACK结束每个事务。

若要保留自动提交，请以START TRANSACTION开始每个事务，然后以COMMIT或ROLLBACK结束。以下示例显示了两个事务。第一个是COMMIT；第二个ROLLBACK。

$> mysql test

mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do a transaction with autocommit turned on.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do another transaction with autocommit turned off.
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- Now we undo those last 2 inserts and the delete.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

2.2 客户端语言的事务

在PHP、PerlDBI、JDBC、ODBC等API或MySQL的标准C调用接口中，您可以像SELECT或INSERT等任何其他SQL语句一样，将COMMIT等事务控制语句作为字符串发送到MySQL服务器。一些API还提供单独的特殊事务提交和回滚功能或方法。

3.无锁快照读

快照读意味着InnoDB使用MVCC在某个时间设置数据库的快照。

此事务的查询会看到在此时间点之前提交的事务所做的更改，而不会看到以后或未提交的事务进行的更改。

此规则的例外情况是，查询会看到同一事务中前面的语句所做的更改。

此时会导致以下异常：

如果更新表中的某些行，SELECT会看到更新行的最新版本，但也可能会看到任何行的旧版本。

如果其他会话同时更新同一个表，则异常意味着您可能会看到该表从未存在过的状态。

PS：上面说的是读提交。

如果事务隔离级别为REPEATABLE READ（默认级别），则同一事务中的所有快照读都会读取该事务中第一次的快照读。通过提交当前事务，然后发出新的查询，可以为查询获取更新的快照。

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

快照读是InnoDB在 READ COMMITTED和 REPEATABLE READ隔离级别中处理SELECT语句的默认模式。

一致性读取不会对其访问的表设置任何锁，因此，在对表执行一致性读取的同时，其他会话可以自由修改这些表。

假设您在默认的REPEATABLE READ隔离级别下运行。当您发出快照读（即普通的SELECT语句）时，InnoDB会为您的事务提供一个时间点，根据该时间点您的查询可以看到数据库。

如果另一个事务删除了一行并在指定时间点后提交，则不会认为该行已被删除。插入和更新的处理方式类似。

注意：

数据库状态的快照应用于事务中的SELECT语句，而不一定应用于DML语句。

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

例如，您可能会遇到以下情况：
SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

您可以通过执行另一个SELECT或START TRANSACTION WITH CONSISTENT SNAPSHOT且提交事务来提前时间点。

这被称为多版本并发控制。

在下面的示例中，会话A只有在B提交了插入并且A也提交了插入时才看到由B插入的行，因此时间点提前到B的提交之后。

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

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

SELECT * FROM t FOR SHARE;

使用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，以避免设置行锁。

4.锁读取

如果查询数据时在同一事务中插入或更新相关数据，则常规SELECT语句无法提供足够的保护。

其他事务可以更新或删除您刚才查询的同一行。

InnoDB支持两种类型的锁定读取，提供额外的安全性：

4.1 SELECT ... FOR SHARE

在读取的任何行上设置共享模式锁定。其他会话可以读取这些行，但在事务提交之前不能修改它们。如果这些行中的任何一行被另一个尚未提交的事务更改，则查询将等待该事务结束，然后使用最新的值。

注意：

SELECT ... FOR SHARE 是 SELECT ... LOCK IN SHARE MODE的替代品。但LOCK IN SHARE MODE任然可用。这些语句是等效的。

但是，FOR SHARE支持OF table_name、NOWAIT和SKIP LOCKED选项。

请参阅

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

MySQL 8.0.22之前，SELECT。。。FOR SHARE需要SELECT权限和至少一个DELETE、LOCK TABLES或UPDATE权限。

从MySQL 8.0.22开始，只需要SELECT权限。

从MySQL 8.0.22，SELECT。。。FOR SHARE语句不会获取MySQL 权限表上的读锁。

有关详细信息，请参阅权限表并发性。

4.2 SELECT ... FOR UPDATE

对于查询到的行记录，会锁住行和任何关联的索引数据，就像为这些行发出UPDATE语句一样。

其他事务被阻止更新这些行、执行SELECT。。。FOR SHARE，或读取某些事务隔离级别的数据。

快照读将忽略在快照中记录上的任何锁。（记录的旧版本无法锁定；它们是通过在记录的内存副本上应用撤消日志来重建的。）

SELECT ... FOR UPDATE需要SELECT权限和至少一个DELETE、LOCK TABLES或UPDATE权限。

当事务被提交或回滚时，FOR SHARE和FOR UPDATE查询设置的所有锁都会被释放。

注意：

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

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

例如，以下语句不会锁定表t2中的行。

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

要锁定表t2中的行，请向子查询添加一个锁定读取子句：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

4.3 加锁读示例

假设要将新行插入到表的child表中，并确保该child行在parent表中具有父行。您的应用程序代码可以确保整个操作序列中的引用完整性。

首先，使用一快照读来查询表PARENT，并验证父行是否存在。您能安全地将子行插入到表child中吗？

不可以，因为其他会话可能会在SELECT和INSERT之间删除父行，而您不会意识到这一点。

要避免此潜在问题，请使用FOR SHARE执行查询：

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

在FOR SHARE查询返回父“Jones”之后，可以安全地将子记录添加到child表并提交事务。

任何试图在PARENT表中的对应行中获取独占锁的事务都会等待，直到完成，也就是说，直到所有表中的数据处于一致状态。

例如，考虑表CHILD_CODE中的一个整数计数器字段，该字段用于为添加到表CHILD的每个子项分配唯一标识符。不要使用一致读取或共享模式读取来读取计数器的当前值，因为数据库的两个用户可能会看到计数器的相同值，如果两个事务试图将具有相同标识符的行添加到CHILD表中，则会出现重复键错误。

这里，FOR SHARE不是一个好的解决方案，因为如果两个用户同时读取计数器，那么当它试图更新计数器时，至少其中一个用户会陷入死锁。

要实现计数器的读取和递增，首先使用FOR UPDATE执行计数器的锁定读取，然后递增计数器。例如：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE读取最新的可用数据，对它读取的每一行设置独占锁。因此，它设置了UPDATE语句将在行上设置的相同的锁。

前面的描述只是SELECT。。。FOR UPDATE有效。

在MySQL中，生成唯一标识符的具体任务实际上只需对表进行一次访问即可完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

SELECT语句仅检索标识符信息（特定于当前连接）。它不访问任何表。

5 使用NOWAIT和SKIP LOCKED实现加锁读并发

如果某一行被事务锁定，则SELECT ... FOR UPDATE 或SELECT ... FOR SHARE事务必须等待直到其他释放行锁。此行为防止其他更新或删除行事务。

但是，如果您希望查询到行被锁定时立即返回，或者如果可以从结果集中排除锁定的行，则无需等待行锁定被释放。

为了避免等待其他事务释放行锁，NOWAIT和SKIP LOCKED选项可以与SELECT ... FOR UPDATE 或SELECT ... FOR SHARE 加锁读语句一起使用。

NOWAIT

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

SKIP LOCKED

使用SKIP LOCKED的加锁读从不等待获取行锁。查询立即执行，从结果集中删除锁定的行。

NOWAIT和SKIP LOCKED仅适用于行级锁。

使用NOWAIT或SKIP LOCKED的语句对于基于语句的复制是不安全的。

以下示例演示NOWAIT和SKIP LOCKED。

会话1启动一个事务，该事务对单个记录加行锁。

会话2尝试使用NOWAIT选项对同一记录进行加锁读。

因为请求的行被会话1锁定，所以加锁读会立即返回并返回一个错误。

在会话3中，使用SKIP LOCKED进行的加锁读返回请求的行，但会话1锁定的行除外。

# Session 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# Session 3:

mysql> START TRANSACTION;

mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

【MySQL精通之路】InnoDB(7)-锁和事务模型(2)-事务模型

1.事务隔离级别

2.1 可重复读