Mysql 数据库超时和锁定

chuchur

发布于 2022-10-25 15:01:20

5K0

发布于 2022-10-25 15:01:20

文章被收录于专栏：禅境花园

问题

昨天项目中遇到部分服务一直是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 tables

lock 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 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如在线抢购等业务。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022/09/22 ，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server

数据库

sql

登录后参与评论

0 条评论

热度