mysql数据库一些实用的东西

-------------------------------数据库的锁粒度篇---------------------------------------------

首先我们有一张数据表，三个字段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 自动回滚其中一个，然后另外一个完成锁升级，获取写锁正常执行。

间隙锁在我们认为应该获取写的的时候获取的是读锁，然后再真实写入数据的时候升级读锁伪写锁。
1. 上面的测试都是基于RR级别。在RC级别小 update 后面where 语句即便没有索引，有些时候会强制使用主键索引，类似查询出ids，然后更新，这时候是锁行。
  但是通过执行计划来看，RC RR 级别下都是强制使用主键索引，但是RC 确实实时锁行，RR锁表了。不知道是执行计划显示异常还是，别的别的什么原因。

posted on 2022-12-17 22:26 zhangyukun 阅读(29) 评论(0) 编辑收藏举报

刷新页面返回顶部

lomi

mysql数据库一些实用的东西

导航

公告