-------------------------------数据库的锁粒度篇---------------------------------------------
首先我们有一张数据表,三个字段id,name,age,val。id 是主键,name 是索引,info 是普通字段,val是我们需要改动的字段。
数据库引擎使用innodb,myisam只支持表锁,这里不讨论,表结构和初始化数据如下:
CREATE TABLE `test` (
`id` bigint(0) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`val` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 'name1', 'info1', '1');
INSERT INTO `test` VALUES (2, 'name2', 'info2', '2');
INSERT INTO `test` VALUES (3, 'name3', 'info3', '3');
INSERT INTO `test` VALUES (4, 'name4', 'info4', '4');
INSERT INTO `test` VALUES (5, 'name5', 'info5', '5');
INSERT INTO `test` VALUES (6, 'name6', 'info5', '6');
INSERT INTO `test` VALUES (7, 'name7', 'info7', '7');
INSERT INTO `test` VALUES (8, 'name8', 'info8', '8');
INSERT INTO `test` VALUES (9, 'name9', 'info9', '9');me9', 'info9', '9');
-
无索引命中,修改数据库记录使用的表锁
执行一下语句,但是不执行提交,where info = "info3" 是不能命中索引的
然后我们去修改数据库表的任意一行记录,修改操作等锁阻塞,直到前面一个事务commit提交这一行执行,锁释放,如果阻塞超过默认配置等锁时间45秒,那么直接等锁失败,回滚事务。
重复试验,我们修改一下别的字段,依旧阻塞
-
有索引命中的时候,锁的粒度是行锁,或者间隙锁
把前面的数据还原到初始化状态,然后通过索引字段那么修改,但是不提交
我们修改第7列,然后保存,直接成功没有被锁定
我们修改第2,4列直接成功
我们修改第3列,等锁,直到commit 提交以后修改才能进行
-
我们继续验证通过主键索引修改
我们修改,3,4的记录把它改回去,没有阻塞
我们修改3的记录,等锁阻塞
我们暂时得出结论,索引字段和 id 字段结果一样(实际在间隙锁的时候有区别,暂时可以认为是一样的)
- 结合实际,如果我们要要通过索引字段去修改数据,直接修,并且尽量要这么做,如果不能那么我们应该查询出对应数据的id,然后通过id去修改对应的数据,避免表锁造成大面积阻塞。
例子:修改 第三行记录如果我们知道name或者id
update test set XXX=XXX where name = XXX;
如果只知道val就应该这么写
select id from test where val = XXX;
update test set xxx=xxx where id = 上面查询出来的id;
5. 数据库的锁在什么时候获取的,在什么时候释放的
在当前事务第一次修改对应数据的时候,获取写锁(获取要修改数据的锁,不是全部需要修改的数据的锁)
在当前事务提交的时候,释放锁
- for update 效率很低吗?是表锁吗?
回答,效率不一定低,有时候还必须用,是不是表锁分情况
for update 是一个写锁,什么粒度取决于索引命中的情况。
for update 相当于加长了锁的范围,从这点上来说它效率是比较低的,只要控制锁的是行锁,并且注意代码上面的粒度 for update 是完全可以接受了,数据库写操作都是有锁的,编码的过程中应该优先考虑是否不是数据库锁就应该能够完成功能,而不是一味的在数据库锁上面在套一层别的锁。
索引情况下,执行前两句,不commit
修改,2,4 列不会阻塞,修改3列会阻塞
非索引情况下,修改条件问val=3
修改第4条数据等锁阻塞
-
数据库的读数据加了读锁吗?
没有加,但是也可以自己 -
lock in share mode 是加的 写锁吗?我怎么感觉它和 for update 效果一样呀?
lock in share mode 加的读锁,排查写操作,不排读锁
for update 加的写锁,排除写操作,也排除读锁,但是不排查数据库非锁定读
获取一个读锁还能在获取一次
然后去改数据,获取写锁等待,并且需要两个读锁都释放以后这个写锁才能获取
然后我们用 for update 是不能 获取两次的,第一次获取了,第二次就会阻塞
-
间隙锁的情况,间隙锁的情况默认都是先加读锁,然后真实写入数据的时候是锁升级,写锁升级,锁升级就会带来死锁问题,mysql 默认处理是回滚其中一个,提交另一一个。
执行前两行,name=name19,数据库没有命中,使用间隙锁,锁定的间隙是 name9 -name 19 这个区间,用两个窗口都执行前两行,都能获取到锁,然后分别执行第三行
先执行第三行的会阻塞等待读锁升级写锁,后执行的也获取写锁检查到死锁,mysql 自动回滚其中一个,然后另外一个完成锁升级,获取写锁正常执行。
间隙锁在我们认为应该获取写的的时候获取的是读锁,然后再真实写入数据的时候升级读锁伪写锁。
- 间隙锁区间的开闭情况