MySQL-锁等待排查
背景
最近我们的登录系统在每个钟点的 18分就会登录不进去, 排查后发现有锁等待情况 , 周期地发生那么很大几率是自动任务了, 为了找到为什么会锁等待 ,我们做了以下的排查
排查过程
-- 1.锁住的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 2.事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 3.等待的锁
SELECT * FROM information_schema.INNODB_LOCK_waits;
-- 4.当前进行的进程
SHOW FULL PROCESSLIST;
先看 1.锁住的事务 3.等待的锁
-- 1.锁住的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-----------+---------+---------+--------------------+----------+----------+---------+--------+---------+
|lock_id |lock_trx_id|lock_mode|lock_type|lock_table |lock_index|lock_space|lock_page|lock_rec|lock_data|
+-----------------+-----------+---------+---------+--------------------+----------+----------+---------+--------+---------+
|45982383:101:7:72|45982383 |X |RECORD |`mdp`.`mdp_sec_user`|PRIMARY |101 |7 |72 |1 |
|45982369:101:7:72|45982369 |S |RECORD |`mdp`.`mdp_sec_user`|PRIMARY |101 |7 |72 |1 |
+-----------------+-----------+---------+---------+--------------------+----------+----------+---------+--------+---------+
-- 3.等待的锁
SELECT * FROM information_schema.INNODB_LOCK_waits;
+-----------------+-----------------+---------------+-----------------+
|requesting_trx_id|requested_lock_id|blocking_trx_id|blocking_lock_id |
+-----------------+-----------------+---------------+-----------------+
|45982383 |45982383:101:7:72|45982369 |45982369:101:7:72|
+-----------------+-----------------+---------------+-----------------+
图片比较好看点 :
可以看到 , lock_trx_id 为 45982369 , 持有的是 S锁-读锁 , 而 45982383 持有的是 X锁-写锁 , 我们知道这是冲突的, 并且从3.等待的锁可以看到 45982383 正在等待 45982369 释放锁 . 我们看一下这个 45982383 和 45982369 各是执行了什么 SQL , 导致读写锁阻塞了.
然后接着看 2.事务 4.当前进行的进程
然后我们把 SQL 复制出来
持有 S 锁 的 SQL :
INSERT INTO dataauth.ccs_data_auth_user_org (
USER_ID,
ORG_ID,
ORG_CODE,
ORG_NAME,
SETS_OF_BOOKS_ID,
CREATION_DATE,
LAST_UPDATE_DATE
) SELECT
u.USER_ID,
cus.CORG_ID,
cus.CORG_CODE,
cus.CORG_NAME,
u.SETS_OF_BOOKS_ID,
now(),
now()
FROM
mdp.mdp_sec_user u
INNER JOIN mdp.mdp_sec_org_staff s ON u.USER_ID = s.USER_ID
INNER JOIN mdp.mdp_sec_org o ON s.ORG_ID = o.ORG_ID
INNER JOIN (
SELECT
c.CORG_ID,
c.CORG_CODE,
c.CORG_NAME,
cl.CORG_CODE CORG_LINE_CODE,
c.SETS_OF_BOOKS_ID
FROM
basedata.ccs_base_customer c,
basedata.ccs_base_customer_line cl
WHERE
c.CUSTOMER_ID = cl.CUSTOMER_ID
AND c.STORE_TYPE IN (1, 2)
AND c.CORG_CODE IS NOT NULL
) cus
WHERE
cus.CORG_LINE_CODE LIKE concat(o.`CODE`, '%')
AND u.SETS_OF_BOOKS_ID = cus.SETS_OF_BOOKS_ID
ON DUPLICATE KEY UPDATE
LAST_UPDATE_DATE = VALUES(LAST_UPDATE_DATE)
持有 X 锁 的 SQL :
update mdp.mdp_sec_user u set LAST_UPDATE_DATE = 'xx' where LOGIN_ID = 'YY' ;
剩下的就是 SQL 优化了
SQL 字段说明
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
- lock_id：锁定的标识符。
- lock_trx_id：持有锁定的事务 ID。
- lock_mode：锁定的模式，如 SHARED、EXCLUSIVE、RECORD、TABLE 等。
- lock_type：锁定的类型，如 RECORD、TABLE、PAGE 等。
- lock_table：锁定的表名。
- lock_index：锁定的索引名。
- lock_space：锁定的表空间 ID。
- lock_page：锁定的页码。
需要注意的是，查询 INFORMATION_SCHEMA.INNODB_LOCKS 表需要具有相应的权限。如果您没有足够的权限，可能无法执行该查询语句。
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_waits
- requesting_trx_id：正在等待锁定的事务 ID。
- requested_lock_id：正在等待的锁定 ID。
- blocking_trx_id：正在持有锁定的事务 ID。
- blocking_lock_id：正在持有的锁定 ID。
- requested_lock_type：正在等待的锁定类型，如 S、X、IS、IX 等。
- blocking_lock_type：正在持有的锁定类型，如 S、X、IS、IX 等。
- requested_table：正在等待锁定的表名。
- blocking_table：正在持有锁定的表名。
- requested_index：正在等待锁定的索引名。
- blocking_index：正在持有锁定的索引名。
杀死阻塞进程
有时候在生产环境 ,我们不得不先杀死阻塞的线程 ,以便不阻塞生产业务.
#!/bin/bash
mysql -u root -e "show processlist" | grep -i "Locked" >> locked_log.txt
for line in `cat locked_log.txt | awk '{print $1}'`
do
echo "kill $line;" >> kill_thread_id.sql
done
现在kill_thread_id.sql的内容像这个样子
kill 66402982;
kill 66402983;
kill 66402986;
kill 66402991;
好了, 我们在mysql的shell中执行, 就可以把所有锁表的进程杀死了.执行上面的SQL
mysql>source kill_thread_id.sql
参考资料
- Chatgpt
