来源:
《MySQL实战45讲》
根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。
本文介绍碰到锁时的现象和其背后的原理。
1 全局锁
对整个数据库实例加锁
加全局读锁的方法:FTWRL
Flush tables with read lock
使用这个命令之后,整个库处于只读的状态,其他线程的如下语句会被阻塞:
数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句
典型场景:做全库逻辑备份,即把整个库所有表都select出来存成文本
但是FTWRL这种只读的方式必须停掉业务;
可以使用官方自带的逻辑备份⼯具mysqldump,当mysqldump使用参数–single-transaction的时候,会启动⼀个事务,确保拿到⼀致性视图,而由于MVCC的⽀持,这个过程中数据是可以正常更新的;
(–single-transaction 是⼀致性读,在开始备份前开启事务并将隔离级别设置为可重复读,所以备份期间主库上后发起的DDL在备库上不可⻅。)
这也是业务中InnoDB代替MyISAM的原因,这样就能使用–single-transaction了
2 表锁
MySQL表级别的锁有两种:一种是表锁,一种是元数据锁
表锁的语法如下
lock tables … read/write
与FTWRL类似,可以用unlock tables主动释放锁,也可以在客户端断开的时候自动释放。
需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象:
如在某个线程A中执行lock tables t1 read, t2 write; 这个语句,则其他线程写t1、读写t2的语句都会被阻塞;
同时,线程A在执行unlock tables之前,也只能执行读t1、读写t2的操作。不允许写t1和访问其他表。
InnoDB一般不使用
元数据锁MDL不需要显式使用,当对⼀个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时
候,加MDL写锁。
作用:保证读写正确性,如果⼀个查询正在遍历⼀个表中的数据,而执⾏期间另⼀个线程对这个表结构做变更,删了⼀列,那么查询线程拿到的结果跟表结构对不上,那就不行了
读锁之间不互斥,因此可以有多个线程同时对⼀张表增删改查。
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给⼀个表加字段,其中⼀个要等另⼀个执行完才能开始执行
- 问题:给小表加字段
如下图:
session A先启动,这时候会对表t加⼀个MDL读锁;
由于session B需要的也是MDL读锁,因此可以正常执行;
之后session C会被blocked,是因为session A的MDL读锁还没有释放,而session C需要MDL写锁,因此只能被阻塞;
session C之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞,因为所有对表的增删改查操作都需要先申请MDL读锁,所以都被锁住,等于这个表现在完全不可读写了;
如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起⼀个新session再请求的话,这个库的线程很快就会爆满。
可以看到,事务中的MDL锁,在语句执行开始时申请,但是语句结束后并不会⻢上释放,而会等到整个事务提交后再释放。
- 解决:如何安全地加小字段
⾸先要解决长事务,事务不提交,就会⼀直占着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 ...
3 行锁
MySQL的行锁是在引擎层由各个引擎自己实现的。
在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释
放,这个就是两阶段锁协议,如下图的执行序列,事务B的update会被阻塞,要等事务Acommit了之后,事务B的update才能继续执行:
如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
要注意死锁问题:
事务A和事务B在互相等待对方的资源释放,进⼊了死锁状态。
出现死锁以后有两种策略:
1 直接进入等待,直到超时;
这个超时时间可以通过参数innodb_lock_wait_timeout来设置,默认50s,这个时间太长了,用户无法接受,而设置太短,又可能本次不是死锁而是正常的锁等待,但是超时而被误伤,所以一般采用第二种策略2 发起死锁检测,发现死锁后,主动回滚死锁链条中的某⼀个事务,让其他事务得以继续执行;
将参数innodb_deadlock_detect设置为on,表示开启这个逻辑,默认就是on;
使用策略2时可能出现热点行更新导致的性能问题:所有事务都要更新同⼀行,每个新来的被堵住的线程,都要判断会不会由于自己的加⼊导致了死锁,这是⼀个时间复杂度是O(n)的操作;
假设有1000个并发线程要同时更新同⼀行,那么死锁检测操作就是100万这个量级的;
虽然最终检测的结果是没有死锁,但是这期间要消耗⼤量的CPU资源。因此会看到CPU利用率很⾼,但是每秒却执行不了几个事务。
解决:思路为控制访问相同资源的并发事务量
1 如果能确保这个业务⼀定不会出现死锁,可以临时把死锁检测关掉;
这种操作本身带有⼀定的风险,因为业务设计的时候⼀般不会把死锁当做⼀个严重错误,毕竟出现死锁了就回滚,然后通过业务重试⼀般就没问题了,这是业务无损的;
而关掉死锁检测意味着可能会出现大量的超时,这是业务有损的。2 控制并发度;
在客户端进行限流,但是其实也不太可行,比如有600个客户端,即使每个客户端控制到只有5个并发线程,汇总到数据库服务端以后,峰值并发数也可能要达到3000,比上面说的1000还高;
因此可以在服务端进行并发控制,也就是限流,基本思路为:对于相同行的更新,在进入引擎之前排队,这样在InnoDB内部就不会有大量的死锁检测工作了;3 在设计上进行优化:
通过将一行改为逻辑上的多行来减少冲突,汇总的时候记得合并就好,这类方案需要根据业务逻辑做详细设计
4 加锁规则
可重复读隔离级别下的规则:两个原则、两个优化、一个bug
1 原则 1:加锁的基本单位是 next-key lock。(前开后闭区间)
2 原则 2:查找过程中访问到的对象才会加锁。
3 优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
4 优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
5 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止,而不是只访问到一个就完事。
读提交级别的优化:语句执行过程中加上的行锁,在语句执行完成后,就要把“不满足条件的行”上的行锁直接释放了,不需要等到事务提交。
show engine innodb status可以看到锁信息,如是否死锁、是否锁等待
5 Insert的锁
insert … select 是很常见的在两个表之间拷贝数据的方法;
注意,在可重复读隔离级别下,这个语句会给select的表里扫描到的记录和间隙加读锁。
如果insert和select的对象是同⼀个表,则有可能会造成循环写入;
这种情况下需要引入用户临时表来做优化。
insert 语句如果出现唯⼀键冲突,会在冲突的唯⼀值上加共享的next-key lock(S锁);
因此,碰到由于唯⼀键约束导致报错后,要尽快提交或回滚事务,避免加锁时间过长。
6 如何避免死锁
可结合第3点的“解决
行锁、间隙锁可能会产生死锁,
1 注意程序的逻辑
2 保持事务的轻量
3 提高运行的速度
4 尽量快地提交事务,减少持有锁的时间