mysql 查看锁_【MySQL入门】之MySQL数据库的锁机制（二）

  上篇文章主要聊了全局锁和表锁，并详细分析MDL锁的作用以及可能带来的问题。今天我们主要来聊一聊Innodb存储引擎的行锁。MySQL的行锁是在引擎层由引擎自己实现的，并不是所有的引擎都支持行锁，MyISAM 引擎就不支持行锁。行锁，顾名思义就是针对数据表中的行记录的锁。比如事物A更新了一行，而事物B也要更新同一行，就必须等待事物A的操作完成后才能进行。下面我们就介绍下行锁的种类，针对不同的锁进行操作演示。

一． 行锁的种类

1.单个记录的锁(record lock)，在RC隔离级别下只有record  lock记录锁模式。

2.间隙锁(Gap lock)

3.记录锁和间隙锁的组合(next-key lock), 加锁的基本单位就是next-key lock。

以下所有演示都是在RR隔离级别下进行的。

演示表t，主键是id，索引是age字段。

表数据如下：

二． 单个记录的锁

Session A

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update t set name='lili' where age=24;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

Session B

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update t set name='jim' where age=24;

试验结果:Session B

 

通过这个演示可以看出两个事务针对同一行数据修改时，后执行的事务会出现锁等待现象，超过innodb_lock_wait_timeout(默认50s)定义的时间后会超时。

我们再做个试验，把age上面的索引删除，看看会出现什么样的试验效果。

alter table t drop index idx_age;//删除索引

session  A

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update t set name='lili' where age=24;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

Session B

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update t set name='jim' where age=16;

试验结果：Session B

通过试验看出，虽然更新了不同的行，也出现了锁超时的情况。两个实现不同的地方就是试验2把索引删除了，试验结果就截然不同了。通过这个试验，我们可以看出，就是Innodb的行锁是加在索引上的。

三． 间隙锁(Gap lock)

在RR隔离级别下，为了避免幻读现象，引入了Gap lock。它锁定行记录的范围，不包含记录的本身，不允许在此范围内插入任何数据。

表数据如下：

Session A

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t where age<24 lock in share mode;+----+------+------+| id | name | age  |+----+------+------+|  4 | jack |   16 |+----+------+------+1 row in set (0.01 sec)

Session B

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> insert into t(name,age) value('angel',20);

实验结果:Session B

可以看出innodb在age<24的范围内加了间隙锁，导致session B插入age=20的记录时阻塞。

注意：间隙锁只在RR隔离级别下生效，RC隔离级别下是允许出现幻读现象的。

四． Next-key Lock

Next-keys Lock是记录锁和间隙锁的组合，Innodb扫描索引记录时，会先对索引记录加上记录锁(Record Lock)，再对索引记录之间的间隙加上间隙锁(Gap Lock)。

Innodb加锁的基本单位是next-key lock，是前开后闭区间。

Session A

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t where age >=16 and age<24 for update;+----+------+------+| id | name | age  |+----+------+------+|  4 | jack |   16 |+----+------+------+1 row in set (0.00 sec)

session B

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>insert into t(name,age) value('lucy',17);

Session C

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>update t set name='lucy' where age=24;

试验结果:

Session B

Session C

Innodb引擎加锁有个原则：查找过程中访问到的对象才会加锁。

在上面的例子中当用到age=16的时候，会在索引age上 (5,16]范围内加next-key锁，同时在(16,24]范围内加next-key锁，这样session A加锁的范围就是(5,24]。从结果上看也符合这个预期。

五． 锁监控

关于MySQL锁的监控，我们一般可以通过show processlist和show engine innodb status来查看和监控数据库的锁信息，其实还有一些更简单的方法，MySQL把事务和锁的信息记录在了information_schema库中，设计到的三张表分别是INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS。

我们模拟了一个锁等待的场景，以下是从这三张表收集的信息

通过以下命令可以查看锁源头：

SELECT r.trx_wait_started AS wait_started,TIMEDIFF(NOW(),r.trx_wait_started) AS wait_age,rl.lock_table AS locked_table,rl.lock_index AS locked_index,rl.lock_type AS locked_type,r.trx_id AS waiting_trx_id,r.trx_mysql_thread_id AS waiting_pid,sys.format_statement(r.trx_query) AS waiting_query,rl.lock_id AS waiting_lock_id,rl.lock_mode AS waiting_lock_mode,b.trx_id AS blocking_trx_id,b.trx_mysql_thread_id AS blocking_pid,sys.format_statement(b.trx_query) AS blocking_query,bl.lock_id AS blocking_lock_id,bl.lock_mode AS blocking_lock_mode 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 INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = w.blocking_lock_id INNER JOIN information_schema.INNODB_LOCKS rl ON rl.lock_id = w.requested_lock_id ORDER BY r.trx_wait_started\G

     【MySQL入门】之MySQL数据库的锁机制(一)