数据库的锁机制

mysql如何处理脏写的

　　mysql处理写并发，说白了就是靠的锁机制，让多个事务对一行数据执行写操作的时候串行化，避免同时写一行数据。如果现在有一个事务来了要更新这行数据，它会先看看这行数据此时有没有人加锁？一看没人加锁，太好了，说明他是第一个人，捷足先登了。此时这个事务就会创建一个锁，里面包含了自己的trx_id和等待状态，然后把锁跟这行数据关联在一起。

　　

　　现在有另外一个事务B过来了，也想更新那行数据，此时就会检查一下，当前这行数据有没有别人加锁。然而他一下子发现，事务A居然抢先给这行数据加锁了，这怎么办呢？事务B这个时候一想，那行，我也加个锁，然后等着排队不就得了，这个时候事务B也会生成一个锁数据结构，里面有他的trx_id，还有自己的等待状态，但是他因为是在排队等待，所以他的等待状态就是true了。

　　

　　接着事务A这个时候更新完了数据，就会把自己的锁给释放掉了。锁一旦释放了，他就会去找，此时还有没有别人也对这行数据加锁了呢？他会发现事务B也加锁了。于是这个时候，就会把事务B的锁里的等待状态修改为false，然后唤醒事务B继续执行，此时事务B就获取到锁了。

　　

共享锁和独占锁

　　刚才上面提到的锁实际上就是Exclude独占锁，当有一个事务加了独占锁之后，此时其他事务再要更新这行数据，都是要加独占锁的，但是只能生成独占锁在后面等待。但是可以读，因为读是没有锁的，通过mvcc直接读取快照数据。

　　

共享锁：LOCK in SHARE MODE 独占锁：for UPDATE

读锁能够对读请求共享，写锁具有排他性。
（1）当我们对一行数据开启事务后加读锁，我们还能再次加读锁；若是加写锁就会因为锁占用而出现等待，只有事务commit后才能加锁。

　　

 （2） 我们对一行数据加写锁后，只有事务commit后才能加其他锁(读锁/写锁)。

　　

于是可以总结出如下规律：

　　

死锁

　　比如这两个事务，都在等待对方事务执行完毕才能获取到当前记录的锁。此时就会陷入等待状态造成死锁。这种情况一般有以下策略：
　　1. 直接进入等待，直到超时。innodb_lock_wait_timeout 默认是 50S，也就是说被锁住的线程 50S 之后才会超时退出、然后被其他线程执行。你也可以设置短一点、比如 5S 左右，不过这种方法一般都不会用。
　　2. 将参数 innodb_deadlock_detect 设置为 on(默认已开启)，就是说发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。只是这样比较消耗cpu。
　　3. 控制并发、减少事务并行。
　　4. 优化sql，尽量保证sql一次只会持有一条数据的锁。

锁案例

数据库脚本

CREATE TABLE `test`  (
  `id` int(11) NOT NULL,
  `a` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `b` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `c` int(255) NULL DEFAULT NULL,
  `d` datetime(0) NULL DEFAULT NULL,
  `e` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `f` bigint(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `b`(`b`) USING BTREE,
  INDEX `c`(`c`) USING BTREE,
  INDEX `d`(`d`) USING BTREE,
  INDEX `e`(`e`) USING BTREE,
  INDEX `f`(`f`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (4, '4', '4', 4, '2021-11-14 22:24:53', '4', 4);
INSERT INTO `test` VALUES (8, '8', '8', 8, '2021-11-14 22:15:01', '8', 8);
INSERT INTO `test` VALUES (12, '12', '12', 12, '2021-11-14 22:15:01', NULL, 12);
INSERT INTO `test` VALUES (16, '16', '16', 16, '2021-11-14 22:15:01', NULL, 16);
INSERT INTO `test` VALUES (20, '20', '20', 20, '2021-11-14 22:15:01', NULL, 20);
INSERT INTO `test` VALUES (24, '24', '24', 24, '2021-11-14 22:15:01', NULL, 24);
INSERT INTO `test` VALUES (28, '28', '28', 28, '2021-11-14 22:15:01', NULL, 28);
INSERT INTO `test` VALUES (32, '32', '32', 32, '2021-11-14 22:15:01', NULL, 32);
INSERT INTO `test` VALUES (36, '36', '36', 36, '2021-11-14 22:15:01', NULL, 36);
INSERT INTO `test` VALUES (40, '40', '40', 40, '2021-11-14 22:15:01', NULL, 40);
INSERT INTO `test` VALUES (44, '44', '44', 44, '2021-11-14 22:15:01', NULL, 44);
INSERT INTO `test` VALUES (48, '48', '48', 48, '2021-11-14 22:15:01', NULL, 48);
INSERT INTO `test` VALUES (52, '52', '52', 52, '2021-11-14 22:15:01', NULL, 52);
INSERT INTO `test` VALUES (56, '56', '56', 56, '2021-11-14 22:15:01', NULL, 56);
INSERT INTO `test` VALUES (60, '60', '60', 60, '2021-11-14 22:15:01', NULL, 60);

把事务锁超时时间设置短一点

表锁

　表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单。最常使用的MYISAM与INNODB都支持表级锁定。不通过索引条件检索数据，InnoDB就会使用表锁(即upadte没命中索引时触发，insert 和 delete不会触发)。

　　字段a是没有索引的，所以第一个方法直接锁住了表，其他sql想要来操作这个表就直接被锁住了

行锁

　　行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。只有通过索引条件检索数据，InnoDB才使用行级锁。(即upadte命中索引时触发，insert 和 delete不会触发)。

　　字段b是有索引的，所以第一个方法直接锁住了b=20的那行数据，只有此时其他线程来操作这条数据才会死锁。

间隙锁

 　　RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

　　比如有 1  4  7  9 四条数据，我where id <10,则会给这4个数据加上记录锁，给2 3 5 等不存在的数据加上间隙锁 。比如现在数据库里面有 16  20 和 24 三条数据，update b = 20,他会先找出上限(也就是自己 20)，下限24，锁住范围就是20~24(20是上限，但也是自身的值，所以这里才会包含20)，其中20  24  是记录锁，21  22 是间隙锁；update b = 22，上下限也还是 20 24 ，但是锁住的范围就成了21~24(不包含上限)。

　　快照读(select)是undolog+mvcc解决的幻读，查的是之前的数据; 当前读(update)是加间隙锁，查的是最新的数据。

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

 　　等值查唯一索引间隙锁的话，就会退化成行锁。所以我们用唯一索引或者主键就不会有这种问题。

 

。

posted @ 2021-02-26 13:03  吴磊的  阅读(222)  评论(0)  编辑  收藏  举报
//生成目录索引列表