官方文档翻译-innodb transaction model

官方文档 innodb-transaction-model-8.0

目录

事务隔离级别

事务隔离是数据库处理的基础之一.事务蛤蜊(isolation)是ACID中的I.隔离级别是在多个事务同时进行更新和查询时,对结果的性能、可靠性、一致性、可重复性之间平衡微调的设置(即根据业务选择不同的隔离级别)。

innodb根据1992年的sql标准提供了4个隔离级别:未提交读、提交都、可重复读、串行化.默认隔离级别是可重复读

用户通过set transaction设置隔离级别.通过–transaction-isolation=level在服务端为所有连接更改默认隔离级别

innodb为支持不同的事务隔离级别使用不同的锁策略.在操作核心数据对ACID要求较高时,可以使用默认的可重复读隔离级别获得高度得一致性.在批量处理下,精确的一致性和可重复的结果不如最小化锁开销重要,也可使用提交读或未提交读.串行行主要用于特殊情况,如XA事务,以及并发和死锁问题的排查.

对于锁定读(select … for update/share,update,delete)语句,锁依赖语句是否使用唯一索引和一个唯一搜索条件,或者范围搜索

  • 具有唯一搜索条件的唯一索引,innodb锁定找到的索引记录,而不锁定其前的间隙
  • 其他搜索条件,innodb锁定扫描到的范围,使用间隙锁或next-key锁去阻塞其他会话的插入该范围的间隙.关于锁的更多信息Section 15.7.1, “InnoDB Locking”

可重复读

innodb的默认隔离级别.

在同一个事务内的一致性读操作读取第一次读建立的快照。意思是在同一事务内发起了一些非锁select语句,这些select语句彼此一致.参见Section 15.7.2.3, “Consistent Nonlocking Reads”.(一致性非锁读)

读提交

即使在同一事物的一致性非锁读操作，都会更新设置数据库快照,关于更多的一致性读，参照一致性非锁读。

关于上锁select（select…for share/update),update语句，innodb仅仅锁住行，而不会锁住他们的间隙，允许自由的插入新的记录到间隙。间隙锁仅仅用于外键一致性和重复索引检查。

由于禁用了间隙锁，将因此会由于其他事务插入到间隙中产生幻读问题。关于幻读，参见Section 15.7.4, “Phantom Rows”.

readCommited只支持基于行的binlog，如果设置binlog format=MIXED，那么服务器自动使用基于行的日志记录。

使用读提交的其他影响：

  • 对于update和delete语句，innodb只持有更新删除的行锁。where语句未包含的记录所将在mysql评估条件后被释放。大大减少了死锁发生的可能性（还是有可能发生）
  • 对于update语句，如果一行已经被锁，innodb执行semi-consitent读，返回mysql最后提交的版本，以便mysql确定该行是否匹配语句中的where条件。如果行匹配，mysql再次读取该行，并且innodb锁定他或等等锁释放。

example：

1
2
3
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;

在这个例子中，表重没有索引，因此搜索和索引扫描使用隐藏的聚集索引来记录锁而非索引列（参见( Section 15.6.2.1, “Clustered and Secondary Indexes”)[https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html]）

假设某个会话使用如下语句:

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

第二个会话在第一个会话之后执行以下语句:

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

inndobb每次执行update时，他首先获取每行的x锁，然后决定是否修改，如果innodb不修改该行，则释放该行的锁。否则，innodb保留锁直到事务结束。其对事务过程情况如下：

使用默认的repeatable read隔离级别时，第一次update申请获取读到的每行的x锁，别难过且不会释放锁：

1
2
3
4
5
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

第二会话的更新将会阻塞直到申请获得到锁（第一个会话的更新保留了所有行的锁）并且直到事务提交或者回滚才会继续进行。

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

如果改用read commited，第一个事务的更新申请x锁，会释放不需要修改的行的锁

1
2
3
4
5
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)

第二个事务的更新，innodb使用semi-consistent读，将它读取的每一行的最新提交版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配：

1
2
3
4
5
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在更新相同记录时尝试获取锁时被阻塞

1
2
3
4
5
6
7
8
9
10
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;

读提交隔离级别可以在每次启动或者运行时设置。运行时可以为所有会话设置，也可以为单独的会话设置。

读未提交

查询语句以非锁形式执行,但是可能获取早期的行.使用这种隔离级别的读,不能保证一致性, 会导致脏读.

串行化

这种隔离级别像可重复读,但是当禁用自动提交时,innodb隐式地转换了所有sellect语句为select…for share.如果开启了自动提交,select是它自己的事务,它被认为是只读,并且如果以一致性非锁读执行也不需要阻塞其他事务就可以序列化(如果其他事务已修改选定的行, 则强制普通select组织,请禁用自动提交.)

note

As of MySQL 8.0.22, DML operations that read data from MySQL grant tables (through a join list or subquery) but do not modify them do not acquire read locks on the MySQL grant tables, regardless of the isolation level. For more information, see Grant Table Concurrency.

一致性非锁读

一致性非锁读(consistent nonlocking reads)

rp和rc的隔离级别下,一致性读是innodb的默认模式,一致性读不会加锁,不影响其他事物修改数据.

一致性读(consistent read)是指innodb使用多版本查询来读取数据库在某个时间点的快照.查询的是事务提交前时间点的变化,不会被之后提交或未提交的事务影响.不过对于此规则的例外情况为同一事务的早期更改.这个例外会引起如下问题:如果更新a表的某些行,查询语句将获取更新行的最新版本,但会看到其他行的老版本.如果其他会话同时更新该表,则可能会看到该表处于数据库处于未存在过的状态(…).

  • 在可重复读的隔离级别下,同一个事务的一致性读都是该事务第一次查询建立的快照.
  • 在已提交读的隔离级别下,事务每次一致性读都会设置并使用最新的快照.

设想正在允许一个默认隔离级别(可重复读)的数据库,当你发出一致性读(普通select语句),innodb给了你(事务)一个根据这个sql语句查询看到的数据库设置的一个时间点.如果另外一个事务在这个时间点之后删除了其中的一些行且提交,你不会看到这些行已经被删除了(更新和插入也是一样).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 如果数据库快照适用于事务的select语句,不适用DML(data manipulation language)语句.
-- 事务A中插入或者修改一些行,然后提交事务,
-- 另一个并发的可重复读事务提交删除或者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.

更新快照的方式即为:1. 提交当前事务, 2. 使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 开启事务

可以通过提交事务然后在执行其他语句(select 或者 START TRANSACTION WITH CONSISTENT SNAPSHOT)更新快照.

这就是多版本控制(multi-versioned concurrency control)

在下面的例子中,只有在b提交插入语句并且A也已经提交的事务的时候 ,会话A看到b刚刚插入的行,由于快照生成的时间点在b提交之前

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
time        session A           sessionB
  |       SET commit=0        SET commit=0
  |      select * from t;
  |      empty set
  |                           insert into t values(1,2);
  |      select * from t;
  |      empty set
  |                           commit;
  |      select * from t;
  |      empty set
  |      commit;
  |      select * from t;
  |      ----------------
  |     |   1   |   2   |
  ↓

如果想要看到最新的快照,使用读已提交隔离级别或者锁读

1
select * from t for SHARE;

在一个事务的提交读隔离级别中,每次一致性读设置并且读取自己的最新快照.使用读锁,一个select语句将发生阻塞直到包含查询行中最新状态的事务结束

一致性读不适用某些ddl语句:

  1. 不适用drop table,mysql无法使用已经被mysql删除的表
  2. 不适用alter table,alter语句基于原始表的一个临时复制,当发起一致性读时, 事务会返回个异常.ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.

某些查询语句无需加锁也能刷新快照.如: INSERT INTO … SELECT, UPDATE … (SELECT), and CREATE TABLE … SELECT

  • 默认情况下,innodb回为这些语句设置更强的锁,并且selct部分类似read commited,即使在同一个事务,每次一致性读,也会设置并且读取自己的最新快照
  • 在这些情况下执行非锁读,可以使用读未提交或已提交读,来避免为表中查询到的行上锁

autocommit,commit and rollback

innodb中所有的用户活动都在一个事务中。如果使用自动提交模式，每个sql语句的形式都是一个事务。mysql默认为每个会话开启自动提交。如果语句没有报错，则自动提交，如果报错，根据具体内容会自动提交或者回滚，参见section 15.21.4,”innodb error handling”

开启了自动提交的会话也可以使用显示开启事务来执行多个语句，(Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”)[https://dev.mysql.com/doc/refman/8.0/en/commit.html]

如果使用set autocommit = 0禁用自动提交,会话仍然会开启事务,当前事务的结尾使用commitorrollback然后开启新事务.

如果会话禁用了自动提交，并且再事务的结尾也没有显示提交事务，那么mysql会回滚这个事务。

有一些语句会隐式的结束事务，就如同执行那条语句前提交（commit）了一样。参见 Section 13.3.3, “Statements That Cause an Implicit Commit”.

  • Data definition language (DDL) statements that define or modify database objects
  • Statements that implicitly use or modify tables in the mysql database.
  • Transaction-control and locking statements.
  • Data loading statements.
  • Administrative statements.
  • Replication control statements.

commit意味着当前事务做出的改变，将对其他事务可见，rollback意味着当前事务做出的更改，将全部取消。commit与rollback都会释放当前事务获得到的锁。

组dml操作

默认情况下，mysql server启用自动提交，当你执行时，会自动提交sql语句，如果你有使用其他数据库的惊艳，可能会对这种方式感到陌生。再其他数据库系统中，标准做法是发出一系列dml语句，然后提交或者回滚

使用多个事务语句，使用set autocommit=0关闭自动提交,然后在事务的结尾使用恰当的commit或者rollback语句结束事务.要使autocommit为开启状态,可以使用start transaction开启事务,然后使用commit或者rollback结束事务.下面举例,第一个为commit,第二个为rollback

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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中，可以将事务控制语句(如COMMIT)作为字符串发送到mysql server，就像任何其他SQL语句(如SELECT或INSERT)一样。一些api还提供单独的特殊事务提交和回滚函数或方法。

上锁读

在同一个事务中，查询数据，然后插入或者更新相关数据，常规得select语句不能提供足够得保护。其他事务能够更新或者删除查询导得行。innodb支持两种上锁读的方式提供额外的安全：

select…for share

在读到得行增加共享锁，其他事务可以读取该行，但是直到你得事务提交前，不能对其修改。如果该行正在被其他事物修改，那么你的语句将被阻塞到该事务提交然后使用最新的值。

select...for share是替select...lock in share mode的一种替换方式.lock in share mode向前兼容.语句是等价的.for share支持 of table_name,NOWAIT和SKIP LOCKED,参见后文

权限：select...for share在8.0.22之前的版本,不仅需要select权限,还至少需要delete,lock tables or update 之一.

select…for update

对于查询涉及的索引记录.锁住相应行和索引,效果和使用update语句一样.如果其他事务中需要对这些行的更新,或者执行select…for share,或者使用某些事务隔离级别读取数据,都会被阻塞.一致性读会忽略读视图(read-view)中的锁.(记录的旧版本数据不能被锁定；它们通过记录在内存中的拷贝，加上回滚日志进行重构。)

权限: select…for update不仅需要select权限,还至少需要delete,lock tables or update 之一.

这些子句主要用于处理树结构或者图结构的数据.可以是单表或跨多个表的存储的数据.可通过图的边界或者树的分支从一个位置到另一个为你之.同时反向移动并移动修改这些指针数据(ps:没看懂 = =)

事务提交时,将会释放由for share和for update查询设置的锁.

注意:苏定读只有在设置了禁止自动提交时才有用(使用 start transaction开始一个事务,或者将 autocommit 设置为0 )

外部查询中的锁定读子句不会锁住子查询中的数据,除非在子查询中也指定了锁读.

1
2
3
4
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;


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

locking read examiples

example1

假设你想要在child表中增加一条记录,并且需要确保parent表中,存在对应的父级数据,应用程序可以通过以下系列操作确保参照完整性。首先执行一个一致性读,查询parent表并验证存在相应的父级数据行.然后可以安全地在child表中增加记录吗?不可以,因为其他会话可能在你的select语句和insert语句之间删除记录.而你不知道.为了避免这个问题,可以使用select…for share 语句

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

在 FOR SHARE 语句返回父记录 ‘Jones’ 之后，你可以安全地在 CHILD 表中增加子记录，并且提交事务。任何相应获取 PARENT 表中该记录上的排他锁的事务都需要等待你的事务结束，也就是等待所有表中的数据到达一致性的状态。

example2

CHILD_CODES 表中存在一个整数计数器字段，用于为 CHILD 表中的每个子记录指定一个唯一标识符。不要使用一致性读或共享模式读的方式获取计数器的当前值，因为两个用户可能同时读取了相同的值，如果两个事务使用相同的标识符为 CHILD 表增加数据，将会产生重复键值错误。

这种情况下，FOR SHARE 并不是一个好的解决方法，因为如果两个用户同时读取了计数器的值，至少有一个用户在更新计数器的时候会死锁。

为了实现计数器的读取和增长，首先使用带 FOR UPDATE 选项的锁定读取操作，然后再修改计数器的值。例如：

1
2
3

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 中，实际上可以通过一条简单的语句生成唯一的标识符：

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

SELECT 语句只是为了返回标识符的值（与当前会话相关的值）。它不会访问任何表。

locing read concurrency with nowait and skip locked

NOWAIT 和 SKIP LOCKED选项.

如果某行被某个事务锁住,访问该行的select...for share,select...for update事务必须等待其他事务释放该行上的锁.这种行为可以防止事务更新或者删除其他事务正在查询的行.不过如果在请求的数据行已经被锁定的情况下立即返回,或者可以只接受没有被锁定数据的行,就不需要一致等待释放锁.

NOWAIT

使用NOWAIT选项的锁定读不会等待获取行锁,查询立即执行.如果请求的行被锁定,则返回一个错误信息

SKIP LOCKED

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

注意:跳过锁定行的查询返回数据的不一致性视图。因此，SKIP LOCKED 不适合用于常见的交易系统。但是，它可以用于避免多个会话访问同一个队列表时的锁竞争。

以下示例演示了NOWAIT和SKIP LOCKED选项的使用。
会话 1 开始了一个事务，获取了单个记录上的行锁。
会话 2 使用 NOWAIT 选项尝试针对同一条记录的带锁读取操作。由于请求的数据已经被会话 1 锁定，会话 2 中的锁定读取操作立即返回一个错误
。在会话 3中，使用锁定读取的 SKIP LOCKED 选项返回没有被会话 1 锁定的其他数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 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 |
+---+