【MySQL入门】之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: 0Session 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: 0Session 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腾讯云开发者
