mysql锁等待查询分析
发布时间：2022-03-30 12:03:44 所属栏目：MySql教程 来源：互联网
导读：mysql锁等待分析 1、简单说明 使用innodb存储引擎后，mysql有三张表来分析锁及阻塞的问题，在information_schema下面有三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通过这三张表，可以更简单地监控当前的事务并分析可能存在的问题。 mysql show ta
|
mysql锁等待分析 1、简单说明 使用innodb存储引擎后，mysql有三张表来分析锁及阻塞的问题，在information_schema下面有三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通过这三张表，可以更简单地监控当前的事务并分析可能存在的问题。 mysql> show tables like '%INNODB%'; +-----------------------------------------+ | Tables_in_information_schema (%INNODB%) | +-----------------------------------------+ | INNODB_LOCKS | | INNODB_TRX | | INNODB_LOCK_WAITS | INNODB_TRX表及结构 比较常用的列： trx_id:InnoDB存储引擎内部唯一的事物ID trx_status:当前事务的状态 trx_requested_lock_id：等待事务的锁ID trx_wait_started：事务等待的开始时间 trx_weight：事务的权重，反应一个事务修改和锁定的行数，当发现死锁需要回滚时，权重越小的值被回滚 trx_mysql_thread_id：MySQL中的进程ID，与show processlist中的ID值相对应 trx_query：事务运行的SQL语句 其余两个表字段相对较少 INNODB_LOCKS INNODB_LOCK_WAITS 2、锁定测试 mysql> use test; Database changed mysql> create table mytest1 (id int(4),pername char(10),bithday date,telphone char(11)); Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | mytest1 | +----------------+ 1 row in set, 1 warning (0.00 sec) --以mytest1表进行测试，里面的记录如下： mysql> select * from mytest1; +------+---------+------------+----------+ | id | pername | bithday | telphone | +------+---------+------------+----------+ | 1 | Jone | 1994-01-02 | 11111111 | | 2 | Tom | 1994-04-23 | 11214115 | | 3 | Rose | 1993-05-02 | 21214719 | | 4 | Jack | 1992-07-18 | 41218613 | | 5 | Block | 1991-09-21 | 75294651 | | 6 | Block | 1990-10-21 | 65364671 | +------+---------+------------+----------+ 6 rows in set (0.00 sec) --将自动提交改为手动提交 mysql> show variables like '%commit%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | autocommit | ON | | binlog_order_commits | ON | | innodb_api_bk_commit_interval | 5 | | innodb_commit_concurrency | 0 | | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 5 rows in set (0.00 sec) mysql> set @@autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%commit%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | autocommit | OFF | | binlog_order_commits | ON | | innodb_api_bk_commit_interval | 5 | | innodb_commit_concurrency | 0 | | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 5 rows in set (0.00 sec) --将表进行加锁 mysql> select * from mytest1 for update; +------+---------+------------+----------+ | id | pername | bithday | telphone | +------+---------+------------+----------+ | 1 | Jone | 1994-01-02 | 11111111 | | 2 | Tom | 1994-04-23 | 11214115 | | 3 | Rose | 1993-05-02 | 21214719 | | 4 | Jack | 1992-07-18 | 41218613 | | 5 | Block | 1991-09-21 | 75294651 | | 6 | Block | 1990-10-21 | 65364671 | +------+---------+------------+----------+ 6 rows in set (0.00 sec) --重新开一个窗口执行另一个语句 mysql> select count(*) from test.mytest1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 3、查看锁定情况 mysql> select r.trx_id waiting_trx_id,r.trx_mysql_thread_Id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id; +----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+ | waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | +----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+ | 5458 | 4 | select count(*) from test.mytest1 for update | 5450 | 3 | NULL | +----------------+----------------+----------------------------------------------+-----------------+-----------------+----------------+ 1 row in set (0.01 sec) 这里可以很清楚的看到阻塞的thread 3，被阻塞的thread 4 mysql> show full processlist; +----+-------------+-----------+--------------------+---------+-------+---------------------------------+----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+--------------------+---------+-------+---------------------------------+----------------------------------------------+ | 1 | system user | | NULL | Daemon | 18882 | Waiting for ndbcluster to start | NULL | | 3 | root | localhost | test | Sleep | 1025 | | NULL | | 4 | root | localhost | information_schema | Query | 45 | Sending data | select count(*) from test.mytest1 for update | | 5 | root | localhost | information_schema | Query | 0 | init | show full processlist | | 6 | root | localhost | test | Sleep | 212 | | NULL | +----+-------------+-----------+--------------------+---------+-------+---------------------------------+----------------------------------------------+ 5 rows in set (0.00 sec) 由于我这里是两个会话窗口，所以很容易判断出id 3(thread 3)，为阻塞会话！ 知道会话后，可以采用kill进行查杀 mysql> kill 3; --3指的是thread id(processlist中的id) Query OK, 0 rows affected (0.00 sec) 查杀以后，第二个会话迅速将结果显示出来 4、总结 --以前使用processlist时，显示太多，根本找不锁的根本原因，会话少时，可以凭直觉查看 --直接使用show engine innodb status查看，可以查看到一些东西，但是不全面，显示太多 mysql> show engine innodb status; Per second averages calculated from the last 6 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 22 srv_active, 0 srv_shutdown, 18645 srv_idle srv_master_thread log flush and writes: 18667 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 25 OS WAIT ARRAY INFO: signal count 25 Mutex spin waits 228, rounds 723, OS waits 3 RW-shared spins 22, rounds 660, OS waits 22 RW-excl spins 0, rounds 0, OS waits 0 Spin rounds per wait: 3.17 mutex, 30.00 RW-shared, 0.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 5458 Purge done for trx's n:o < 5441 undo n:o < 0 state: running but idle History list length 26 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 6, OS thread handle 0x7fb3169c1700, query id 403 localhost root init show engine innodb status ---TRANSACTION 0, not started MySQL thread id 5, OS thread handle 0x7fb316a02700, query id 393 localhost root cleaning up ---TRANSACTION 5457, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) 指出一个行锁 MySQL thread id 4, OS thread handle 0x7fb316a43700, query id 402 localhost root Sending data select count(*) from test.mytest1 for update ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: 等待时间 RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`mytest1` trx id 5457 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000300; asc ;; 1: len 6; hex 00000000152f; asc /;; 2: len 7; hex a30000015b0110; asc [ ;; 3: len 4; hex 80000001; asc ;; 4: len 10; hex 4a6f6e65202020202020; asc Jone ;; 5: len 3; hex 8f9422; asc ";; 6: len 11; hex 3131313131313131202020; asc 11111111 ;; 这一段说的是等待内容，包括表的内容，指出了表的内容mytest1 ------------------ TABLE LOCK table `test`.`mytest1` trx id 5457 lock mode IX RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`mytest1` trx id 5457 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000300; asc ;; 1: len 6; hex 00000000152f; asc /;; 2: len 7; hex a30000015b0110; asc [ ;; 3: len 4; hex 80000001; asc ;; 4: len 10; hex 4a6f6e65202020202020; asc Jone ;; 5: len 3; hex 8f9422; asc ";; 6: len 11; hex 3131313131313131202020; asc 11111111 ;; ---TRANSACTION 5450, ACTIVE 813 sec 2 lock struct(s), heap size 360, 7 row lock(s) MySQL thread id 3, OS thread handle 0x7fb316a84700, query id 388 localhost root cleaning up TABLE LOCK table `test`.`mytest1` trx id 5450 lock mode IX RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`mytest1` trx id 5450 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum; 而这一段正好说明了，5450正在锁定表mytest1，所以可以确定是5450(thread 3)正执有资源 如此去查看，非常耗费时间！ --使用mysqladmin debug查看，能看到所有产生锁的线程，但无法判断哪个才是根因。 所以，感觉在新的版本中，使用语句查询确实是一个好办法，能够迅速的找到阻塞的原因！ （编辑：吕梁站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
