Windard +
Github Zhihu RSS

MySQL 异常排查

引入

首先介绍一下 MySQL 的事务,和事务的四大特性。一般 MySQL 正常使用,也不会出现什么问题,主要基本上都在 事务 造成的问题。

获取锁超时

异常现场

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

一般出现这个问题的原因是因为有另一个事务获取锁,当前事务在获取锁的时候,等待超时,抛出异常,在 MySQL 中获取锁的超时时间是 50 秒。

复现一下也很简单。

首先在一个 MySQL 连接中,开启事务,然后锁定全表。

mysql> BEGIN;
mysql> select * from note for update;

然后在另一个 链接中,更新字段,就会等待直到超时最终报错。

mysql> update note set content="改一下-确实很简朴" where id = 9;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在事务中 rollback 回滚,commit 提交,都可以结束当前事务。

如何解决

查进程

找到获取锁的进程 show full processlist;

mysql> show full processlist;
+----+-----------------+-----------+----------+---------+--------+------------------------+-----------------------+
| Id | User            | Host      | db       | Command | Time   | State                  | Info                  |
+----+-----------------+-----------+----------+---------+--------+------------------------+-----------------------+
|  4 | event_scheduler | localhost | NULL     | Daemon  | 102717 | Waiting on empty queue | NULL                  |
| 11 | root            | localhost | nodetodo | Query   |      0 | starting               | show full processlist |
| 12 | root            | localhost | nodetodo | Sleep   |    200 |                        | NULL                  |
+----+-----------------+-----------+----------+---------+--------+------------------------+-----------------------+
3 rows in set (0.00 sec)

可以看到 12 号进程也是在 nodetodo 表上占用,而且占用时间过长 kill 12 就好了。

查事务

查看当前活动事务,找到长期持有未释放锁的事务 select * from 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 |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 5647            | RUNNING   | 2020-06-09 20:13:22 | NULL                  | NULL             |          2 |                  12 | NULL                                        | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 281479655995944 | RUNNING   | 2020-06-09 20:22:37 | NULL                  | NULL             |          0 |                  11 | select * from information_schema.innodb_trx | NULL                |                 0 |                 0 |                0 |                  1136 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.04 sec)

或者换一个展示方式 和上面👆是同样的数据

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 5647
                 trx_state: RUNNING
               trx_started: 2020-06-09 20:13:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 12
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 281479655995944
                 trx_state: RUNNING
               trx_started: 2020-06-09 20:22:37
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 11
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

字段详解

  1. trx_id: 唯一事务id号,只读事务和非锁事务是不会创建id的。
  2. TRX_WEIGHT: 事务的高度,代表修改的行数(不一定准确)和被事务锁住的行数。为了解决死锁,innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高,即使改变的行和锁住的行比其他事务低。
  3. TRX_STATE: 事务的执行状态,值一般分为: RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
  4. TRX_STARTED: 事务的开始时间
  5. TRX_REQUESTED_LOCK_ID: 如果trx_state是lockwait,显示事务当前等待锁的id,不是则为空。想要获取锁的信息,根据该lock_id,以innodb_locks表中lock_id列匹配条件进行查询,获取相关信息。
  6. TRX_WAIT_STARTED: 如果trx_state是lockwait,该值代表事务开始等待锁的时间;否则为空。
  7. TRX_MYSQL_THREAD_ID: mysql线程id。想要获取该线程的信息,根据该thread_id,以INFORMATION_SCHEMA.PROCESSLIST表的id列为匹配条件进行查询。
  8. TRX_QUERY: 事务正在执行的sql语句。
  9. TRX_OPERATION_STATE: 事务当前的操作状态,没有则为空。
  10. TRX_TABLES_IN_USE: 事务在处理当前sql语句使用innodb引擎表的数量。
  11. TRX_TABLES_LOCKED: 当前sql语句有行锁的innodb表的数量。(因为只是行锁,不是表锁,表仍然可以被多个事务读和写)
  12. TRX_LOCK_STRUCTS: 事务保留锁的数量。
  13. TRX_LOCK_MEMORY_BYTES: 在内存中事务索结构占得空间大小。
  14. TRX_ROWS_LOCKED: 事务行锁最准确的数量。这个值可能包括对于事务在物理上存在,实际不可见的删除标记的行。
  15. TRX_ROWS_MODIFIED: 事务修改和插入的行数
  16. TRX_CONCURRENCY_TICKETS: 该值代表当前事务在被清掉之前可以多少工作,由 innodb_concurrency_tickets系统变量值指定。
  17. TRX_ISOLATION_LEVEL: 事务隔离等级。
  18. TRX_UNIQUE_CHECKS: 当前事务唯一性检查启用还是禁用。当批量数据导入时,这个参数是关闭的。
  19. TRX_FOREIGN_KEY_CHECKS: 当前事务的外键坚持是启用还是禁用。当批量数据导入时,这个参数是关闭的。
  20. TRX_LAST_FOREIGN_KEY_ERROR: 最新一个外键错误信息,没有则为空。
  21. TRX_ADAPTIVE_HASH_LATCHED: 自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区,一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制,默认值为8。
  22. TRX_ADAPTIVE_HASH_TIMEOUT: 是否为了自适应hash索引立即放弃查询锁,或者通过调用mysql函数保留它。当没有自适应hash索引冲突,该值为0并且语句保持锁直到结束。在冲突过程中,该值被计数为0,每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区(由 innodb_adaptive_hash_index_parts参数控制),值保持为0。
  23. TRX_IS_READ_ONLY: 值为1表示事务是read only。
  24. TRX_AUTOCOMMIT_NON_LOCKING: 值为1表示事务是一个select语句,该语句没有使用for update或者shared mode锁,并且执行开启了autocommit,因此事务只包含一个语句。当TRX_AUTOCOMMIT_NON_LOCKING和TRX_IS_READ_ONLY同时为1,innodb通过降低事务开销和改变表数据库来优化事务。

根本原因

还是有事务开启长期占用锁未提交,开启 autocommit 或者在每个事务中都要求必须手动 commit

查看是否开启 autocommit

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

如果为 0 的话需要开启 set global autocommit=1;

MySQL 字段长度

以 int 为例,tinyint 是 1个字节,smallint 是 2个字节,int 是 4个字段,bigint 是 8个字节。所以其存储的数据大小是根据其存储空间决定的。

Type Storage (Bytes) Minimum Value Signed Minimum Value Unsigned Maximum Value Signed Maximum Value Unsigned
TINYINT 1 -128 0 127 255
SMALLINT 2 -32768 0 32767 65535
MEDIUMINT 3 -8388608 0 8388607 16777215
INT 4 -2147483648 0 2147483647 4294967295
BIGINT 8 -2^63 0 2^63-1 2^64-1

以 smallint 为例,如果你在创建表的时候,未指定是 unsigned 字段,那么如果存入 65535 的值,就会报错。

> UPDATE xxxx_table_name t SET t.yyyy_cloum_name = 65535 WHERE t.id = 1
Data truncation: Out of range value for column 'yyyy_cloum_name' at row 1

如果需要更大的字段范围,需要创建时指定

alter table xxxx_table_name 
add column yyyy_cloum_name smallint unsigned NOT NULL DEFAULT 0 ;

参考链接

MySQL事务锁等待超时 Lock wait timeout exceeded; try restarting transaction
MySql Lock wait timeout exceeded该如何处理?
[Mysql] mysql innodb_trx参数详解 2018-09-10
MySql 死锁时的一种解决办法


headlogo   Windard

但行好事,莫问前程

Blog

Opinion

Project

页阅读量:  ・  站访问量:  ・  站访客数: