一 在分析innodb中锁阻塞时,几种方法的对比情况:
1.使用show processlist查看不靠谱
2.直接使用show engine innodb status查看,无法判断到问题的根因
3.使用mysqladmin debug查看,能看到所有产生锁的线程,但无法判断哪个才是根因
# mysqladmin -uroot -p debug
4.开启innodb_lock_monitor后,再使用show engine innodb status查看,能够找到锁阻塞的根因
# 随便在一个数据库中创建这个表,就会打开lock monitor。创建表后innodb会每过15秒输出一次innodb状态信息到error log,通过删除表停止该monitor功能
MySQL> create table innodb_monitor(a int) engine=innodb;
二 查看事务和锁的相关视图
select * from INFORMATION_SCHEMA.innodb_trx;
select * from INFORMATION_SCHEMA.innodb_locks;
select * from INFORMATION_SCHEMA.innodb_lock_waits;
三 案例
创建测试表并填充数据
root@localhost:mysql.sock 02:30:59 [school]>CREATE TABLE t_gap( a INT, b INT, PRIMARY KEY (a), KEY(b));
root@localhost:mysql.sock 02:31:19 [school]>insert into t_gap values(1,1), (3,1), (5,3), (7,6),(10,8);
root@localhost:mysql.sock 22:45:13 [school]>select * from t_gap;
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 3 | 1 |
| 5 | 3 |
| 7 | 6 |
| 10 | 8 |
+----+------+
5 rows in set (0.00 sec)
session1:
T1:begin;SELECT * FROM t_gap WHERE b =3 FOR UPDATE;
session2:
T2:begin;SELECT * FROM t_gap WHERE a=5 LOCK IN SHARE MODE;(等待)
注意:
在辅助索引上加的锁,最终会回溯到主键上再加一把锁.如上T1,在索引列b上加了锁,会回溯到主键a=5上再加一把锁
root@localhost:mysql.sock 03:08:12 [school]>show engine innodb status \G;
------------
TRANSACTIONS
------------
Trx id counter 44387
Purge done for trx's n:o < 44376 undo n:o < 0 state: running but idle
History list length 2206
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 31, OS thread handle 0x7f80f5cc6700, query id 260 localhost root init
show engine innodb status
---TRANSACTION 44386, 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 32, OS thread handle 0x7f80f5cf7700, query id 259 localhost root statistics
SELECT * FROM t_gap WHERE A=5 LOCK IN SHARE MODE
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 3 n bits 72 index `PRIMARY` of table `school`.`t_gap` trx id 44386 lock mode S locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 00000000ad4e; asc N;;
2: len 7; hex b7000002330128; asc 3 (;;
3: len 4; hex 80000003; asc ;;
------------------
---TRANSACTION 44385, ACTIVE 15 sec
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 33, OS thread handle 0x7f80f5d28700, query id 257 localhost root cleaning up