InnoDB行锁升级表锁(简单易懂)

最新推荐文章于 2024-03-09 23:59:08 发布

郭吱吱

最新推荐文章于 2024-03-09 23:59:08 发布

阅读量1.1k

点赞数 4

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

本文链接：https://blog.csdn.net/pingfanxiansheng/article/details/127070167

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1、InnoDB行锁的实现方式

通过给索引上的索引项加锁来实现的，也就意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁。

2、案例分析

CREATE TABLE `account` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `balance` int(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `account`(`id`, `name`, `balance`) VALUES (1, 'lilei', 800);
INSERT INTO `account`(`id`, `name`, `balance`) VALUES (2, 'hanmei', 16000);
INSERT INTO `account`(`id`, `name`, `balance`) VALUES (3, 'lucy', 2400);
INSERT INTO `account`(`id`, `name`, `balance`) VALUES (4, 'lily', 700);

2.1 对没有索引的数据加锁(行锁升级)

1、session 1(客户端A) 开启一个事务，并且对一条数据做不是索引条件的修改操作
-- session 1(客户端A)
-- 开启事务
begin;
-- 修改条件没有索引
 UPDATE account set name = 'lilei111' where name = 'lilei';
2、session 2(客户端B)对该表的另一条数据进行修改，不管是否修改同一数据行都会阻塞(证明上一步使用了表锁)
-- session 2
-- 非索引条件修改
UPDATE account set name = 'hanmei123' where name = 'hanmei';
-- 索引条件修改
UPDATE account set name = 'hanmei123' where id = 2;
3、session 1(客户端A)commit或者rollback后session 2(客户端B)会解除阻塞

总结：

也就是在INNODB加行锁的时候不仅会将该行数据进行加锁，还会将在此过程中扫描过的数据进行额外的加锁，所以如果MYSQL在找到目标数据的时候进行的是全表扫描，那么实际上就不是行锁了，而是直接将整个表给锁住了，如果是通过索引的方式确定到目标上锁的行数据，那么只会给目标数据进行上锁（因为如果使用索引查找是不用进行全表的数据遍历的）。

2.2 对有索引的数据加锁(行锁)

1、session 1(客户端A)开启事务，然后通过索引修改数据(id是索引)
-- session 1

begin;
UPDATE account set name = 'lilei222' where id = 1;
2、session 2(客户端B)根据其他id修改--可以提交（证明操作1是行锁）
--session 2
-- 索引条件修改
UPDATE account set name = 'hanmei123' where id = 2;
3、session 2(客户端B) 根据非索引字段修改其他数据行--发生锁冲突(证明该操作发生表锁，与操作1的行锁产生了锁冲突)
-- session 2
-- 非索引条件修改
UPDATE account set name = 'hanmei123-session 2' where name = 'hanmei123';
4、session 1 提交事务，创建索引后继续开启事务修改数据，并作操作1的动作
-- session 1 先提交上面的事务，可以后面创建索引
commit;
-- 创建索引
alter table account add index idx_balance(balance);

-- 开始事务，继续操作1的事务
begin;
UPDATE account set name = 'lilei222' where id = 1;
5、session 3(客户端C) 根据其他索引字段修改(条件没有冲突的数据行-可以提交)
-- session 3
-- 其他索引条件修改(没有冲突的数据行)
UPDATE account set name = 'hanmei123-session 3' where balance = 16000;
6、session 3 (客户端C)根据其他索引字段修改(条件有冲突的数据行-阻塞)
-- session 3
-- 其他索引条件修改(有冲突的数据行)
UPDATE account set name = 'lilei111-session 3' where balance = 800;

2.3 索引失效-导致锁升级

先将name修改为数字

1、session 1(客户端A) 添加name索引，根据该索引进行修改，但是使用隐形类型转换手段导致该索引失效
-- session 1
-- 创建索引
alter table account add index idx_name(name);
-- 开启事务 

begin;
 -- 因为name是字符串，条件值为int导致所有失效

UPDATE account set name = 'lilei222-session 1' where name = 111;
2、session 2(客户端B)修改其他数据行，但是依然阻塞-锁升级，说明操作1升级到了表锁
-- session 2
UPDATE account set name = 'hanmei123-session 2' where name = 222;