================================
虽然锁机制是 InnoDB 引擎中为了保证事务性而自然存在的,在索引、表结构、配置参数一定的前提下,InnoDB 引擎加锁过程是一样的,所以理论上来说也就不存在 "锁机制能够提升性能" 这样的说法。但如果技术人员不理解 InnoDB 中的锁机制或者混乱、错误的索引定义和同样混乱的 SQL 写操作语句共同作用,那么导致死锁出现的可能性就越大,需要 InnoDB 进行死锁检测的情况就越多,最终导致不必要的性能浪费甚至事务执行失败。所以理解 InnoDB 引擎中的锁机制可以帮助我们在高并发系统中尽可能不让锁和死锁成为数据库服务的一个性能瓶颈。
本文讲解的锁机制主要依据 MySQL Version 5.6 以及之前的版本(这是目前线上环境使用最多的版本),在 mysql Version 5.7 以及最新的 MySQL 8.0 中 InnoDB 引擎的锁类型发生了一些变化(后文会提及),但基本思路没有变化。InnoDB 引擎中的锁类型按照独占形式可以分为共享锁和排它锁(还有意向性共享锁和意向性排它锁);按照锁定数据的范围可以分为行级锁(其它引擎中还有页级锁的定义)、间隙锁、间隙复合锁??和表锁;为了保证锁的粒度能够至上而下传递,InnoDB 中还设计有不能被用户干预的意向共享锁和意向排它锁。
由于 InnoDB 引擎支持事务,所以需要锁机制在多个事务同时工作时保证每个事务的 ACID 特性。共享锁的特性是多个事务可以同时为某个资源加锁后进行读操作,并且这些事务间不会出现相互等待的现象。
排它锁又被称为独占锁,一旦某个事务对资源加排它锁,其它事务就不能再为这个资源加共享锁或者排它锁了。一直要等待到当前的独占锁从资源上解除后,才能继续对资源进行操作。排它锁只会影响其他事务的加锁操作,也就是说如果其它事务只是使用简单的 SELECT 查询语句检索资源,就不会受到影响,因为这些 SELECT 查询语句不会试图为资源加任何锁,也就不会受资源上已有的排它锁的影响。我们可以用一张表表示排它锁和共享锁的互斥关系:
互斥:如果某个资源要加排它锁,则需要等待到资源上的排它锁配解除后,才能进行这个操作
互斥:如果资源要加排它锁,则需要等待到资源上所有共享锁都被解除后,才能进行这个操作
排它锁 X
互斥:如果某个资源要加共享锁,则需要等待到资源上的排它锁配解除后,才能进行这个操作
不互斥:多个共享锁不会相互影响相互等待
共享锁 S
排它锁 X
共享锁 S
锁类型
排它锁和共享锁的互斥关系
行级锁是 InnoDB 引擎中对锁的最小支持粒度,即是指这个锁可以锁定数据表中某一个具体的数据行,锁的类型可以是排它锁也可以是共享锁。例如读者可以在两个独立事务中同时使用以下语句查询指定的行,但是两个事务并不会相互等待:
- #lock in share mode是为满足查询条件的数据行加共享锁#注意它和直接使用select语句的不同特性select * from myuser where id = 6 lock in share mode;
- 1 2 3 1 2 3
间隙锁只有在特定事务级别下才会使用,具体来说是 "可重复读"(Repeatable Read )这样的事务级别,这也是 InnoDB 引擎默认的事务级别,它的大致解释是无论在这个事务中执行多少次相同语句的当前读操作,其每次读取的记录内容都是一样的,并不受外部事务操作的影响。间隙锁主要为了防止多个事务在交叉工作的情况下,特别是同时进行数据插入的情况下出现幻读。举一个简单的例子,事务 A 中的操作正在执行以下 update 语句的操作:
- ......#事务A正在执行一个范围内数据的更新操作#大意是说将用户会员卡号序列大于10的所有记录中user_name字段全部更新为一个新的值update myuser set user_name = '用户11'where user_number >= 10;......1 2 3 4 5 1 2 3 4 5
其中 user_number 带有一个索引(后续我们将讨论这个索引类型对间隙锁策略的影响),这样的检索条件很显然会涉及到一个范围的数据都将被更新(例如 user_number==10、13、15、17、19、21……),于此同时有另一个事务 B 正在执行以下语句:
- ......#事务B正在执行一个插入操作insert into myuser(........., 'user_number') values(........., 11);#插入一个卡号为11的新会员,然后提交事务B......1 2 3 4 5 1 2 3 4 5
如果 InnoDB 只锁住 user_number 值为 10 的非聚簇索引和相应的聚簇索引,显然就会造成一个问题:在 A 事务处理过程中,突然多出了一条满足更新条件的记录。事务 A 会很纠结的,很尴尬的。如果读者是 InnoDB 引擎的开发者,您会怎么做呢?正确的做法是为满足事务 A 所执行检索条件的整个范围加锁,这个锁不是加在某个或某几个具体的记录上,因为那样做还是无法限制类似插入 "一个卡号为 11 的新纪录" 这样的情况,而是加在到具体索引和下一个索引之间,告诉这个索引 B + 树的其它使用者,包括这个索引在内的之后区域都不允许使用。这样的锁机制称为间隙锁(GAP 锁)。
间隙锁和行级锁组合起来称为 Next-Key Lock,实际上这两种锁一般情况下都是组合工作的。
uid(PK) varchar
user_name varchar
user_sex int
这张数据表中只有一个由 uid 字段构成的主索引。接着两个事务同时执行以下语句:
- begin;
- select * from t_user where uid = 2 lock in share mode;#都先不执行commit,以便观察现象#commit;
- 1 2 3 4 1 2 3 4
这里的 select 查询虽然使用的检索依据是 uid,但是设置检索条件时 uid 的 varchar 类型却被错误的使用成了 int 类型。那么数据表将不再使用索引进行检索,转而进行全表扫秒。这是一种典型的索引失效情况,最终读者观察到的现象是,在执行以上同一查询语句的两个事务中,有一个返回了查询结果,但是另外一个一直为等待状态。以上的小例子也可以让读者看到,科学管理索引在 InnoDB 引擎中是何等重要。本文后续部分将向读者介绍表级锁的实质结构。
为了在某一个具体索引上加共享锁,事务需要首先为涉及到的数据表加意向共享锁(IS 锁);为了在某一个具体所以上加排它锁,事务需要首先为涉及到的数据表加意向排它锁(IX 锁)。这样 InnoDB 可以整体把握在并发的若干个事务中,让哪些事务优先执行更能产生好的执行效果。意向共享锁是 InnoDB 引擎自动控制的,开发人员无法人工干预,也不需要干预。
InnoDB 引擎中的锁机制基于索引才能工作。对数据进行锁定时并不是真的锁定数据本身,而是对数据涉及的聚集索引和非聚集索引进行锁定。在之前的文章中我们已经介绍到,InnoDB 引擎中的索引按照 B + 树的结构进行组织,那么加锁的过程很明显就是在对应的 B + 树上进行加锁位置检索和进行标记的过程。并且 InnoDB 引擎中的非聚簇索引最终都要依靠聚簇索引才能找到具体的数据记录位置,所以加锁的过程都涉及到对聚簇索引进行操作。
SELECT 关键字的查询操作一般情况下都不会涉及到锁的问题(这种类型的读操作称为快照读),但并不是所有的查询操作都不涉及到锁机制。只要 SELECT 属于某种写操作的前置子查询 / 检索或者开发人员显式为 SELECT 加锁,这些 SELECT 语句就涉及到锁机制——这种读操作称为当前读。而执行 Update、Delete、Insert 操作时,InnoDB 会根据会根据操作中 where 检索条件所涉及的一条或者多条数据加排它锁。
为了进一步详细说明各种典型的加锁过程,本小节为读者准备了几个实例场景,并使用图文混合的方式从索引逻辑层面上进行说明。后续的几种实例场景都将以以下数据表和数据作为讲解依据:
- CREATE TABLE`myuser` (`Id`int(11) NOT NULL AUTO_INCREMENT, `user_name`varchar(255) NOT NULL DEFAULT '', `usersex`int(9) NOT NULL DEFAULT '0', `user_number`int(11) NOT NULL DEFAULT '0', PRIMARY KEY(`Id`), UNIQUE KEY`number_index` (`user_number`), KEY`name_index` (`user_name`)) 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9
这张表中有三个索引,一个是以 id 字段为依据的聚簇索引,一个是以 user_name 字段为依据的非唯一键非聚簇索引,最后一个是以 user_number 字段为依据的唯一键非聚簇索引。我们将在实例场景中观察唯一键索引和非唯一键索引在加锁,特别是加 GAP 锁的情况的不同点。这张数据表中的数据情况如下图所示:
首先我们演示一个工作在 InnoDB 引擎下的数据表只加行锁的情况。
- begin;
- update myuser set user_name = '用户11'where id = 10;
- commit;
- 1 2 3 1 2 3
以上事务中只有一条更新操作,它直接使用聚簇索引作为检索条件。聚簇索引肯定是一个唯一键索引,所以 InnoDB 得出的加锁条件也就不需要考虑类似 "insert into myuser(id,………) values(10,………)" 这样的字段重复情况。因为如果有事务执行了这样的语句,就会直接报错退出。那么最终的加锁结果就是:只需要在聚簇索引上加 X 锁。
(额~~~ 你要问我为什么树结构会是连续遍历的?请重读 B + 树的介绍)
其它事务依然可以对聚簇索引上的其它节点进行操作,例如使用 update 语句更新 id 为 14 的数据:
- begin;
- update myuser set user_name = '用户1414'where id = 14;
- commit;
- 1 2 3 1 2 3
当然,由于这样的执行过程没有在 X 锁临近的边界加 GAP 锁,所以开发人员也可以使用 insert 语句插入一条 id 为 11 的数据:
- begin;
- insert into myuser(id, user_name, usersex, user_number) values(11, '用户1_1', 1, '110110110');
- commit;
- 1 2 3 1 2 3
工作在 InnoDB 引擎下的数据表,更多的操作过程都涉及到加间隙锁(GAP)的情况,这是因为毕竟大多数情况下我们定义和使用的索引都不是唯一键索引,都在 "可重复读" 的事务级别下存在 "幻读" 风险。请看如下事务执行过程:
- begin;
- update myuser set usersex = 0 where user_name = '用户8'commit;
- 1 2 3 1 2 3
这个事务操作过程中的 update 语句,使用非唯一键非聚簇索引'name_index'进行检索。InnoDB 引擎进行分析后发现存在幻读风险,例如可能有一个事务在同时执行以下操作:
- begin;
- insert into myuser(id, user_name, usersex, user_number) values(11, '用户8', 1, '110110110');#或者执行以下插入#insert into myuser(id, user_name, usersex, user_number) values(11, '用户88', 1, '110110110');
- commit;
- 1 2 3 4 5 1 2 3 4 5
所以 InnoDB 需要在 X 锁临近的位置加 GAP 锁,避免幻读:
以上示意图有一个注意点,在许多技术文章中对 GAP 锁的讲解都是以 int 字段类型为基准,但是这里讲解所使用的类型是 varchar。所以在加 GAP 锁的时候,看似'用户 8'和'用户 9'这两个索引节点没有中间值了。但是字符串也是可以排序的,所以'用户 8'和'用户 9'这两个字符串之间实际上是可以放置很多中间值的,例如'用户 88'、'用户 888'、'用户 8888'等。
这就是为什么另外的事务执行类似 "insert into myuser(id,user_name,usersex,user_number) values (11,'用户 88',1,'110110110');" 这样的语句,同样会进入等待状态:因为有 GAP 锁进行独占控制。
上文已经提到,索引一旦失效 InnoDB 也会为整个数据表加锁。那么 "为整个数据表加锁" 这个动作怎么理解呢?很多技术文章在这里一般都概括为一句话 "在 XXX 数据表上加锁"。要弄清楚表锁的加载位置,我们就需要进行实践验证。首先,为了更好的查看 InnoDB 引擎的工作状态和加锁状态,我们需要打开 InnoDB 引擎的监控功能:
- #使用以下语句开启锁监控set GLOBAL innodb_status_output = ON;
- set GLOBAL innodb_status_output_locks = ON;
- 1 2 3 1 2 3
接下来我们就可以使用 myuser 数据表中没有键立索引的 "usersex" 字段进行加锁验证:
- begin;
- update myuser set user_name = '用户1414'where usersex = 1;#先不忙使用commit,以便观察锁状态#commit;
- 1 2 3 4 1 2 3 4
在执行以上事务之前,myuser 数据表中最新的记录情况如下图所示:
可以看到 myuser 数据表中一共有 13 条记录,其中满足 "usersex = 1" 的数据一共有 9 条记录。那么按照 InnoDB 引擎行锁机制来说,就应该只有这 9 条记录被锁定,那么是否如此呢?我们通过执行 InnoDB 引擎的状态监控功能来进行验证:
- show engine innodb status;
- #以下是执行结果(省略了一部分不相关信息) === ==================================2016 - 10 - 06 22 : 22 : 49 2f74 INNODB MONITOR OUTPUT === ==================================.......------------TRANSACTIONS------------Trx id counter 268113 Purge done
- for trx 's n:o < 268113 undo n:o < 0 state: running but idle
- History list length 640
- LIST OF TRANSACTIONS FOR EACH SESSION:
- ......
- ---TRANSACTION 268103, ACTIVE 21 sec
- 2 lock struct(s), heap size 360, 14 row lock(s), undo log entries 9
- MySQL thread id 5, OS thread handle 0x1a3c, query id 311 localhost 127.0.0.1 root cleaning up
- TABLE LOCK table `qiang`.`myuser` trx id 268103 lock mode IX
- RECORD LOCKS space id 1014 page no 3 n bits 152 index `PRIMARY` of table `qiang`.`myuser` trx id 268103 lock_mode X
- Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
- 0: len 8; hex 73757072656d756d; asc supremum;;
- Record lock, heap no 79 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
- 0: len 4; hex 8000000a; asc ;;
- 1: len 6; hex 000000041723; asc #;;
- 2: len 7; hex 2c000001e423fd; asc , # ;;
- 3: len 8; hex e794a8e688b73130; asc 10;;
- 4: len 4; hex 80000000; asc ;;
- 5: len 4; hex 80018a92; asc ;;
- Record lock, heap no 80 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
- 0: len 4; hex 8000000e; asc ;;
- 1: len 6; hex 000000041721; asc !;;
- 2: len 7; hex 2b000001db176a; asc + j;;
- 3: len 8; hex e794a8e688b73134; asc 14;;
- 4: len 4; hex 80000000; asc ;;
- 5: len 4; hex 80022866; asc (f;;
- Record lock, heap no 81 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
- 0: len 4; hex 80000012; asc ;;
- 1: len 6; hex 00000004171f; asc ;;
- 2: len 7; hex 2a000001da17b2; asc * ;;
- 3: len 8; hex e794a8e688b73138; asc 18;;
- 4: len 4; hex 80000000; asc ;;
- 5: len 4; hex 8002c63a; asc :;;
- Record lock, heap no 82 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
- 0: len 4; hex 80000016; asc ;;
- 1: len 6; hex 00000004171d; asc ;;
- 2: len 7; hex 290000024d0237; asc ) M 7;;
- 3: len 8; hex e794a8e688b73232; asc 22;;
- 4: len 4; hex 80000000; asc ;;
- 5: len 4; hex 80035c3c; asc \<;;
- Record lock, heap no 86 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
- 0: len 4; hex 80000001; asc ;;
- 1: len 6; hex 000000041747; asc G;;
- 2: len 7; hex 41000002580110; asc A X ;;
- 3: len 10; hex e794a8e688b731343134; asc 1414;;
- 4: len 4; hex 80000001; asc ;;
- 5: len 4; hex 80002b67; asc +g;;
- ...... 这里为节约篇幅,省略了6条行锁记录......
- Record lock, heap no 93 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
- 0: len 4; hex 80000008; asc ;;
- 1: len 6; hex 000000041747; asc G;;
- 2: len 7; hex 410000025802b4; asc A X ;;
- 3: len 10; hex e794a8e688b731343134; asc 1414;;
- 4: len 4; hex 80000001; asc ;;
- 5: len 4; hex 80015b38; asc [8;;
- Record lock, heap no 94 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
- 0: len 4; hex 80000009; asc ;;
- 1: len 6; hex 000000041747; asc G;;
- 2: len 7; hex 410000025802f0; asc A X ;;
- 3: len 10; hex e794a8e688b731343134; asc 1414;;
- 4: len 4; hex 80000001; asc ;;
- 5: len 4; hex 8001869f; asc ;;
- ......
- 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
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 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
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82'
通过以上日志我们观察到的比较重要情况是,编号为 268103 的事务拥有两个锁结构(2 lock struct(s)),其中一个锁结构是意向性排它锁 IX,这个锁结构一共锁定了一条记录(这条记录并不是 myuser 数据表中的一条记录);另外一个锁结构是排它锁(X),这个锁结构加载在主键索引上("page no 3 n bits 152 index'PRIMARY'of table'qiang'.'myuser'"),并且锁定了 13 条记录。这 13 条记录就是 myuser 数据表中的所有数据记录,并非我们最先预计的 9 条记录。
这就是表锁在锁定规律上的具体表现:因为不能基于索引检索查询条件,所以就只能基于聚集索引进行全表扫描。因为不能确定聚集索引上哪些 Page 中数据满足检索条件,所以只能用排它锁一边锁定数据一边进行检索。因为要满足事务的 ACID 特性,所以在事务完成执行(或错误回滚)前都不能解除锁定:
由于我们一直讨论的 InnoDB 引擎默认的事务级别是 "可重复度"(Repeatable Read),所以为了避免幻读,InnoDB 还会在每一个排它性行锁周围都加上间隙锁(GAP)。那么在这个事务级别下表锁最终的逻辑表现就如下图所示:
是的,没有索引可以提供检索依据的数据表正在进行一场豪赌!这还是只有 13 条数据的情况下,那么试想一下如果数据表中有 10,000,000 条记录呢?这不仅造成资源的浪费,更重要的是表锁是造成死锁的重要原因,而且由此引发的 InnoDB 自动解锁代价非常昂贵(后文会详细讲到)。
一旦构成死锁,InnoDB 会尽可能的帮助开发者解除死锁。其做法是自动终止一些事务的运行从而释放锁定状态。在上一小节我们示范的多个加锁场景,它们虽然都构成锁等待,但是都没有构成死锁。那么本文就要首先说明一下,什么样的情况才构成死锁。
两个或者多个事务相互等待对方已锁定的资源,而彼此都不为协助对方达成操作目而主动释放已锁定的资源,这样的情况就称为死锁。请区分正常的锁等待和死锁的区别,例如以下示意图中的锁等待并不构成死锁:
上图中的情况只能称为锁资源等待,这是因为当 A 事务完成处理后就会释放所占据的资源上的锁,这样 B 事务就可以继续进行处理。并且在这个过程中没有任何因素阻止 A 事务完成,也没有任何因素阻止 B 事务在随后的操作中获取锁。但是,以下示意图中的两个事务就在相互等待对方已锁定的资源,这就称为死锁:
上图中 A 事务已为 id1 和 id2 这两个索引项加锁,当它准备为 id4 这个索引加锁时,却发现 id4 已经被事务 B 加锁,于是事务 A 进行等待过程。恰巧的是,B 事务在为 id4、id5 加锁后,正在等待为 id2 这个索引项加锁。于是最后造成的结果就是事务 A 和事务 B 相互等待对方释放资源。注意,由于需要保证事务的 ACID 特性,所以 A 事务已经锁定的索引 id1、id2 在事务 A 的等待过程中,是不会被释放的;同样事务 B 已经锁定的索引 id4、id5 在等待过程中也不会被释放。很明显如果没有外部干预,这个互相等待的过程将一直持续下去。这就是一个典型的死锁现象。在实际应用场景中,往往会由超过两个事务共同构成死锁现象,甚至会出现强制终止某一个等待的事务后依然不能解除死锁的复杂情况。
死锁造成的根本原因和上层 MySQL 服务和下层 InnoDB 引擎的协调方式有关:在上层 MySQL 服务和下层 InnoDB 引擎配合进行 Update、Delete 和 Insert 操作时, 对满足条件的索引加 X 锁的操作是逐步进行的。
当 InnoDB 进行 update、delete 或者 insert 操作时,如果有多条记录满足操作要求,那么 InnoDB 引擎会锁定一条记录(实际上是相应的索引)然后再对这条记录进行处理,完成后再锁定下一条记录进行处理。这样依次循环直到所有满足条件的数据被处理完,最后再统一释放事务中的所有锁。如果这个过程中某个将要锁定的记录已经被其它事务抢先锁定,则本事务就进入等待状态,一直等待到锁定的资源被释放为止。
要知道在正式的生成环境中,可能会同时有多个事务对某一个数据表上同一个范围内的数据进行加锁(加 X 锁后进行写操作)操作。而 InnoDB 引擎和 MySQL 服务的交互采用的这种方式很可能使这些事务各自持有某些记录的行锁,但又不构成让事务继续执行下去的条件。那为什么说在生产环境下,多数死锁状态的出现是因为表锁导致的呢?
百度搜索 "就爱阅读", 专业资料, 生活学习, 尽在就爱阅读网 92to.com, 您的在线图书馆!
来源: http://www.92to.com/bangong/2017/07-26/25724468.html