innodb存储引擎锁的实现(一)

http://blog.itpub.net/28218939/viewspace-2158361/

 

| 概述

通常，我们在95%以上的MySQL使用场景中，从一定程度上来讲，就是在使用InnoDB存储引擎，很多时候我们选择使用InnoDB存储引擎的原因，就是因为它支持高并发，而高并发的实现很大程度上得益于细粒度的锁实现（行级锁），不仅如此，MySQL 还支持更多灵活多变的锁类型，例如：按照锁类型可分为共享锁(S锁)和排它锁(X锁)，按照锁范围可分为记录锁(record lock)、间隙锁(gap lock)、next-key lock。以及一些其他特殊用途的锁。这些锁在不同的隔离级别下存在着不同的表现形式。尤其在RR隔离级别下锁的类型最为丰富、灵活多变。下面我们将为大家一一介绍这些锁的含义和用途。 

PS：由于本文侧重点在于介绍锁类型，所以下文中的示例统一使用RR隔离级别进行演示，其他隔离级别下的锁表现形式如有兴趣请自行研究。

 

|  innodb 存储引擎行锁的算法

数据库对锁的使用是为了支持对共享资源进行并发的访问，提供数据的完整性和一致性。innodb存储引擎提供了表锁、行锁和意向锁用来实现事物在不同粒度上进行上锁，从而提高数据库的并发访问，并且保证数据的完整性和一致性。

innodb存储引擎的锁类型

innodb存储引擎是通过给索引上的索引项加锁来实现行锁，这种特点也就意味着，只要通过索引条件检索数据，innodb才会使用行级锁，否则会使用表锁。innodb存储引擎有以下锁类型：

• 1.共享锁和排他锁（Shared and Exclusive Locks）

• 2.意向锁（Intention Locks）

• 3.记录锁（Record Locks）

• 4.间隙锁（Gap Locks）

• 5.Next-Key Locks

• 6.插入意向锁（Insert Intention Locks）

• 7.自增锁（AUTO-INC Locks）

• 8.空间索引谓词锁（Predicate Locks for Spatial Indexes）

（1）共享锁和排他锁（Shared and Exclusive Locks）

• innodb实现标准行级锁，其中有两种类型的锁，共享锁（S）和独占锁（X）。

  • 1.共享锁，允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

  • 2.排它锁，允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集的共享读锁和排他锁。

• 如果事务T1持有一行记录的共享锁，那么另一个不同的事务T2对该行记录的锁定如下：

  • 1.如果事务T2对该行的请求是一个S锁，那么事务T1和事务T2可以共同对该行记录持有同一把S锁。

  • 2.如果事务T2对该行的请求是一个X锁，那么事务T2不可能马上获得对该行记录的X锁，必须要等到事务T1将该记录的S锁释放，才可以对该行记录持有X锁。

  • 3.如果事务T1持有第 r 行的独占（X）锁，那么对于事务T2对该行记录的任何一种请求的锁都不能立即授予。相反，事务T2必须要等到事务T1释放在r 行上的锁。

（2）意向锁（Intention Locks）

innodb存储引擎支持多种粒度锁，允许行锁和表锁共存。为了在多个粒度级别上进行锁定，innodb存储引擎使用意向锁来实现。意向锁是表级锁，它先指明了该事物是那种类型的锁（共享锁或者独占锁），然后去锁定表中某行记录。（我们可以在后面的Gap lock 和 Next-Key Locks 的演示中MySQL 8.0 的information_schema.data_locks 表中显示的信息看到） 

有两种类型的意向锁：

• 1.意向共享锁（IS），表明事务在一个表中的单个行上设置共享锁。

• 2.意向独占锁（IX），表明事务在表中的某行设置独占锁。 
  例如，SELECT … LOCK IN SHARE MODE 是IS，而 SELECT … FOR UPDATE 是IX锁。

• 意向锁的添加方式：

  • 1.在一个事务对一张表的某行添加S锁之前，它必须对该表获取一个IS锁或者优先级更高的锁。

  • 2.在一个事务对一张表的某行添加X锁之前，它必须对该表获取一个IX锁。

• 表级锁类型兼容性如下图所示： 

• 如果与现有锁相兼容，则授予事务请求的锁，但如果它与之冲突，则不会，并且该事务一直等待直到冲突的现有锁被释放。如果所请求的锁与持有的锁冲突是不可能被授予，因为这将会导致死锁，并且返回错误。

• 意向锁不会阻塞任何请求，除非将这个表锁住，例如，LOCK TABLE …. WRITE。意向锁的主要目的是显示某人正在锁定一行，或者在锁定表中的一行数据。

（3）记录锁（Record Locks）

Record Lock总是会去锁定主键、非空的唯一性索引对应的索引记录，如果在建innodb表时并没有创建任何索引，innodb会对6字节的rowid的主键来进行锁定。Read-Uncommited/RC级别都是使用该方式来进行加锁。 

Record Lock的主要目的：行锁可以防止不同事务版本的数据修改提交时造成数据冲突的情况。

admin@localhost : test 10:53:27> select * from test; +------+------+ | id  | xid  | +------+------+ |    1 |    2 | |    3 |    3 | +------+------+ 2 rows in set (0.00 sec) admin@localhost : test 10:53:45> show index from test; Empty set (0.01 sec) admin@localhost : test 10:54:05> show create table test; +-------+---------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table                                                                                                                          | +-------+---------------------------------------------------------------------------------------------------------------------------------------+ | test  | CREATE TABLE `test` ( `id` int(11) DEFAULT NULL, `xid` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | +-------+---------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

 

下面我们开启2个会话session A 、session B 、session C进行测试

 

 

可以通过INFORMATION_SCHEMA中的innodb_lock_waits、innodb_locks、innodb_trx查看到锁的详细信息

 

admin@localhost : test 11:10:26> select * from information_schema.innodb_lock_waits; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 17660049          | 17660049:589:3:4  | 17660047        | 17660047:589:3:4 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.02 sec) Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release. admin@localhost : test 11:10:35> select * from information_schema.innodb_locks; +------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+ | lock_id          | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index      | lock_space | lock_page | lock_rec | lock_data      | +------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+ | 17660049:589:3:4 | 17660049    | X        | RECORD    | `test`.`test` | GEN_CLUST_INDEX |        589 |        3 |        4 | 0x000000000400 | | 17660047:589:3:4 | 17660047    | X        | RECORD    | `test`.`test` | GEN_CLUST_INDEX |        589 |        3 |        4 | 0x000000000400 | +------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) admin@localhost : test 11:11:31> select * from information_schema.innodb_trx\G *************************** 1. row ***************************                 trx_id: 17660049             trx_state: LOCK WAIT           trx_started: 2018-05-24 11:10:33 trx_requested_lock_id: 17660049:589:3:4       trx_wait_started: 2018-05-24 11:10:33             trx_weight: 3   trx_mysql_thread_id: 15             trx_query: update test set xid=5 where id=3   trx_operation_state: fetching rows     trx_tables_in_use: 1     trx_tables_locked: 1       trx_lock_structs: 3 trx_lock_memory_bytes: 1136       trx_rows_locked: 2     trx_rows_modified: 0 trx_concurrency_tickets: 0   trx_isolation_level: READ COMMITTED     trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0       trx_is_read_only: 0 trx_autocommit_non_locking: 0

 

从information_schema.innodb_locks可以看出session A的事务17660047 与 session C 的事务 17660049 都是对聚集索引上的id=3记录是上排它锁，并且session C 的事务 17660049 在等待session A的事务17660047 的X锁对 id = 3记录的释放。

（4）间隙锁（Gap Locks）

• Gap Lock：间隙锁只存在于RR隔离级别下的辅助索引中，只锁定一个范围，但不包含记录本身。如果只锁定一个范围，那这个范围是怎样的？

• Gap Lock的主要目的：间隙锁避免了别的事务插入数据，从而避免了不可重复读现象。

• Gap Lock的特点：

  • 一个间隙锁可能间隔一个索引值、多个索引值或者是无穷

  • 间隙锁是为了平衡性能和并发的一部分，并且间隙锁只能在RR隔离级别下使用

  • 对于使用唯一索引查找数据， 是 不需要使用间隙锁，但是并不包含查询条件中只包含多列中的某些列，唯一索引在这样的情况下，会使用间隙锁来锁定。

  • 不同的事务可以在一个间隙锁中持有冲突的锁。如果事务A在一个间隙锁中持有的是共享的间隙锁（gap S-lock），而事务B持有事务A在相同间隙的独占间隙锁（gap X-lock）。该类型的锁冲突间隙锁是允许的，如果a记录被从索引上删除，不同事务在该记录上的间隙锁将被合并。

  • innodb存储引擎的间隙锁范围是完全禁止操作的，这将意味着其他事务无法对间隙锁范围进行插入操作。间隙锁不会阻止不同的事务去获取同样的间隙锁范围，因此间隙锁 gap X-lock 和 gap S-lock 的效果是一样的。

  • 间隙锁可以被显式的禁用。将事务的隔离级别设置为 READ COMMITTED 或者开启 innodb_locks_unsafe_for_binlog=ON （已经被弃用）。在这样的情况下，在查询和索引扫描中禁用间隙锁，并且只适用于外键约束和主键检查。

  • 在使用READ COMMITTED隔离级别或者开启 innodb_locks_unsafe_for_binlog=ON 都可以显式的禁用间隙锁。开启“semi-consistent” 半一致行读取后，MySQL 会过滤掉不匹配的行，并且释放不匹配的行的锁，并且将过滤后数据返回到存储引擎层去更新。

• 测试步骤 
  在RR隔离级别下，创建一张只有辅助索引的t3表，并且对辅助索引的一个范围使用 for update 查询，插入包含在范围中的值，然后分别对范围的上确界和下确界进行update操作。

admin@localhost : test 03:55:34> set session transaction_isolation='REPEATABLE-READ' ; Query OK, 0 rows affected (0.00 sec) admin@localhost : test 03:29:44> show variables like '%lation'; +---------------+-----------------+ | Variable_name | Value          | +---------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.01 sec) root@localhost : test1 12:12:40> select * from t3; +------+------+ | id | xid | +------+------+ | 1 | 1 | | 2 | 1 | | 4 | 3 | | 7 | 7 | | 10 | 9 | +------+------+ 5 rows in set (0.00 sec) root@localhost : test1 12:08:24> show index from t3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t3 | 1 | xid | 1 | xid | A | 3 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)

 

 

接下来使用mysql 8.0 中的performance_schema.data_locks 来辅助我们查看这2个事务具体锁的那条记录，和锁的模式等相关信息。