上锁过程实例

最新推荐文章于 2023-01-31 14:40:44 发布

Jaymeng8848

最新推荐文章于 2023-01-31 14:40:44 发布

阅读量370

点赞数

分类专栏： mysql 文章标签：数据库

原文链接：https://blog.csdn.net/yinwenjie/article/details/52735063

版权

mysql 专栏收录该内容

23 篇文章 2 订阅

订阅专栏

**nnoDB引擎中的锁机制基于索引才能工作。对数据进行锁定时并不是真的锁定数据本身，而是对数据涉及的聚集索引和非聚集索引进行锁定。**在之前的文章中我们已经介绍到，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)
)
这张表中有三个索引，一个是以id字段为依据的聚簇索引，一个是以user_name字段为依据的非唯一键非聚簇索引，最后一个是以user_number字段为依据的唯一键非聚簇索引。我们将在实例场景中观察唯一键索引和非唯一键索引在加锁，特别是加GAP锁的情况的不同点。这张数据表中的数据情况如下图所示：

在这里插入图片描述
行锁加锁过程

首先我们演示一个工作在InnoDB引擎下的数据表只加行锁的情况

begin;
update myuser set user_name = '用户11' where id = 10;
commit;

以上事务中只有一条更新操作，它直接使用聚簇索引作为检索条件。聚簇索引肯定是一个唯一键索引，所以InnoDB得出的加锁条件也就不需要考虑类似“insert into myuser(id,………) values(10,………)”这样的字段重复情况。因为如果有事务执行了这样的语句，就会直接报错退出。那么最终的加锁结果就是：只需要在聚簇索引上加X锁。

在这里插入图片描述
其它事务依然可以对聚簇索引上的其它节点进行操作，例如使用update语句更新id为14的数据

begin;
update myuser set user_name = '用户1414' where id = 14;
commit;

当然，由于这样的执行过程没有在X锁临近的边界加GAP锁，所以开发人员也可以使用insert语句插入一条id为11的数据：

begin;
insert into myuser(id,user_name,usersex,user_number) values (11,'用户1_1',1,'110110110');
commit;

间隙锁加锁过程
工作在InnoDB引擎下的数据表，更多的操作过程都涉及到加间隙锁（GAP）的情况，这是因为毕竟大多数情况下我们定义和使用的索引都不是唯一键索引，都在“可重复读”的事务级别下存在“幻读”风险。请看如下事务执行过程：

begin;
update myuser set usersex = 0 where user_name = '用户8'
commit;

这个事务操作过程中的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;

所以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;

接下来我们就可以使用myuser数据表中没有键立索引的“usersex”字段进行加锁验证：

begin;
update myuser set user_name = '用户1414' where usersex = 1;
# 先不忙使用commit，以便观察锁状态
#commit;

在这里插入图片描述
可以看到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     ;;
......

通过以上日志我们观察到的比较重要情况是，编号为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特性，所以在事务完成执行（或错误回滚）前都不能解除锁定：
在这里插入图片描述是的，没有索引可以提供检索依据的数据表正在进行一场豪赌！这还是只有13条数据的情况下，那么试想一下如果数据表中有10,000,000条记录呢？这不仅造成资源的浪费，更重要的是表锁是造成死锁的重要原因，而且由此引发的InnoDB自动解锁代价非常昂贵（后文会详细讲到）。

不是强制加锁的查询和前置子查询不会加锁，他们会进行快照读，反之会进行加锁后执行当前读
所以对于三种锁，我们可以看到的是
行级锁
一般对字段是唯一键索引进行操作会加行级锁，唯一键有加了唯一的字段和主键字段。
间隙锁
对于字段不是唯一键索引进行操作时，在可重复读的事务级别下存在幻读风险
表锁
对没有建立索引的字段进行操作

Jaymeng8848

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
上锁过程实例

**nnoDB引擎中的锁机制基于索引才能工作。对数据进行锁定时并不是真的锁定数据本身，而是对数据涉及的聚集索引和非聚集索引进行锁定。**在之前的文章中我们已经介绍到，InnoDB引擎中的索引按照B+树的结构进行组织，那么加锁的过程很明显就是在对应的B+树上进行加锁位置检索和进行标记的过程。并且InnoDB引擎中的非聚簇索引最终都要依靠聚簇索引才能找到具体的数据记录位置，所以加锁的过程都涉及到对聚簇索引进行操作。SELECT关键字的查询操作一般情况下都不会涉及到锁的问题（这种类型的读操作称为快照读），但并不
复制链接

扫一扫

专栏目录