关于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<'u1119yux' lock in share mode;
+----------+

 

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 inserting
mysql 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 sec
5 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: RECORD
 lock_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: RECORD
 lock_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