Mysql InnoDB事务模型(官方文档)

linqiw
993 阅读15分钟

         Mysql InnoDB事务模型包括事务的隔离级别、autocommit, Commit, and Rollback、一致非锁定读取、锁定读取。事务的隔离级别可以看下另一篇juejin.cn/post/684490…，这篇会对其他的三个官方文档进行翻译，写的不对的地方欢迎帮忙指正。

一、autocommit, Commit, and Rollback

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

         启用autocommit的会话可以通过使用显式START transaction或BEGIN语句启动多语句事务，并使用COMMIT或ROLLBACK语句结束它来执行多语句事务。

         如果在设置为autocommit=0的会话中禁用autocommit模式，则该会话始终有一个打开的事务。COMMIT或ROLLBACK语句结束当前事务并启动新事务。

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

         有些语句隐式地结束事务，就好像在执行语句之前执行了提交一样。详细信息在"导致隐式提交的语句"，这个会有一篇进行分析。

          提交意味着当前事务中所做的更改将成为永久性的，并对其他会话可见。另一方面，ROLLBACK语句取消当前事务所做的所有修改。COMMIT和ROLLBACK都释放当前事务期间设置的所有InnoDB锁。

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

          若要使用多个语句事务，请关闭autocommit，并将SQL语句设置为autocommit=0，然后根据需要使用COMMIT或ROLLBACK结束每个事务。要使autocommit保持打开状态，请使用START transaction开始每个事务，并使用COMMIT或ROLLBACK结束它。下面的示例显示了两个事务。第一个被提交；第二个被回滚。

shell> 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>

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

二、一致非锁定读取

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

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

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

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

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

           数据库状态的快照应用于事务中的SELECT语句，而不一定应用于DML语句。如果插入或修改某些行，然后提交该事务，则从另一个并发可重复读取事务发出的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事务来提前您的时间点。 这称为多版本并发控制。 在下面的示例中，会话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都会新建新的快照视图)隔离级别或锁定读取：  

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, 或 REPEATABLE READ（即，除SERIALIZABLE之外的任何内容）。在这种情况下，不会对从选定表中读取的行设置锁。

三、锁定读取

        如果查询数据，然后在同一事务中插入或更新相关数据，常规的SELECT语句不会提供足够的保护。其他事务可以更新或删除刚才查询的同一行。InnoDB支持两种提供额外安全性的锁定读取：

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

          注意
SELECT ... FOR SHARE是SELECT ... LOCK IN SHARE MODE的替代品，
但LOCK IN SHARE MODE保持向后兼容，后续还是版本还是可以用。但是FOR SHARE
对于表名、NOWAIT和SKIP LOCKED选项的共享支持。
用NOWAIT和SKIP LOCKED锁定读并发，下面会进行介绍

    • SELECT ... FOR UPDATE
      • 对于搜索遇到的索引记录，锁定行和任何关联的索引项（比如条件是使用普通索引，但是需要回表，主键索引也会加上锁），就像对这些行发出UPDATE语句一样。
      • 其他事务无法更新这些行，无法执行SELECT ... FOR SHARE，或者从某些事务隔离级别读取数据。一致读取忽略在读取视图中存在的记录上设置的任何锁。（旧版本的记录无法锁定；它们是通过对记录的内存副本应用undo日志来重建的）。 

这些子句主要用于处理树结构或图结构数据（在单个表中或在多个表中拆分）。您可以从一个地方到另一个地方遍历边或树枝，同时保留返回并更改这些“指针”值的权利。

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

    • 注意
只有当autocommit被禁用时（通过使用START transaction开始事务
或通过将autocommit设置为0），才能进行锁定读取。

除非在子查询中也指定了locking read子句，否则外部语句中的locking read子句不会锁定嵌套子查询中表的行。例如，下面的语句不锁定表t2中的行。 

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

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

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

假设要将新行插入表child，并确保子行在表parent中有父行。应用程序代码可以确保整个操作序列的引用完整性。 首先，使用一致读取查询表parent并验证父行是否存在。您能安全地将子行插入到表child吗？不，因为在您的选择和插入之间的某个其他会话可能会删除父行，而您不会意识到这一点。 要避免此潜在问题，请执行选择用于共享：

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

在FOR SHARE查询返回父“Jones”之后，可以安全地将子记录添加到child表并提交事务。任何试图在父表中的适用行中获取排他锁的事务都将等待您完成，即直到所有表中的数据处于一致状态。

例如，考虑表CHILD_CODES中的整数计数器字段，用于为添加到表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读取最新的可用数据，在读取的每一行上设置独占锁。因此，它设置的锁与搜索的SQL更新在行上设置的锁相同。

前面的描述只是一个例子，说明如何SELECT ... FOR UPDATE工作，在MySQL中，生成唯一标识符的特定任务实际上可以只使用对表的一次访问来完成：

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

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

如果行被事务锁定，SELECT ... FOR UPDATE 或者 SELECT ... FOR SHARE对于请求同一锁定行的共享事务，必须阻塞等到事务释放行锁 。此行为防止事务更新或删除由其他事务查询更新的行。但是，如果希望查询在请求的行被锁定时立即返回，或者可以从结果集中排除锁定的行，则不必等待行锁被释放。

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

NOWAIT

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

SKIP LOCKED

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

NOWAIT和SKIP LOCKED只适用于行级锁。 使用NOWAIT或SKIP LOCKED的语句对于基于语句的复制是不安全的。 下面的示例演示NOWAIT和SKIP LOCKED。会话1启动对单个记录进行行锁定的事务。会话2尝试使用NOWAIT选项对同一记录进行锁定读取。由于请求的行被会话1锁定，因此锁定读取立即返回并返回错误。在会话3中，带SKIP LOCKED的locking read返回请求的行，会话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 |
+---+

四、InnoDB错误处理

       以下项目描述了InnoDB如何执行错误处理。InnoDB有时只回滚失败的语句，有时回滚整个事务。

    • 如果表空间中的文件空间用完，就会出现MySQL Table is full错误，InnoDB会回滚SQL语句。 
    • 事务死锁导致InnoDB回滚整个事务。发生这种情况时重试整个事务。 
      • 锁等待超时导致InnoDB只回滚等待锁并遇到超时的单个语句。（要回滚整个事务，启动服务器使用--innodb-rollback-on-timeout 选项。）如果使用当前行为，请重试该语句；如果使用--innodb-rollback-on-timeout ，请重试整个事务。
      • 在繁忙的服务器上，死锁和锁等待超时都是正常的，应用程序必须意识到它们可能会发生，并通过重试来处理它们。您可以通过在事务期间对数据的第一次更改和提交之间尽可能少地执行操作来降低发生这种情况的可能性，从而使锁保持尽可能短的时间和尽可能少的行数。有时，在不同的事务之间分割工作可能是实用和有用的。
      • 当事务回滚由于死锁或锁等待超时而发生时，它将取消事务内语句的效果。但是，如果start transaction语句是start transaction或BEGIN语句，则rollback不会取消该语句。在COMMIT、ROLLBACK或导致隐式提交的某些SQL语句出现之前，其他SQL语句将成为事务的一部分。 
      • 如果未在语句中指定IGNORE选项，则重复键错误将回滚SQL语句。 
      • 行太长错误将回滚SQL语句。    

其他错误主要由MySQL代码层（InnoDB存储引擎级别之上）检测，并回滚相应的SQL语句。在单个SQL语句的回滚中不会释放锁。 

 在隐式回滚和显式回滚SQL语句执行期间，SHOW PROCESSLIST在相关连接的State列中显示回滚。

avatar
文章
阅读
粉丝