MySQL常见锁问题分析与处理

一、常见锁超时报错

对于常见的锁报错，总体来讲可以分为两大类：锁超时报错、死锁报错。

1.1 锁超时报错

当DML/DDL等操作需要获取指定锁资源而无法获取时，首先会等待对应锁资源的释放，若等待时间超过锁超时相关参数仍未获取到指定资源，则自动回滚相关事务，并返回客户端以下报错:

 Lock wait timeout exceeded; try restarting transaction; 
 nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: 
 Lock wait timeout exceeded; try restarting transaction

1.2 死锁报错

当两个及以上的事务，双方都在互相等待对方已经持有的锁，形成锁的死循环的现象称之为“死锁”。当MySQL检测到死锁发生时，会自动回滚权重较小事务，并反馈客户端以下报错：

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: 
Deadlock found when trying to get lock; try restarting transaction

二、锁超时

2.1 行锁超时

1、常见行锁超时场景

   1）并发SQL执行效率差且需要持有大量锁资源；
   2）事务未及时提交导致相关锁资源一直占用不释放。

2、数据库相关参数

   innodb_lock_wait_timeout ：行锁超时等待最大时间限制，默认为50s。

3、行锁超时的优化

   1）SQL优化，有效利用索引减少SQL执行期间需要获取的锁资源、同时优化提高SQL执行效率，及时释放相关锁资源；
   2）及时提交事务，避免长事务占用锁资源未释放；
   3）优化代码逻辑，同一事物中尽量将锁资源消耗较大SQL最后执行；

4、行锁现象示例

1）当一个事物长时间占用锁资源未释放，后续事物若需要相关锁资源时，会优先进行等待，超过innodb_lock_wait_timeout后若仍未获取到相关锁资源，MySQL会自动将后续的等待事物进行回滚处理。

2）当一个事物长时间占用锁资源未释放，后续事物若需要相关锁资源时，会优先进行等待，在innodb_lock_wait_timeout之内若前面的事物可快速释放掉相关资源，则后续的等待事务会成功获取到相关锁资源并执行成功。

2.2 表锁超时

1、常见表锁超时场景及现象

   1）手动执行lock table相关操作、mysqldump备份使用–lock-tables；
   2）业务并发超过数据库可承载能力；
   3）元数据锁等待也是表锁等待的一种
   4）数据库会话状态显示为“Waiting for table level lock”或者“Waiting for table metadata lock”。

2、元数据锁等待

对于元数据锁，我们需要知道元数据锁分为MDL写锁、MDL读锁，其兼容性如下：

兼容性	MDL写锁	MDL读锁
MDL写锁	互斥	互斥
MDL读锁	互斥	兼容

元数据锁等待出现的场景有：
   1）手动执行一些表级锁操作
   2）元数据锁的出现一般与DDL操作密不可分，比较常见的场景是大查询、长时间未释放相关资源的事务，导致与后续的DDL在获取MDL写锁时，产生锁冲突，导致后续的DDL产生元数据锁等待的现象
   3）当DDL操作无法正常快速获取到MDL写锁时，该相关表的正常DML、Query查询操作也会出现元数据锁等待的现象

3、数据库相关参数
lock_wait_timeout：表级别锁、metadata元数据锁超时等待时间，默认为31536000s。

4、表锁超时的优化

   1）尽量避免表级锁操作；
   2）高并发情况下导致的表锁等待可考虑相关SQL优化
   3）DDL变更操作尽量在业务低峰执行；
   4）DDL执行期间，关注数据库会话运行情况，避免DDL变更相关表涉及的长事务/大查询阻塞DDL操作

4、表锁现象示例

1）表锁等待超时

2）表锁在超时范围内正常释放

3）元数据锁等待

三、死锁

1、常见引发元锁等待场景及现象

   业务逻辑设计不合理，导致两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象。

2、数据库相关参数

   1）innodb_deadlock_detect : 死锁检测，默认开启
   2）innodb_print_all_deadlocks : 是否将数据库发生死锁相关日志打印至error log，默认关闭。

3、死锁的优化

   1）通过show engine innodb status或者error log
查看死锁日志
   2）分析相关事务锁资源争用，从业务逻辑层面进行优化

4、死锁现象示例

四、锁相关排查手段

4.1 查看锁等待信息的相关SQL

SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread
	, b.trx_query AS blocking_query
FROM information_schema.innodb_lock_Waits w
	JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
	JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id

waiting_trx_id: 被阻塞的事务ID
waiting_thread:被阻塞的mysql线程ID
waiting_query:被阻塞的SQL语句
blocking_trx_id: 阻塞者的事务ID
blocking_thread: 阻塞者的mysql线程ID
blocking_query: 阻塞者的SQL语句

4.2 查看当前会话长时间未提交的事务的会话

      SELECT p.id AS ID, p.user AS User, p.host AS Host, p.db AS DB, p.command AS Cmd
	, p.state AS State, p.info AS `SQL`, timediff(now(), trx.trx_started) AS Transaction_During
FROM information_schema.processlist p
	JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = p.id
ORDER BY Transaction_During DESC;