Mysql 数据库 超时和锁定
问题
昨天项目中遇到部分服务一直是pending状态，排查了代码和重启了服务都没能解决问题，于是从数据库开始排查。
尝试着执行select 对应的表， 果然，超时了,最后 通过排查 processlist ，找到阻塞的线程id, kill掉，零时解决了问题
数据库服务不能直接重启，万一会有其它可能性的问题，停了就起不来了，整个服务都挂了。
现在来复盘下问题的原因。
复盘锁超时
测试表 test ,分别执行两个事务：
## 事务1
mysql> begin;update test set phone='123' where id=1;
Query OK, 0 rows affected (0.00 sec)
## 事务2
mysql> begin;update test set phone='123' where id=1;
Query OK, 0 rows affected (0.00 sec)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction查看线程：
mysql> show processlist;
+----+-----------------+-----------+---------+---------+-------+------------------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+---------+---------+-------+------------------------+----------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 13118 | Waiting on empty queue | NULL |
| 7 | root | localhost | my_test | Sleep | 197 | | NULL |
| 8 | root | localhost | my_test | Query | 6 | updating | update test set phone='123' where id=1 |
| 11 | root | localhost | my_test | Query | 0 | init | show processlist |
+----+-----------------+-----------+---------+---------+-------+------------------------+----------------------------------------+
4 rows in set (0.00 sec)可以看到线程id为8的处于update阻塞中，同时info展示了正在阻塞的语句。 执行 information_schema.INNODB_TRX:
mysql> select * from information_schema.INNODB_TRX;
+--------+-----------+---------------------+------------------------------+---------------------+------------+---------------------+----------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight |
+--------+-----------+---------------------+------------------------------+---------------------+------------+---------------------+----------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| 45579 | LOCK WAIT | 2022-04-23 15:38:19 | 5652286592:2:4:20:4471239192 | 2022-04-23 15:38:19 | 2 | 8 | update test set phone='123' where id=1 | starting index read | 1 | 1 | 2 | 1128 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | 1 |
| 45577 | RUNNING | 2022-04-23 15:35:08 | NULL | NULL | 3 | 11 | NULL | NULL | 0 | 1 | 2 | 1128 | 1 | 1 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | NULL |
+--------+-----------+---------------------+------------------------------+---------------------+------------+---------------------+----------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
2 rows in set (0.00 sec)可以看到trx_id为45579、trx_mysql_thread_id为8的事务处于锁等待中，同时也展示了执行的语句。我们来看看锁的持有和请求情况： 执行 performance_schema.data_locks：
mysql> select * from performance_schema.data_locks;
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 5652286592:1063:4688481592 | 45579 | 51 | 16 | my_test | test | NULL | NULL | NULL | 4688481592 | TABLE | IX | GRANTED | NULL |
| INNODB | 5652286592:2:4:20:4471239192 | 45579 | 51 | 16 | my_test | test | NULL | NULL | PRIMARY | 4471239192 | RECORD | X,REC_NOT_GAP | WAITING | 1 |
| INNODB | 5652287384:1063:4688483544 | 45577 | 50 | 20 | my_test | test | NULL | NULL | NULL | 4688483544 | TABLE | IX | GRANTED | NULL |
| INNODB | 5652287384:2:4:20:4471243800 | 45577 | 50 | 20 | my_test | test | NULL | NULL | PRIMARY | 4471243800 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)可以看到trx_id为45579正在等待id为1的行锁，行锁被trx_id为45577的事务持有。 一般遇到这样的问题是因为另外一个事务出现了IO阻塞或者等待或者处理其他逻辑耗时导致事务一直没有被提交.
临时解决办法就是kill长期占用锁的事务对应的线程id，如上面的事务trx_id为45577，线程id为8。 之后可以对这个事务对应的功能进行降级，处理完后在重新上线。
mysql> kill 9;
Query OK, 0 rows affected (0.00 sec)排查步骤和辅助SQL
# 1.查看表是否在使用
show OPEN TABLES where In_use > 0;
# 2.查询正在执行的线程
show processlist;
SELECT * FROM information_schema.PROCESSLIST；
SELECT * FROM information_schema.PROCESSLIST where length(info) >0;
# 3.当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
# 4.锁的持有和请求情况/当前出现的锁
# SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; #8.0以下
select * from performance_schema.data_locks; # 8.0+版本
# 5.锁等待的对应关系
# SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; #8.0以下
select * from performance_schema.data_lock_waits; # 8.0+版本
# 6.批量删除事务表中的事务，生成kill sql
SELECT concat('KILL ',id,';')
FROM information_schema.processlist p
INNER JOIN information_schema.INNODB_TRX x
ON p.id=x.trx_mysql_thread_id
WHERE db='test'; # test 改为你的表
# 7.kill id
kill 9;实际上，复盘的过程里，如果只Kill 一个 阻塞的事务，那么第二个事务如果不提交，或者不kill，它依然会造成锁表。
SELECT
p.id,
p.time,
x.trx_id,
x.trx_state,
p.info
FROM
INFORMATION_SCHEMA.PROCESSLIST p,
INFORMATION_SCHEMA.INNODB_TRX x
WHERE
p.id = x.trx_mysql_thread_id ;mysql> SELECT p.id, p.time, x.trx_id, x.trx_state, p.info FROM INFORMATION_SCHEMA.PROCESSLIST p, INFORMATION_SCHEMA.INNODB_TRX x WHERE
p.id = x.trx_mysql_thread_id ;
+----+------+--------+-----------+-----------------------------+
| id | time | trx_id | trx_state | info |
+----+------+--------+-----------+-----------------------------+
| 8 | 3 | 23317 | LOCK WAIT | update test set phone='123' |
| 11 | 1792 | 23311 | RUNNING | NULL |
+----+------+--------+-----------+-----------------------------+
2 rows in set (0.01 sec)同时杀掉 8, 11 就可以
其它的锁
全局锁
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法
flush tables with read lock; # 加锁
unlock tables； #解锁当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。 ** 也就是把整库每个表都 select 出来存成文本
风险： 1.如果在主库备份，在备份期间不能更新，业务停摆 2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，建议你选择使用 –single-transaction 参数，对应用会更友好。
表锁
表锁是在Server层实现的。ALTER TABLE之类的语句会使用表锁，忽略存储引擎的锁机制。 加读锁(共享锁)：所有人只能读不能写 加写锁(排他锁)：加锁的人可以写可以读
lock table t1 read, t2 write
unlock tableslock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象
如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL锁
mysql8.0：默认开启，mysql 5.7 ：需要手工开启， 开启步骤如下:
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';永久生效 在配置文件中设置
[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'查看是否有MDL锁 使用show processlist; 语句查看线程信息时可能会发现State字段 值为'Waiting for table metadata lock'
MDL 不需要显式使用，在访问一个表的时候会被自动加上。 MDL 的作用是并发情况下维护数据的一致性，保证读写的正确性。(避免加字段删字段导致查询结果异常) 因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁； 当要对表做结构变更操作的时候，加 MDL 写锁。
MDL 是并发情况下维护数据的一致性，在表上有事务的时候,不可以对元数据经行写入操作,并且这个是在server层面实现的
行锁
MySQL 的行锁是在引擎层由各个引擎自己实现的。 但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。 InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。 InnoDB行锁包括 Record Lock 、 Gap Lock、 Next-Key Lock
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。 如在线抢购等业务。