MySQL 锁

1、执行一些很长的SQL导致进程卡住的问题解决

查询进程列表—show full processlist;

  1. show full processlist;

show full processlist 返回的结果是实时变化的，是对mysql链接执行的现场快照，所以用来处理突发事件非常有用。

它可以查看当前mysql的一些运行情况，是否有压力，都在执行什么sql，语句耗时时间，有没有慢sql在执行等等。
当发现一些执行时间很长的sql时，就需要多注意一下了，必要时kill掉，先解决问题。
命令有三种执行方式：

1、直接在命令行查询

:::info 末尾带\G是表示将查询结果进行按列打印，可以使每个字段打印到单独的行。 :::

  1. mysql> show full processlist;
  2. +--------+------+----------------------+-------+---------+------+----------+-----------------------+
  3. | Id     | User | Host                 | db    | Command | Time | State    | Info                  |
  4. +--------+------+----------------------+-------+---------+------+----------+-----------------------+
  5. | 449000 | root | 127.123.213.11:59828 | stark | Sleep   | 1270 |          | NULL                  |
  6. | 449001 | root | 127.123.213.11:59900 | stark | Sleep   | 1241 |          | NULL                  |
  7. | 449002 | root | 127.123.213.11:59958 | stark | Sleep   | 1216 |          | NULL                  |
  8. | 449003 | root | 127.123.213.11:60088 | stark | Sleep   | 1159 |          | NULL                  |
  9. | 449004 | root | 127.123.213.11:60108 | stark | Sleep   | 1151 |          | NULL                  |
  10. | 449005 | root | 127.123.213.11:60280 | stark | Sleep   | 1076 |          | NULL                  |
  11. | 449006 | root | 127.123.213.11:60286 | stark | Sleep   | 1074 |          | NULL                  |
  12. | 449007 | root | 127.123.213.11:60344 | stark | Sleep   | 1052 |          | NULL                  |
  13. | 449008 | root | 127.123.213.11:60450 | stark | Sleep   | 1005 |          | NULL                  |
  14. | 449009 | root | 127.123.213.11:60498 | stark | Sleep   |  986 |          | NULL                  |
  15. | 449013 | root | localhost            | NULL  | Query   |    0 | starting | show full processlist |
  16. +--------+------+----------------------+-------+---------+------+----------+-----------------------+
  17. 11 rows in set (0.01 sec)
  18. mysql> show full processlist\G;
  19. *************************** 1. row ***************************
  20.      Id: 449000
  21.    User: root
  22.    Host: 127.123.213.11:59828
  23.      db: stark
  24. Command: Sleep
  25.    Time: 1283
  26.   State: 
  27.    Info: NULL
  28. *************************** 2. row ***************************
  29.      Id: 449001
  30.    User: root
  31.    Host: 127.123.213.11:59900
  32.      db: stark
  33. Command: Sleep
  34.    Time: 1254
  35.   State: 
  36.    Info: NULL

2、通过查询链接线程相关的表来查看快照

  1. SELECT id, db, USER, HOST, command, time, state, info FROM information_schema.PROCESSLIST WHERE command != 'Sleep' ORDER BY time DESC;

3、通过navicat中的【工具】=> 【服务器监控】进行查看。

这种方式比较方便，还可以排序。
简单介绍一下，每列的含义：

  • Id：链接mysql 服务器线程的唯一标识，可以通过kill来终止此线程的链接。
  • User：当前线程链接数据库的用户
  • Host：显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户
  • db: 线程链接的数据库，如果没有则为null
  • Command: 显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）
  • Time: 线程处在当前状态的时间，单位是秒
  • State：显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成

  • Info: 线程执行的sql语句，如果没有语句执行则为null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句

    解决

    1、可以单独kill掉上面有问题的行

    1. kill 449000

    2、也可以批量结束时间超过3分钟的线程

    1. -- 查询执行时间超过3分钟的线程，然后拼接成 kill 语句
    2. select concat('kill ', id, ';')
    3. from information_schema.processlist
    4. where command != 'Sleep'
    5. and time > 3*60
    6. order by time desc;
    1. select concat('KILL ',id,';') from information_schema.processlist where command='Sleep';

    2、出现锁等待超时，重试事务的错误

    同一时刻，存在一个仓的多个用户并发修改同一行的数据，导致业务高峰期产生很多的锁等待现象。事务经常超时，从业务日志中经常输出Lock wait timeout exceeded; try restarting transaction。
    有时候在debug时间比较长时也会出现这种问题。

    1. Lock wait timeout exceeded; try restarting transaction

    查询未提交的事务，kill未提交的事务的id

    在5.5中，information_schema库中增加了三个关于锁的表（MEMORY引擎）；
    innodb_trx ## 当前运行的所有事务
    innodb_locks ## 当前出现的锁
    innodb_lock_waits ## 锁等待的对应关系

    1. select * from information_schema.innodb_trx;

    字段意义：

  • trx_state: 事务状态，一般为RUNNING

  • trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理
  • trx_mysql_thread_id: MySQL的线程ID，用于kill
  • trx_query: 事务中的sql

一般只要kill掉这些线程，DDL操作就不会Waiting for table metadata lock。

调整锁超时的阀值

lock_wait_timeout 表示获取metadata lock的超时（单位为秒），允许的值范围为1到31536000（1年）。默认值为31536000。
image.png
将其调整为30分钟，使得出现问题时快速失败（failfast）。

  1. set session lock_wait_timeout = 1800;
  2. set global lock_wait_timeout = 1800;

调大MySQL innodb_lock_wait_timeout这个参数的值

可以调大MySQL innodb_lock_wait_timeout这个参数的值，其默认值是50，但是带来的弊端就是依然某些情况下必然出现继续超时的情况，解决不了根本问题，只能用于某些特别紧急的临时场景。

  1. show variables like 'innodb_lock_wait_timeout';

image.png
处理锁等待超时问题的原则就是：对同一行记录的并发更新进行拆分，减少锁冲突。InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。 :::info 开发中，尽可能将大事务拆分为小事务，减少锁定的资源量和锁定的时间长度；尽可能减少基于范围的数据检索过滤条件，避免不该锁定的记录；合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能地缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定。 :::