用户反馈系统部分功能无法使用,紧急排查,定位是数据库表被锁,然后进行紧急处理。这篇文章给大家讲讲遇到类似紧急状况的排查及解决过程,建议点赞收藏,以备不时之需。
用户反馈某功能页面报502错误,于是第一时间看服务是否正常,数据库是否正常。在控制台看到数据库CPU飙升,堆积大量未提交事务,部分事务已经阻塞了很长时间,基本定位是数据库层出现问题了。
查看阻塞事务列表,发现其中有锁表现象,本想利用控制台直接结束掉阻塞的事务,但控制台账号权限有限,于是通过客户端登录对应账号将锁表事务kill掉,才避免了情况恶化。
下面就聊聊,如果当突然面对类似的情况,我们该如何紧急响应?
想象一个场景,当然也是软件工程师职业生涯中会遇到的一种场景:原本运行正常的程序,某一天突然数据库的表被锁了,业务无法正常运转,那么我们该如何快速定位是哪个事务锁了表,如何结束对应的事物?
首先最简单粗暴的方式就是:重启MySQL。对的,网管解决问题的神器——“重启”。至于后果如何,你能不能跑了,要你自己三思而后行了!
重启是可以解决表被锁的问题的,但针对线上业务很显然不太具有可行性。
下面来看看不用跑路的解决方案:
遇到数据库阻塞问题,首先要查询一下表是否在使用。
如果查询结果为空,那么说明表没在使用,说明不是锁表的问题。
如果查询结果不为空,比如出现如下结果:
则说明表(test)正在被使用,此时需要进一步排查。
查看数据库当前的进程,看看是否有慢SQL或被阻塞的线程。
执行命令:
该命令只显示当前用户正在运行的线程,当然,如果是root用户是能看到所有的。
在上述实践中,阿里云控制台之所以能够查看到所有的线程,猜测应该使用的就是root用户,而笔者去kill的时候,无法kill掉,是因为登录的用户非root的数据库账号,无法 *** 作另外一个用户的线程。
如果情况紧急,此步骤可以跳过,主要用来查看核对:
如果情况紧急,此步骤可以跳过,主要用来查看核对:
看事务表INNODB_TRX中是否有正在锁定的事务线程,看看ID是否在show processlist的sleep线程中。如果在,说明这个sleep的线程事务一直没有commit或者rollback,而是卡住了,需要手动kill掉。
搜索的结果中,如果在事务表发现了很多任务,最好都kill掉。
执行kill命令:
对应的线程都执行完kill命令之后,后续事务便可正常处理。
针对紧急情况,通常也会直接 *** 作第一、第二、第六步。
这里再补充一些MySQL锁相关的知识点:数据库锁设计的初衷是处理并发问题,作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则,而锁就是用来实现这些访问规则的重要数据结构。
根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。MySQL中表级别的锁有两种:一种是表锁,一种是元数据锁(metadata lock,MDL)。
表锁是在Server层实现的,ALTER TABLE之类的语句会使用表锁,忽略存储引擎的锁机制。表锁通过lock tables… read/write来实现,而对于InnoDB来说,一般会采用行级锁。毕竟锁住整张表影响范围太大了。
另外一个表级锁是MDL(metadata lock),用于并发情况下维护数据的一致性,保证读写的正确性,不需要显式的使用,在访问一张表时会被自动加上。
常见的一种锁表场景就是有事务 *** 作处于:Waiting for table metadata lock状态。
MySQL在进行alter table等DDL *** 作时,有时会出现Waiting for table metadata lock的等待场景。
一旦alter table TableA的 *** 作停滞在Waiting for table metadata lock状态,后续对该表的任何 *** 作(包括读)都无法进行,因为它们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。如果核心表出现了锁等待队列,就会造成灾难性的后果。
通过show processlist可以看到表上有正在进行的 *** 作(包括读),此时alter table语句无法获取到metadata 独占锁,会进行等待。
通过show processlist看不到表上有任何 *** 作,但实际上存在有未提交的事务,可以在information_schema.innodb_trx中查看到。在事务没有完成之前,表上的锁不会释放,alter table同样获取不到metadata的独占锁。
处理方法:通过 select * from information_schema.innodb_trxG, 找到未提交事物的sid,然后kill掉,让其回滚。
通过show processlist看不到表上有任何 *** 作,在information_schema.innodb_trx中也没有任何进行中的事务。很可能是因为在一个显式的事务中,对表进行了一个失败的 *** 作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效,没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。
处理方法:通过performance_schema.events_statements_current找到其sid,kill 掉该session,也可以kill掉DDL所在的session。
总之,alter table的语句是很危险的(核心是未提交事务或者长事务导致的),在 *** 作之前要确认对要 *** 作的表没有任何进行中的 *** 作、没有未提交事务、也没有显式事务中的报错语句。
如果有alter table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。
关于MySQL的锁表其实还有很多其他场景,我们在实践的过程中尽量避免锁表情况的发生,当然这需要一定经验的支撑。但更重要的是,如果发现锁表我们要能够快速的响应,快速的解决问题,避免影响正常业务,避免情况进一步恶化。所以,本文中的解决思路大家一定要收藏或记忆一下,做到有备无患,避免突然状况下抓瞎。
MySQL 中有哪些锁?数据库中锁的设计初衷处理并发问题,作为多用户共享资源,当出现并发访问的时候,数据库需要合理控制资源访问规则。锁就是实现这些访问规则中的重要数据。
锁的分类根据加锁范围,MySQL 里面的锁可以分成 全局锁 、 表级锁 、 行锁 三类。
全局锁全局锁,就是对整个数据库实例加锁,MySQL 提供了一个加全局读锁的方法,命令是:
Flush tables with read lock (FTWRL)当需要整个库只读状态的时候,可以使用这个命令,之后其他线程的:数据更新语句(增删改),数据定义语句(建表,修改表结构)和更新事务的提交语句将会被阻塞。
全局锁的使用场景全局锁的定型使用场景,做 全库逻辑备份 。也就是把整个库每个表都 Select 出来,然后存成文本。
如何整个库都只读,会有什么问题? 如果你在主库上备份,那么在备份期间都不能执行更想,业务就基本上停摆。 如果在从库上备份,那么备份期间从库不能执行主库同步过来的 binlog ,会导致从延迟。 既然要全库只读, 为什么不使用set global readonly=true的方式呢?readonly 方式也可以让全库进入只读状态,但我还是会建议你用FTWRL方式, 主要有两个原因:
一是, 在有些系统中, readonly的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改global变量的方式影响面更大, 我不建议你使用。 二是, 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开, 那么MySQL会自动释放这个全局锁, 整个库回到可以正常更新的状态。而将整个库设置为readonly之后, 如果客户端发生异常, 则数据库就会一直保持readonly状态, 这样会导致整个库长时间处于不可写状态, 风险较高 表级别锁MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lok, MDL)。表锁的语法是 :
lock tables ... read/write与 FTWRL 类似,可以使用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意的是,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的 *** 作对象。
MDL 表级锁MDL 不需要显示使用,在访问一个表的时候自动加上, MDL 保证读写的正确性,也就是说在查询数据时,不允许有其他线程对这个表结构做变更。
什么 *** 作会加 MDL 锁?在MySQL 5.5版本中引入了MDL, 当对一个表做增删改查 *** 作的时候,加 MDL读锁 ;当要对表做结构变更 *** 作的时候,加 MDL写锁 。
读锁之间不互斥,因此可以有多个线程同时对一张表增删改查。 读写之间、写锁之间是互斥的,用来保证变更表结构 *** 作的安全性,如果有两个线程要同时给一个表加字段,其中一个要等另外一个执行完才能执行。 更改表结构要注意哪些?给一个表加字段, 或者修改字段, 或者加索引, 需要扫描全表的数据。在对大表 *** 作的时候, 你肯定会特别小心, 以免对线上服务造成影响。而实际上, 即使是小表, *** 作不慎也会出问题,导致整个库的线程爆满。
举个例子我们来看一下下面的 *** 作序列, 假设表t是一个小表。
image
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锁, 在语句执行开始时申请, 但是语句结束后并不会马上释放, 而会等到整个事务提交后再释放。
怎么解决这个 更改表结构问题比较理想的机制是, 在alter table语句里面设定等待时间, 如果在这个指定的等待时间里面能够拿到MDL写锁最好, 拿不到也不要阻塞后面的业务语句, 先放弃。
ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...加锁情况与死锁原因分析
为方便大家复现,完整表结构和数据如下:
CREATE TABLE `t3` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB
insert into t3 values(1,1),(15,15),(20,20)
在 session1 执行 commit 的瞬间,我们会看到 session2、session3 的其中一个报死锁。这个死锁是这样产生的:
1. session1 执行 delete 会在唯一索引 c2 的 c2 = 15 这一记录上加 X lock(也就是在MySQL 内部观测到的:X Lock but not gap);
2. session2 和 session3 在执行 insert 的时候,由于唯一约束检测发生唯一冲突,会加 S Next-Key Lock,即对 (1,15] 这个区间加锁包括间隙,并且被 seesion1 的 X Lock 阻塞,进入等待;
3. session1 在执行 commit 后,会释放 X Lock,session2 和 session3 都获得 S Next-Key Lock;
4. session2 和 session3 继续执行插入 *** 作,这个时候 INSERT INTENTION LOCK(插入意向锁)出现了,并且由于插入意向锁会被 gap 锁阻塞,所以 session2 和 session3 互相等待,造成死锁。
死锁日志如下:
请点击输入图片描述
INSERT INTENTION LOCK
在之前的死锁分析第四点,如果不分析插入意向锁,也是会造成死锁的,因为插入最终还是要对记录加 X Lock 的,session2 和 session3 还是会互相阻塞互相等待。
但是插入意向锁是客观存在的,我们可以在官方手册中查到,不可忽略:
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.插入意向锁其实是一种特殊的 gap lock,但是它不会阻塞其他锁。假设存在值为 4 和 7 的索引记录,尝试插入值 5 和 6 的两个事务在获取插入行上的排它锁之前使用插入意向锁锁定间隙,即在(4,7)上加 gap lock,但是这两个事务不会互相冲突等待。
当插入一条记录时,会去检查当前插入位置的下一条记录上是否存在锁对象,如果下一条记录上存在锁对象,就需要判断该锁对象是否锁住了 gap。如果 gap 被锁住了,则插入意向锁与之冲突,进入等待状态(插入意向锁之间并不互斥)。总结一下这把锁的属性:
1. 它不会阻塞其他任何锁;
2. 它本身仅会被 gap lock 阻塞。
在学习 MySQL 过程中,一般只有在它被阻塞的时候才能观察到,所以这也是它常常被忽略的原因吧...
GAP LOCK
在此例中,另外一个重要的点就是 gap lock,通常情况下我们说到 gap lock 都只会联想到 REPEATABLE-READ 隔离级别利用其解决幻读。但实际上在 READ-COMMITTED 隔离级别,也会存在 gap lock ,只发生在:唯一约束检查到有唯一冲突的时候,会加 S Next-key Lock,即对记录以及与和上一条记录之间的间隙加共享锁。
通过下面这个例子就能验证:
请点击输入图片描述
这里 session1 插入数据遇到唯一冲突,虽然报错,但是对 (15,20] 加的 S Next-Key Lock 并不会马上释放,所以 session2 被阻塞。另外一种情况就是本文开始的例子,当 session2 插入遇到唯一冲突但是因为被 X Lock 阻塞,并不会立刻报错 “Duplicate key”,但是依然要等待获取 S Next-Key Lock 。
有个困惑很久的疑问:出现唯一冲突需要加 S Next-Key Lock 是事实,但是加锁的意义是什么?还是说是通过 S Next-Key Lock 来实现的唯一约束检查,但是这样意味着在插入没有遇到唯一冲突的时候,这个锁会立刻释放,这不符合二阶段锁原则。这点希望能与大家一起讨论得到好的解释。
如果是在 REPEATABLE-READ,除以上所说的唯一约束冲突外,gap lock 的存在是这样的:
普通索引(非唯一索引)的S/X Lock,都带 gap 属性,会锁住记录以及前1条记录到后1条记录的左闭右开区间,比如有[4,6,8]记录,delete 6,则会锁住[4,8)整个区间。
对于 gap lock,相信 DBA 们的心情是一样一样的,所以我的建议是:
1. 在绝大部分的业务场景下,都可以把 MySQL 的隔离界别设置为 READ-COMMITTED;
2. 在业务方便控制字段值唯一的情况下,尽量减少表中唯一索引的数量。
锁冲突矩阵
前面我们说的 GAP LOCK 其实是锁的属性,另外我们知道 InnoDB 常规锁模式有:S 和 X,即共享锁和排他锁。锁模式和锁属性是可以随意组合的,组合之后的冲突矩阵如下,这对我们分析死锁很有帮助:
请点击输入图片描述
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)