文章目录
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/67d1b329ed26b691bf1f21766b3a2853.png)
MySQL如果按 锁的颗粒度划分,可以分为MySQL三大锁:全局锁,表级锁,行锁三大类。
最后一行是 Next-Key Lock :间隙锁+行锁
Mysql 三大锁
全局锁
加全局锁
flush tables with read lock
释放全局锁
unlock tables
全局锁 是对整个数据库实例加锁,加全局读锁的命令是:Flush tables with read lock (FTWRL),使用这个命令后,整个数据库处于只读状态,会阻塞其它线程的以下语句:1、对数据的增删改操作,比如 insert、delete、update等语句;2、对表结构的更改操作,比如 alter table、drop table 等语句。
全局锁的典型使用场景是,做全库逻辑备份,MyISAM中使用全局锁做全局逻辑备份,MySQL使用其它方式 mysqldump
全局锁的问题:
-
会阻塞其它线程的更新、删除、修改操作,影响业务执行。
-
(读写分离的情况,就是主表用来 增删改,从表用到查询,从表会接收主表的binlog来增删改数据)
- 在主库备份时,不能执行更新,业务基本停止
- 在从库备份时,从库不能执行主库发过来的binlog,造成
主从延迟
问题的解决:
全局锁加上后,整个数据库都是只读模式,会影响业务,但是对于 MySQL这样支持 可重复读的隔离级别的引擎来说有其它更好解决的解决办法
- 在备份数据库之前先开启事务,会先创建 Read View,然后整个事务执行期间都在用这个 Read View,而且由于 MVCC 的支持,备份期间业务依然可以对数据进行更新操作。因为在可重复读的隔离级别下,即使其他事务更新了表的数据,也不会影响备份数据库时的 Read View,这就是事务四大特性中的隔离性,这样备份期间备份的数据一直是在开启事务时的数据。
数据库备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。
但有些事务的引擎不支持事务比如 MyISAM,这种情况我们就只能使用FTWRL命令了
既然要全库只读,为什么不使用 set global readonly=true 的方式呢?
建议你用 FTWRL 方式,主要有两个原因:
- 在有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改 global 变量的方式影响面更大,不建议你使用。
- 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于不可写状态,风险较高。
表级锁
MySQL中的表级别的锁:
- 表锁;
- 元数据锁(MDL);
- 意向锁;
- AUTO-INC 锁;
表锁
表锁语法:lock tables … read / write
如果我们想对学生表(t_student)加表锁,可以使用下面的命令:
//表级别的共享锁,也就是读锁;
lock tables t_student read;
//表级别的独占锁,也就是写锁;
lock tables t_stuent write;
需要注意的是,表锁除了会限制别的线程的读写外,也会限制本线程接下来的读写操作。
也就是说如果本线程对学生表加了「共享表锁」,那么本线程接下来如果要对学生表执行写操作的语句,是会被阻塞的,当然其他线程对学生表进行写操作时也会被阻塞,直到锁被释放。(和全局锁很像,全局锁是锁所有的表,表锁是锁一张表)
要释放表锁,可以使用下面这条命令,会释放当前会话的所有表锁:
主动释放锁
unlock tables
或 客服端断开的时候自动释放
举例:线程 A 中执行 lock tables t1 read, t2 write;
-
其它线程写 t1,读写 t2的语句会被阻塞,同时A线程也只能执行读 t1 和 读写 t2,但无法写 t1
-
写是排他锁,写锁意味着其他线程不能读也不能写。读锁是共享锁,加上后其他锁只能读不能写,本线程也不能写
如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。
对于InnoDB 这种支持行锁的引擎,一般不使用lock tables,因为它的影响面还是太大,我们需要用更细粒度的锁。
元数据锁(MDL)
meta data lock
MDL 元数据锁
DML 数据操纵语言
DDL 数据定义语言
MDL 不需要显示操作,访问表的时候会自动加上。
对一张表进行 CRUD 操作时,自动加的是 MDL 读锁;
对一张表做结构变更操作的时候,自动加的是 MDL 写锁;
读锁之间不互斥,读写之间和写写之间互斥,所谓互斥就是不能同时发生,只有一个等另一个执行完才能开始执行
- 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。
上面看着没有问题,但我们需要知道一点:MDL 读锁和写锁在事务提交后才释放,在语句开始时,自动加上
所有下面这种情况就会出问题:
sessionA 事务开始,然后会对这个表加一个MDL读表,select查询,然后sessionB的select查询,到这都没有问题,但是之后的sessionC会被阻塞,因为现在的读锁还没有释放,而sessionC需要写锁,但读写互斥,加不进来,所有只能被阻塞。
现在这个线程阻塞,其它线程不能读写,如果后面来的select语句频繁,还有session查询进来,这个库可能就很快爆满了。
为什么线程 C 因为申请不到 MDL 写锁,而导致后续的申请读锁的查询操作也会被阻塞?
这是因为申请 MDL 锁的操作会形成一个队列,队列中写锁获取优先级高于读锁,一旦出现 MDL 写锁等待,会阻塞后续该表的所有 CRUD 操作。
所以为了能安全的对表结构进行变更,在对表结构变更前,先要看看数据库中的长事务,是否有事务已经对表加上了 MDL 读锁,如果可以考虑 kill 掉这个长事务,然后再做表结构的变更。
如何安全地给小表加字段(sessionC)?
首先我们要解决长事务,事务不提交,就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行,要考虑先暂停 DDL,或者 kill 掉这个长事务。
但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。MariaDB 已经合并了 AliSQL 的这个功能,所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
一个小问题:
备份一般都会在备库上执行,你在用–single-transaction 方法做逻辑备份的过程中,如果主库上的一个小表做了一个 DDL,比如给一个表上加了一列。这时候,从备库上会看到什么现象呢?
意向锁
- 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前,需要先在表级别加上一个「意向共享锁」;
- 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前,需要先在表级别加上一个「意向独占锁」;
也就是,当执行插入、更新、删除操作,需要先对表加上「意向独占锁」,然后对该记录加独占锁。
而普通的 select 是不会加行级锁的,普通的 select 语句是利用 MVCC 实现一致性读,是无锁的。
不过,select 也是可以对记录加共享锁和独占锁的,具体方式如下:
注意:意向锁是在表级别,而下面两个sql 在在行级别加锁
//先在表上加上意向共享锁,然后对读取的记录加共享锁
select ... lock in share mode;
//先在表上加上意向独占锁,然后对读取的记录加独占锁
select ... for update;
意向共享锁和意向独占锁是表级锁,不会和行级的共享锁和独占锁发生冲突,而且意向锁之间也不会发生冲突,只会和共享表锁(lock tables … read)和独占表锁(lock tables … write)发生冲突。
表锁和行锁是满足读读共享、读写互斥、写写互斥的。
如果没有「意向锁」,那么加「独占表锁」时,就需要遍历表里所有记录,查看是否有记录存在独占锁,这样效率会很慢。
那么有了「意向锁」,由于在对记录加独占锁前,先会加上表级别的意向独占锁,那么在加「独占表锁」时,直接查该表是否有意向独占锁,如果有就意味着表里已经有记录被加了独占锁,这样就不用去遍历表里的记录。
所以,意向锁的目的是为了快速判断表里是否有记录被加锁。给表锁当辅助的
AUTO-INC 锁
我们在把表的主键设置为自增之后,在不指定主键的情况下,数据库会自动给主键赋递增的值,这就是通过AUTO-INC 锁实现的
它是特殊的表锁机制,不是在事务提交之后才释放,而是在执行完插入语句之后立即释放。
在插入时,加这个锁,然后主键字段赋值 递增,等插入语句执行完之后,锁就会释放掉
但是这种方式还是有一些问题,我们加AUTO-INC 锁之后,会阻塞其它事务的插入语句,尤其当有大量数据插入的时候,这个问题会更严重,更加影响性能,所以MySQL推出了轻量级锁,我们可以通过系统变量innodb_autoinc_lock_mode 来控制选 AUTO-INC 锁(设置为0),还是轻量级的锁(设置为2)。
轻量级的锁:给AUTO_INCREMENT 修饰的字段加锁,该字段赋值 自增的值之后,轻量级锁就释放了,不需要等到整个插入语句执行完后才释放锁。
当 innodb_autoinc_lock_mode = 2 时,并且 binlog_format = row,既能提升并发性,又不会出现数据一致性问题。
行锁
InnoDB支持行锁,但MyISAM不支持。
普通的 select 语句是不会对记录加锁的,因为它属于快照读。如果要在查询时对记录加行锁,可以使用下面这两个方式,这种查询会加锁的语句称为锁定读(当前读)。
//对读取的记录加共享锁 S锁
select ... lock in share mode;
//对读取的记录加独占锁 X锁
select ... for update;
共享锁(S锁)满足读读共享,读写互斥。独占锁(X锁)满足写写互斥、读写互斥。
行级锁的类型主要有三类:
- Record Lock,记录锁,也就是仅仅把一条记录锁上;
- Gap Lock,间隙锁,锁定一个范围,但是不包含记录本身;
- Next-Key Lock:Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。
Record Lock 记录锁
Record Lock 称为记录锁,锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的:
- 当一个事务对一条记录加了 S 型记录锁后,其他事务也可以继续对该记录加 S 型记录锁(S 型与 S 锁兼容),但是不可以对该记录加 X 型记录锁(S 型与 X 锁不兼容);
- 当一个事务对一条记录加了 X 型记录锁后,其他事务既不可以对该记录加 S 型记录锁(S 型与 X 锁不兼容),也不可以对该记录加 X 型记录锁(X 型与 X 锁不兼容)。
eg,当一个事务执行了下面这条语句:
mysql > begin;
mysql > select * from t_test where id = 1 for update;
就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁,这样其他事务就无法对这条记录进行修改了。
当事务执行 commit 后,事务过程中生成的锁都会被释放。
Gap Lock 间隙锁
Gap Lock 称为间隙锁,只存在于可重复读隔离级别,目的是为了解决可重复读隔离级别下幻读的现象。
假设,表中有一个范围 id 为(3,5)间隙锁,那么其他事务就无法插入 id = 4 这条记录了,这样就有效的防止幻读现象的发生。
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁,但是并没有什么区别,间隙锁之间是兼容的,即两个事务可以同时持有包含共同间隙范围的间隙锁,并不存在互斥关系,因为间隙锁的目的是防止插入幻影记录而提出的。
Next-Key Lock 临键锁
Next-Key Lock 称为临键锁,可能S锁可能X锁,主要看Record Lock的类型,是 Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。(保护的比间隙锁更全面)
假设,表中有一个范围 id 为(3,5] 的 next-key lock,那么其他事务即不能插入 id = 4 记录,也不能修改 id = 5 这条记录。
所以,next-key lock 即能保护该记录,又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。
next-key lock 是包含间隙锁+记录锁的,如果一个事务获取了 X 型的 next-key lock,那么另外一个事务在获取相同范围的 X 型的 next-key lock 时,是会被阻塞的。
虽然相同范围的间隙锁是多个事务相互兼容的,但对于记录锁Record Lock,我们是要考虑 X 型与 S 型关系,X 型的记录锁与 X 型的记录锁是冲突的。
插入意向锁
一个事务在插入一条记录的时候,需要判断插入位置是否已被其他事务加了间隙锁(next-key lock 也包含间隙锁)。
如果有的话,插入操作就会发生阻塞,直到拥有间隙锁的那个事务提交为止(释放间隙锁的时刻),在阻塞期间会生成一个插入意向锁,表明有事务想在某个区间插入新记录,但是现在处于等待状态。
举个例子,假设事务 A 已经对表加了一个范围 id 为(3,5)间隙锁。
当事务 A 还没提交的时候,事务 B 向该表插入一条 id = 4 的新记录,这时会判断到插入的位置已经被事务 A 加了间隙锁,于是事物 B 会生成一个插入意向锁,然后将锁的状态设置为等待状态(PS:MySQL 加锁时,是先生成锁结构,然后设置锁的状态,如果锁状态是等待状态,并不是意味着事务成功获取到了锁,只有当锁状态为正常状态时,才代表事务成功获取到了锁),此时事务 B 就会发生阻塞,直到事务 A 提交了事务。
插入意向锁名字虽然有意向锁,但是它并不是意向锁,它是一种特殊的间隙锁,属于行级别锁。
如果说间隙锁锁住的是一个区间,那么「插入意向锁」锁住的就是一个点。因而从这个角度来说,插入意向锁确实是一种特殊的间隙锁。
插入意向锁与间隙锁的另一个非常重要的差别是:尽管「插入意向锁」也属于间隙锁,但两个事务却不能在同一时间内,一个拥有间隙锁,另一个拥有该间隙区间内的插入意向锁(当然,插入意向锁如果不在间隙锁区间内则是可以的)。
两阶段锁协议
![](https://i-blog.csdnimg.cn/blog_migrate/12d4022d06dad297ef4d494dd1c077df.png)
上面两条语句是怎么执行的呢?
首先事务A在执行完两条update语句之后,还会持有行锁,在commit之后才释放。
在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。这样可以最大程度的减少事务之间的锁等待,提高并发度
死锁和死锁检测
当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都会进入无限等待的状态,称为死锁。
![](https://i-blog.csdnimg.cn/blog_migrate/85975cc636c85757c06a7b481b79517f.png)
事务A和事务B都在互相等待对方的资源释放,就进入到死锁状态。
此时有两个策略:
- 进入等待,直到超时,超时时间参数为 innodb_lock_wait_timeout
- 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其它事务得以继续执行,将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。
第一种情况,超时时间设置过长,等待时间就会太长,漫长的无法接受,如果设置过短,对于锁等待就可能误伤
所以一般使用第二种策略,主动死锁检测,在有死锁的情况下,可以很快检测出
每当一个事务被锁的时候,就要看看它所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了循环等待,也就是死锁。
死锁检测也有缺点:因为每加入一个新来的被堵住的线程,都会判断会不会是这个线程导致的死锁,这一操作的时间复杂度是O(n)。假如有1000个并发线程同时更新同一行,死锁检测就是100万量级。这期间会消耗大量CPU资源,这时就会看到,CPU利用率很高,但每秒执行不了几个事务。
那么怎么解决死锁检测消耗大量CPU资源这件事呢?
第一种方法,就是在确认一定不会发生死锁的地方,
关闭死锁检测
。但这有一定风险,如果有死锁就会出现大量的超时。
第二种方法就是
控制并发度
,如果同一时间的并发数量少,那么死锁检测的成本就减少了。并发控制要做在数据库服务端,让对于同一行的更新在进入引擎前排队,这样InnoDB内部就不会有大量的死锁检测了。
还有一种方案是,把多条记录变为一条,比如更新账户的余额,我们可以把100条记录的值相加,求出总和,再去和账户的余额相加 求出余额,这样冲突就变成了原来的 1 / 100。但这个方案需要根据具体业务做调整,比如有退票的可能,余额不能是负数