Mysql 数据库 超时和锁定

邱秋 • 2022年09月22日 • 阅读:261 • 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 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 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。 如在线抢购等业务。

我,秦始皇,打钱!