MySQL⾏级锁

⾏级锁介绍
MySQL的⾏级锁，是由存储引擎来实现的，利⽤存储引擎锁住索引项来实现的。这⾥我们主要讲解InnoDB的⾏级锁。
InnoDB的⾏级锁，按照锁定范围来说，分为三种：

• 记录锁（Record Locks）:锁定索引中⼀条记录。 id=1
• 间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第⼀个索引记录前⾯的值或者最后⼀个索引记录后⾯的值。
• Next-Key Locks:是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

InnoDB的⾏级锁，按照功能来说，分为两种： RR

• 共享锁（S）：允许⼀个事务去读⼀⾏，阻⽌其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻⽌其他事务取得相同数据集的共享读锁（不是读）和排他写锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会⾃动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
⼿动添加共享锁（S）：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
⼿动添加排他锁（x）：
SELECT * FROM table_name WHERE ... FOR UPDATE
InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使⽤的，不需要⽤户⼲预。

• 意向共享锁（IS）：事务打算给数据⾏加⾏共享锁，事务在给⼀个数据⾏加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据⾏加⾏排他锁，事务在给⼀个数据⾏加排他锁前必须先取得该表的IX锁。

意向锁和⾏锁可以共存，意向锁的主要作⽤是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该表是否某些记录上⾯有⾏锁。

InnoDB⾏锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种⾏锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使⽤⾏级锁，否则，InnoDB将使⽤表锁！
Innodb所使⽤的⾏级锁定争⽤状态查看：
mysql> show status like 'innodb_row_lock%';

• Innodb_row_lock_current_waits：当前正在等待锁定的数量；
• Innodb_row_lock_time：从系统启动到现在锁定总时间⻓度；
• Innodb_row_lock_time_avg：每次等待所花平均时间；
• Innodb_row_lock_time_max：从系统启动到现在等待最常的⼀次所花的时间；
• Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，⽐较重要的主要是：

• Innodb_row_lock_time_avg（等待平均时⻓）
• Innodb_row_lock_waits（等待总次数）
• Innodb_row_lock_time（等待总时⻓）这三项。

尤其是当等待次数很⾼，⽽且每次等待时⻓也不⼩的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着⼿指定优化计划。

两阶段锁
传统RDBMS加锁的⼀个原则，就是2PL (Two-Phase Locking，⼆阶段锁)。相对⽽⾔，2PL⽐较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下⾯，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。

从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。
加锁阶段：只加锁，不放锁。
解锁阶段：只放锁，不加锁。

InnoDB⾏锁演示
⾏读锁
session1（Navicat）、session2（mysql）
查看⾏锁状态 show STATUS like 'innodb_row_lock%';
1、session1: begin;--开启事务未提交
select * from mylock where ID=1 lock in share mode; --⼿动加id=1的⾏读锁,使⽤索引
2、session2：update mylock set name='y' where id=2; -- 未锁定该⾏可以修改
3、session2：update mylock set name='y' where id=1; -- 锁定该⾏修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 锁定超时
4、session1: commit; --提交事务 或者 rollback 释放读锁
5、session2：update mylock set name='y' where id=1; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
注：使⽤索引加⾏锁 ，未锁定的⾏可以访问

⾏读锁升级为表锁
session1（Navicat）、session2（mysql）
1、session1: begin;--开启事务未提交
--⼿动加name='c'的⾏读锁,未使⽤索引
select * from mylock where name='c' lock in share mode;
2、session2：update mylock set name='y' where id=2; -- 修改阻塞 未⽤索引⾏锁升级为表锁
3、session1: commit; --提交事务 或者 rollback 释放读锁
4、session2：update mylock set name='y' where id=2; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
注：未使⽤索引⾏锁升级为表锁

⾏写锁
session1（Navicat）、session2（mysql）
1、session1: begin;--开启事务未提交
--⼿动加id=1的⾏写锁,
select * from mylock where id=1 for update;
2、session2：select * from mylock where id=2 ; -- 可以访问
3、session2: select * from mylock where id=1 ; -- 可以读 不加锁
4、session2: select * from mylock where id=1 lock in share mode ; -- 加读锁被阻塞
5、session1：commit; -- 提交事务 或者 rollback 释放写锁
5、session2：执⾏成功
主键索引产⽣记录锁

间隙锁

间隙锁有两种情况
1、防⽌插⼊间隙内的数据
2、防⽌已有数据更新为间隙内的数据
session1（Navicat）、session2（mysql）
案例演示：
mysql> create table news (id int, number int,primary key (id));
mysql> insert into news values(1,2);
......
--加⾮唯⼀索引
mysql> alter table news add index idx_num(number);
session 1:
start transaction ;
select * from news where number=4 for update ;
session 2:
start transaction ;
insert into news value(2,4);#（阻塞）
insert into news value(2,2);#（阻塞）
insert into news value(4,4);#（阻塞）
insert into news value(4,5);#（阻塞）
insert into news value(7,5);#（执⾏成功）
insert into news value(9,5);#（执⾏成功）
insert into news value(11,5);#（执⾏成功）

注：id和number都在间隙内则阻塞。
session 1:
start transaction ;
select * from news where number=13 for update ;
（ select * from news where id>1 and id < 8 for update;）
session 2:
start transaction ;
insert into news value(11,5);#(执⾏成功)
insert into news value(12,11);#(执⾏成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得⽆穷⼤作为右区间，
因此，session 1的间隙锁的范围（11，⽆穷⼤）
注：⾮主键索引产⽣间隙锁，主键范围