mysql 锁监控_关于mysql Gaplock以及监控锁的相关示例

innodb引擎中，mysql中的行级别的锁大致有三种，recordlock、Gap、Next-KeyLocks。recordlock就是锁住某一行记录；而Gap会锁住某一段范围中的记录；Next-KeyLocks则是前两者加起来的效果。

下面列举了一个示例：

创建一张表，User_Info，表示用户信息，其中建了user_num唯一索引，初始有1000w数据。

先执行session1

session1:

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> select count(1) from User_Info where user_num

+----------+

session2:

mysql> set autocommit=0;

insert into User_Info values (100000000,'u10000','ddddd','dddd','name','realname','1392888888',11,'dd','dd',1,1,'2012-10-10');

此时session2的insert 语句没有响应，直至超时

关于锁的查看，可以通过以下几种方式，

1、show engine innodb status

2、show processlist

3、查看INFORMATION_SCHEMA.innodb_trx、innodb_locks、innodb_lock_waits

mysql> show engine innodb status\G

可以看到，trx B5F5001等待X record locks，但是谁持有了这个锁还无法看出

------------

TRANSACTIONS

------------

Trx id counter B5F5002

Purge done for trx's n:o < B5F4EEF undo n:o < 0

History list length 685

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 3, OS thread handle 0x7f566c797700, query id 30 localhost root

show engine innodb status

---TRANSACTION B5F4EE7, ACTIVE 25 sec insertingmysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1

MySQL thread id 2, OS thread handle 0x7f566c7d8700, query id 27 localhost root update

insert into LSMP_LOTTERY_USER values (100000000,'u1019yux','ddddd','dddd','name','realname','1392888888',11,'dd','dd',1,1,'2012-10-10')

------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 4 page no 14 n bits 648 index `INDEX_LUSER_NUM` of table `dlsp`.`LSMP_LOTTERY_USER` trx id B5F5001 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 552 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 18; hex 7531303161716b326d69673331783374677a; asc u101aqk2mig31x3tgz;;

1: len 4; hex 8000fdf8; asc     ;;

------------------

---TRANSACTION B5F4EE6, ACTIVE 55 sec5 lock struct(s), heap size 1248, 1963 row lock(s)

MySQL thread id 1, OS thread handle 0x7f4b30560700, query id 13 localhost root

查看INFORMATION_SCHEMA获得锁的相关信息，

可以看到事务B5F4EE7等待事务B5F4EE6的S锁，

事务B5F4EE6持有行锁中的范围间隙lock

mysql> select * from INFORMATION_SCHEMA.innodb_locks\G;

*************************** 1. row ***************************

lock_id: B5F4EE7:4:14:552

lock_trx_id: B5F4EE7

lock_mode: X,GAP

lock_type: RECORDlock_table: `dlsp`.`LSMP_LOTTERY_USER`

lock_index: `INDEX_LUSER_NUM`

lock_space: 4

lock_page: 14

lock_rec: 552

lock_data: 'u101aqk2mig31x3tgz'

*************************** 2. row ***************************

lock_id: B5F4EE6:4:14:552

lock_trx_id: B5F4EE6

lock_mode: S

lock_type: RECORDlock_table: `dlsp`.`LSMP_LOTTERY_USER`

lock_index: `INDEX_LUSER_NUM`

lock_space: 4

lock_page: 14

lock_rec: 552

lock_data: 'u101aqk2mig31x3tgz'

2 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.innodb_lock_waits\G;

*************************** 1. row ***************************

requesting_trx_id: B5F4EE7

requested_lock_id: B5F4EE7:4:14:552

blocking_trx_id: B5F4EE6

blocking_lock_id: B5F4EE6:4:14:552

1 row in set (0.00 sec)

ERROR: No query specified