InnoDB存储引擎的行级锁
InnoDB存储引擎的行级锁
InnoDB存储引擎和MyISAM的其中有两个很重要的区别：一个是事务，一个就是锁机制不同。事务之前有介绍，有问题的去补课;锁方面的不同是InnoDB引擎既有表锁又有行锁，表锁的应用和MyISAM表锁用法一样，行锁只有通过有索引的字段作为条件检索的时候，才会使用行级锁，反之则是表锁。
一、隐式加锁
创建表和测试数据
用户表user中id为主键索引，username为普通索引，money字段为普通字段。在非事务环境下，隐式加锁的过程时间非常短，不便研究。下面的栗子都是在事务环境下
CREATE TABLE `user`( id INT PRIMARY KEY AUTO_INCREMENT, `username` VARCHAR(50), `money` DECIMAL(7,2), key username(username) )ENGINE=innodb CHARSET=utf8; INSERT INTO user(`username`,`money`) VALUES('乔峰',5); INSERT INTO user(`username`,`money`) VALUES('风清扬',20); INSERT INTO user(`username`,`money`) VALUES('聂哥',88);
1、行锁
当我们对用户表进行增删改（insert、delete、update）的时候，如果有检索条件中使用到了索引，MySQL服务器会自动给当前操作的表添加行锁。
A端
更新id为3的记录，id为主键索引，所以会引发行级锁。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update user set money=money+5 where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
B端在执行update操作的时候，先是光标不停闪烁，且进入锁等待状态，后报锁超时错误
mysql> update user set money=money+5 where id=1; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set money=money+12 where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
A端
A端执行提交（commit），系统会自动释放表锁
mysql> commit; Query OK, 0 rows affected (0.03 sec)
2、表锁
如果有检索条件(money字段)中没有使用到了索引，MySQL服务器会自动给当前操作的表添加表锁。
A端
mysql> select * from user; +----+----------+-------+ | id | username | money | +----+----------+-------+ | 1 | 乔峰 | 10.00 | | 2 | 风清扬 | 20.00 | | 3 | 聂哥 | 93.00 | +----+----------+-------+ 3 rows in set (0.02 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update user set money=money+5 where money=93.00; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
B端
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update user set money=money+12 where id=2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update user set money=money+12 where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
二、显式加行锁
加锁语句：
SELECT 查询语句 LOCK IN SHARE MODE | FOR UPDATE
我们只需在正常的查询语句后面加LOCK IN SHARE MODE | FOR UPDATE就能实现添加行级读锁和行级排他锁
1、意向锁
意向锁是InnoDB自动加的，不需用户干预，对于UPDATE、DELETE、INSERT操作，InnoDB会自动给操作的数据加排他锁。 我们在显式加行锁的时候，MySQL服务器会自动给操作表添加一个意向锁。此意向锁是隐式添加的，多个意向锁之间不会产生冲突且相互兼容
2、锁的兼容问题
| - | 共享锁 | 排他锁 | 意向共享锁 | 意向排他锁 |
|---|---|---|---|---|
| 共享锁 | 兼容 | 冲突 | 兼容 | 冲突 |
| 排他锁 | 冲突 | 冲突 | 冲突 | 冲突 |
2.1、共享锁兼容
A端
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user lock in share mode; +----+----------+-------+ | id | username | money | +----+----------+-------+ | 1 | 乔峰 | 10.00 | | 2 | 风清扬 | 20.00 | | 3 | 聂哥 | 98.00 | +----+----------+-------+ 3 rows in set (0.00 sec)
B端
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user lock in share mode; +----+----------+-------+ | id | username | money | +----+----------+-------+ | 1 | 乔峰 | 10.00 | | 2 | 风清扬 | 20.00 | | 3 | 聂哥 | 98.00 | +----+----------+-------+ 3 rows in set (0.00 sec)
2.2、共享锁和意向排他锁冲突
A端
2.3、排他锁与其他锁都冲突
A端
B端
mysql> select * from user where id=3 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update user set money=money+5 where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
三、间隙锁
间隙锁是针对一个范围条件的检索时，InnoDB会给符合条件的已有的数据记录的索引项加锁；对于键值在条件范围内但是并不存在的记录，叫做“间隙”，InnoDB也会对这个“间隙”加锁，这种锁机制就是间隙锁
A端
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where id > 2 for update; +----+----------+--------+ | id | username | money | +----+----------+--------+ | 3 | 聂哥 | 103.00 | +----+----------+--------+ 1 row in set (0.00 sec)
B端
mysql> update user set money=money+5 where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user(`username`,`money`) VALUES('jack',99); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
