第一章节锁等待原因介绍
1.1sys介绍
mysql 5.6也可以有sys库(基于performance_schema的视图)。sys库是一个开源项目,在githup上早就有,是一个DBA的开源工具,后来mysql感觉好,就放在了mysql5.7上。
下载地址:https://github.com/mysql/mysql-sys
$ cd mysql-sys-master
$ mysql < sys_56.sql
这样,就可以在mysql5.6里面加入sys库了,不过mysql 5.6只有88张表,而mysql 5.7有101张,这是因为Mysql 5.7的performace_schema库里面又多了几张表。
sys库是performance_schema的视图。
1.2锁等待innodb_lock_waits
当Mysql发生锁等待情况时,可以通过如下语句来在线查看:
mysql> select * from sys.innodb_lock_waits \G;
*************************** 1. row ***************************
wait_started: 2018-07-16 16:25:17 //锁等待开始的时间,16:25开始等待
wait_age: 00:10:08 //发现问题时已经等待了10分钟了
wait_age_secs: 608 //608秒,也就是等10分钟了
locked_table: `iws`.`busi_reconciliationgbgsinfo_inputdetails` //被锁住的表名
locked_index: PRIMARY //被锁住的索引
locked_type: RECORD //锁的类型为行锁
waiting_trx_id: 13666265 //waiting transaction id,正在等待事务的id号
waiting_trx_started: 2018-07-16 16:24:54 //这个事务是从16:24开始等待
waiting_trx_age: 00:10:31 //等了10分钟了
waiting_trx_rows_locked: 1 //正在等待的这个事务锁住了1行记录
waiting_trx_rows_modified: 0 //正在等待的这个事务修改了0行记录
waiting_pid: 441805 //这个等待事务的线程id是多少,通过show processlist 命令可以查到它,结果看到是一个sleep的线程,没有执行具体sql语句,见下
waiting_query: update busi_reconciliationgbgs ... where id = 4510 //等待锁释放的语句
waiting_lock_id: 13666265:2924:21:94 //正在等待的锁id
waiting_lock_mode: X //等待锁的类型是排它锁
blocking_trx_id: 13666259 //这个事务id阻塞了waiting lock
blocking_pid: 441803 阻塞事务的pid
blocking_query: NULL //阻塞事务的sql语句
blocking_lock_id: 13666259:2924:21:94
blocking_lock_mode: X
blocking_trx_started: 2018-07-16 16:24:51
blocking_trx_age: 00:10:34
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 441803
sql_kill_blocking_connection: KILL 441803
1 row in set (0.00 sec)
1.3锁等待语句查询--processlist
后来查看mysql官方文档,慢慢的才发现,其实只关注上面的waiting_pid、waiting_query和blocking_pid、blocking_query四个参数即可;其中waiting_pid和blocking_pid两个参数就是通过执行show processlist命令里面输出的线程id号,如下:
mysql> show full processlist \G;
*************************** 8. row ***************************
Id: 441803
User: iws
Host: 172.16.21.7:46121
db: iws
Command: Sleep
Time: 655
State:
Info: NULL
*************************** 9. row ***************************
Id: 441805
User: iws
Host: 172.16.21.7:46122
db: iws
Command: Query
Time: 652
State: updating
Info: update busi_reconciliationgbgsinfo_inputdetails set bgs_id = 1622 , date = '2018-06-24 00:00:00' , awbnumber = '006-85516771' , incidental = 15.00 , entry_exit = 23.00 , warehousing_fee = 0.00 , loading_unloading = 0.00 , other = 0.00 , total = 38.00 , state = 20 , comparison_resultsid = 30 , confirmation_method = '人工' , confirmationid = 'root' , confirmationtime = '2018-07-16 16:25:17' , confirmation_note = '.' , createtime = '2018-06-24 20:00:07' , createrid = '9862ebdbaf3249a88bcaa8f01bde0471' where id = 4510
通过上面两个的输出结果,我们明白了,是441803线程锁住了表,造成线程441805的等待。
我们看到发生等待的线程441805对应的sql语句是:update busi_reconciliationgbgs ... where id = 4510,但是锁表的线程441803对应的sql语句竟然是Null。这就更让人迷惑了。
1.4锁等待events_statements_current查询
于是我默默的翻开了mysql官方文档,原来里面已经对这个null专门做了说明。
官方文档说,要想找到这个null值对应的阻塞语句,可以通过下面几个步骤寻找:
a)、根据锁表的processlist id 441803,运用如下sql,找到null对应的sql语句,如下:
SELECT SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID
in
(SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=441803)
b)、如果上面找到的sql语句,你还是不能分析出为什么他们会锁表,一直拿着锁不释放,那么你可以查看 performance_schema.events_statements_history表里面最近执行过的10条sql(假设上面查到的thread_id=28):
主要涉及的语句
SELECT EVENT_ID,CURRENT_SCHEMA, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID
in
(SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=441803) order by event_id
1.5查询锁的简单语句
SELECT
wait_started,
wait_age,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query,
blocking_lock_mode,
sql_kill_blocking_query
FROM
sys.innodb_lock_waits