前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql 数据库 超时和锁定

Mysql 数据库 超时和锁定

作者头像
chuchur
发布2022-10-25 15:01:20
5K0
发布2022-10-25 15:01:20
举报
文章被收录于专栏:禅境花园

问题

昨天项目中遇到部分服务一直是pending状态,排查了代码和重启了服务都没能解决问题,于是从数据库开始排查。

尝试着执行select 对应的表, 果然,超时了,最后 通过排查 processlist ,找到阻塞的线程id, kill掉,零时解决了问题

数据库服务不能直接重启,万一会有其它可能性的问题,停了就起不来了,整个服务都挂了。

现在来复盘下问题的原因。

复盘锁超时

测试表 test ,分别执行两个事务:

代码语言:javascript
复制
## 事务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

查看线程:

代码语言:javascript
复制
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:

代码语言:javascript
复制
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

代码语言:javascript
复制
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。 之后可以对这个事务对应的功能进行降级,处理完后在重新上线。

代码语言:javascript
复制
mysql> kill 9;
Query OK, 0 rows affected (0.00 sec)

排查步骤和辅助SQL

代码语言:javascript
复制
# 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,它依然会造成锁表。

代码语言:javascript
复制
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  ;
代码语言:javascript
复制
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 提供了一个加全局读锁的方法

代码语言:javascript
复制
flush tables with read lock; # 加锁
unlock tables; #解锁

当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

全局锁的典型使用场景是,做全库逻辑备份。 ** 也就是把整库每个表都 select 出来存成文本

风险: 1.如果在主库备份,在备份期间不能更新,业务停摆 2.如果在从库备份,备份期间不能执行主库同步的binlog,导致主从延迟

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。

全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库,建议你选择使用 –single-transaction 参数,对应用会更友好。

表锁

表锁是在Server层实现的。ALTER TABLE之类的语句会使用表锁,忽略存储引擎的锁机制。 加读锁(共享锁):所有人只能读不能写 加写锁(排他锁):加锁的人可以写可以读

代码语言:javascript
复制
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 :需要手工开启, 开启步骤如下:

代码语言:javascript
复制
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

永久生效 在配置文件中设置

代码语言:javascript
复制
[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 删除

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

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

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题
  • 复盘锁超时
    • 排查步骤和辅助SQL
    • 其它的锁
      • 全局锁
        • 表锁
          • MDL锁
            • 行锁
            相关产品与服务
            云数据库 SQL Server
            腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
            领券
            问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档