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');

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.

1. 无索引命中,修改数据库记录使用的表锁
   执行一下语句，但是不执行提交，where info = "info3" 是不能命中索引的

然后我们去修改数据库表的任意一行记录，修改操作等锁阻塞，直到前面一个事务commit提交这一行执行，锁释放，如果阻塞超过默认配置等锁时间45秒，那么直接等锁失败，回滚事务。

重复试验，我们修改一下别的字段，依旧阻塞

2. 有索引命中的时候，锁的粒度是行锁，或者间隙锁
   把前面的数据还原到初始化状态，然后通过索引字段那么修改，但是不提交

我们修改第7列，然后保存，直接成功没有被锁定

我们修改第2，4列直接成功

我们修改第3列，等锁,直到commit 提交以后修改才能进行

3. 我们继续验证通过主键索引修改
   

我们修改，3,4的记录把它改回去，没有阻塞

我们修改3的记录，等锁阻塞

我们暂时得出结论，索引字段和 id 字段结果一样(实际在间隙锁的时候有区别，暂时可以认为是一样的)

4. 结合实际，如果我们要要通过索引字段去修改数据，直接修，并且尽量要这么做，如果不能那么我们应该查询出对应数据的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. 数据库的锁在什么时候获取的，在什么时候释放的

在当前事务第一次修改对应数据的时候，获取写锁（获取要修改数据的锁，不是全部需要修改的数据的锁）

在当前事务提交的时候，释放锁

6. for update 效率很低吗？是表锁吗？

回答，效率不一定低，有时候还必须用，是不是表锁分情况

for update 是一个写锁，什么粒度取决于索引命中的情况。

for update 相当于加长了锁的范围，从这点上来说它效率是比较低的，只要控制锁的是行锁，并且注意代码上面的粒度 for update 是完全可以接受了，数据库写操作都是有锁的，编码的过程中应该优先考虑是否不是数据库锁就应该能够完成功能，而不是一味的在数据库锁上面在套一层别的锁。

索引情况下，执行前两句，不commit

修改，2,4 列不会阻塞，修改3列会阻塞

非索引情况下，修改条件问val=3

修改第4条数据等锁阻塞

7. 数据库的读数据加了读锁吗？
   没有加,但是也可以自己
8. lock in share mode 是加的 写锁吗？我怎么感觉它和 for update 效果一样呀？
   lock in share mode 加的读锁，排查写操作，不排读锁
   for update 加的写锁，排除写操作，也排除读锁，但是不排查数据库非锁定读
   获取一个读锁

还能在获取一次

然后去改数据，获取写锁等待，并且需要两个读锁都释放以后这个写锁才能获取

然后我们用 for update 是不能 获取两次的，第一次获取了，第二次就会阻塞

9. 间隙锁的情况，间隙锁的情况默认都是先加读锁，然后真实写入数据的时候是锁升级，写锁升级，锁升级就会带来死锁问题，mysql 默认处理是回滚其中一个，提交另一一个。
   执行前两行，name=name19，数据库没有命中，使用间隙锁，锁定的间隙是 name9 -name 19 这个区间，用两个窗口都执行前两行，都能获取到锁，然后分别执行第三行

先执行第三行的会阻塞等待读锁升级写锁，后执行的也获取写锁检查到死锁，mysql 自动回滚其中一个，然后另外一个完成锁升级，获取写锁正常执行。

间隙锁在我们认为应该获取写的的时候获取的是读锁，然后再真实写入数据的时候升级读锁伪写锁。

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

原创作者: u_15958225 转载于: https://blog.51cto.com/u_15958225/11802701