Mysq系列-锁-CSDN博客

本文链接：https://blog.csdn.net/qq_37154446/article/details/132571064

Mysq系列-锁

前言

面试过程中，对于锁知识点的考察，也是经常出现在面试过程中，常见面试题大概有这么几个，小伙伴们试下是否能否回答出来吧

Mysql数据库的锁机制，锁分类
Mysql数据库的粒度
乐观锁和悲观锁是怎么实现
什么是间隙锁
给小表添加字段需要注意什么东西

Mysql设计锁这个玩意目的是处理多线程对共享资源的访问，在Mysq体系中我们知道处理并发访问共享资源的方法一般是加锁，事务，也是InnoDB和MySiam最重要的不同点，今天我们一起研究下锁相关的知识。

锁分类

全局锁

概念

全局锁就是对整个数据库实例加锁，让整个数据库只读。一但加上全局锁之后，之后其他线程的一下语句就会被阻塞:数据更新语句(数据正删改)，数据定义语句(建表，修好表的结果)，更新事务的提交语句都会阻塞在那等着吧，等我完事你们再来。

使用场景

全局锁的使用场景主要用来做全库的逻辑备份，如果不加全局锁，就会造成备份数据的一致性出现问题。因为备份系统得到的数据库不是在一个逻辑时间点上，视图逻辑不一致，小伙伴请看下面这个例子

备份系统备份余额表->用户原系统购买插入订单表->备份系统备份订单表 //对应情况是余额没扣，订单表多出一条数据
备份系统备份订单表->用户原系统购买插入订单表扣除余额->备份系统备份余额表 //对应情况是余额扣了,订单没数据，线上系统这样搞岂不要滚犊子

如何加全局锁

set global readonly=true ，数据库处于只读状态，更新数据你就进不来
Mysql官方自带的逻辑备份数据mysqldump，使用参数-single -transaction时候，会在开始时候启动一个事务，拿到一致性视图(事务下一篇我们就开始了，小伙伴这里只需有个概念，一致性视图就是事务在我之前的认，在我之后的我就不认，你在我之后更新删除没啥用，我不认你)。数据库在备份的时候，推荐这个
FTWRL(Flush table with read lock) Mysql提供的一个命令，功能就是让整个数据库处于只读状态，小伙伴肯定问了，这不是和set global readonly=true 一个意思，enen，还是有点不一样，不一样点主要有以下几点
1. 客户端发生异常的话，FTWRL会自动释放全局锁。
2. set global readonly=true数据库读写分离的时候，主库写从库读，我们在一些逻辑操作的时候可能会用这个判断是不是从库。

表级锁

概念

Mysql表级别的锁会将整个表锁住，锁粒度比较大，表级别锁主要分为两种，一种是表锁，一种是元数据锁(meta data lock,MDL)

表锁

如何加表锁

lock tables tb1 read/write;
// do something
unlock tables;//lock tables unlock tables会主动当前会话中的所有表锁

这个有个注意的点需要说下，lock tables除了会限制别的线程读写之外，也会限制本线程的一些操作，啥意思？上个例子

线程A执行 lock table t1 read,t2 write这个语句，则其他线程写t1表，读写t2的都会被阻塞
线程A执行unlock tables之前，也只能读t1，读写t2的操作，这个时候你线程A写t1也不行，自然不能访问其他表

MDL（元数据锁）

当对一个表进行增删改查操作的时候，会加MDL读锁；当对表结构变更操作的时候，会加MDL写锁
MDL的读写锁，写锁之间是相互互斥，两个线程同时给一个表添加字段，后面的要等待前面一个执行完才开始执行
MDL元数据锁数据提交才会释放，使用元数据锁的时候注意在表变更的时候会阻塞表的相关查询以及更新语句 （ps 面试时候可以唠唠的点是如何安全给小表添加字段这个例子）

业务场景：表数据量虽然小，却是一个热点表，访问频率特别高，而且该表的访问是在一个大事务中。加字段的时候一直在等待获取MDL写锁。这个等待也影响了后续表访问对MDL读锁的获取，导致后面的查询也都被堵塞了。更惨的是，客户端有重试机制，查询堵塞超过超时时间会再起一个session进行请求，导致数据库的线程池很快就爆满了，直接挂掉。

如何给小表添加字段

没加之前，解决点长事务（一直持有读锁），添加字段会一直等待
表变更语句alter table设置等待时间(没有拿到锁的时候自动释放)

查询表级锁争用

这里有两个参数可以查看到表级锁的竞争情况，我们执行下面这个命令，会出现这两个参数

show STATUS like '%table%'

Table_locks_immediate：能够立即获取表级锁的次数
Table_locks_waited：不能立即获取表级锁而需要等待的次数(这个值越大，说明表级别锁竞争越大)

行级锁

行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行。Mysql行级锁是由各个引擎实现的，并不是所以的引擎都支持行级锁，比如MyISAM引擎不支持行级锁，我们处理并发的时候只能使用表级锁，这是MySIAM被InnoDB取代的原因之一。InnoDB支持行锁，关于InnoDB行锁需要记住以下几点

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，如果索引失效情况话，行锁会升级为表锁
InnoDB事务中，行锁是需要的时候才加上，但并不是不需要了就立刻释放，需要等到事务执行结束之后才会释放，也就是我们常说的两阶段锁协议

减少行锁对性能上面的影响

两阶段锁协议一个应用：如何减少锁等待时间：将影响并发度的操作尽量往后放，假设你负责实现一个电影票在线交易业务，顾客A要在影院B购买电影票。我们简化一点，这个业务需要涉及到以下操作：

从顾客A账户余额中扣除电影票价；
给影院B的账户余额增加这张电影票价；
记录一条交易日志。
两阶段锁协议是事务提交的时候才会释放锁，我们可以将3，1,2这三个操作放在一个事务中。可能出冲突的是给影院账户余额，这个操作，所以按照3，1，2的顺序大大减少锁等待时间，提高并发

我们创建一个表,表中id不设置索引，我们通过两个窗口查询数据，我们会发现，行级锁会变成表锁

CREATE TABLE `user`  (
  `id` int(11) NOT NULL  COMMENT 'id值',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
insert INTO USER(id,age) value(1,1);
insert INTO USER(id,age) value(2,2);
insert INTO USER(id,age) value(3,3);

session A 查询id=1这一列，因为没有索引，所以行级锁变成表级锁，锁住整个表

session A

Session B查询id=2的时候，因为session A持有标记锁，session B只能等待，看那个红框，一直转圈圈

sessionB

接下来一起分析一下Mysql行级锁中的共享锁和排他锁

共享锁/排他锁

共享锁又称为读锁，多个事务进行读操作可以同时进行而不会互相影响，但是只能读不能修改
排他锁又称为写锁，一个事务获取一个数据行的排他锁，其他事务不能再获取这一行的其他锁，不论是读锁和写锁，其他事务不能在这一行上加读锁和写锁，只能当前事务对数据读取和修改，但是其他事务可以通过select 语句去查，因为select是不加任何锁的
select语句本身是默认不加任何锁的，如果需要加排他锁，可以使用select ... for update语句，加共享可以通过select ... lock in share mode
Mysql中的更新语句(update/delete/insert)会自动加上排他锁

死锁和死锁检测

并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略:

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置（超时时间设置）。
一种策略是，发起死锁检测（增加系统），发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

间隙锁

间隙锁是MySQL在RR可重复读隔离级别下用来修复幻读才引入的一种锁，间隙锁也只有在RR可重复读隔离级别下才会存在，如果是在RC读已提交隔离级别下，是没有间隙锁的存在的。另外，我们也知道，幻读这种现象也只有在当前读的时候才会发生，在一致性快照读的情况下是没有幻读现象的。

1 CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);

间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

当我们程序范围查询的请求共享和排他锁的时候，InnoDB会给符合条件的已有记录的索引项添加锁，对于键值对在范围内但是不存在的记录，InnoDB会给这个间隙添加锁，也就是我们常说的间隙锁。(PS间隙锁解决Mysql幻读问题)

如上图，给id>5中并不存在的数据加上了间隙锁，当插入id=6的数据时被阻塞了。这是一个坑：「若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响」

加锁规则

锁的两种机制

锁有两种机制，乐观锁和悲观锁，是两种常见的资源并发锁设计思路，有这两个玩意的主要为了解决数据丢失问题和脏读问题

数据丢失的情景 A先更新一条id=1的数据，B后然后也更新id=1的数据，B把A更新的数据覆盖
脏读：A，B看到的值都是10，B把10改成5，但是A看到的值还是6

乐观锁

每次别人提交访问数据的时候，认为这次访问改变数据的概率很低，不加锁，只要数据提交的时候，数据库冲突的话才会将数据锁住。这样可以避免等待长事务数据库加锁开销，提高系统的并发性，乐观锁不能解决脏读问题。

实现方式

基于版本号实现，当进行数据读操作的时候，将提交数据的版本信息和数据库对应记录的当前版本信息进行对比，若是提交的数据版本大于数据库的版本，进行更新，小于的话就认为是过期数据，数据不进行更改。
基于时间戳实现，数据库增加时间戳字段，进去数据更新的时候，比较是不是和自己开始的时候查的时间戳是否一致，如果一致代表没有其他数据进行操作，可以进行这次操作