MySQL基础7——隔离级别，锁

隔离级别

ISO和ANIS SQL标准制定了四种事务隔离级别的标准，各数据库厂商在正确性和性能之间做了妥

协，并没有严格遵循这些标准；MySQL innodb默认支持的隔离级别是 REPEATABLE READ；

READD UNCOMMITTED

读未提交；该级别下读不加锁，写加排他锁，写锁在事务提交或回滚后释放锁；

READ COMMITTED

读已提交；从该级别后支持 MVCC (多版本并发控制)，也就是提供一致性非锁定读；此时读取操作

读取历史快照数据；该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据；

REPEATABLE READ

可重复读；该级别下也支持 MVCC，此时读取操作读取事务开始时的版本数据；

SERIALIZABLE

可串行化；该级别下给读加了共享锁；所以事务都是串行化的执行；此时隔离级别最严苛；

不同隔离级别下并发异常

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	存在	存在	存在
READ COMMITTED	不存在	存在	存在
REPEATABLE READ	不存在	不存在	存在（加锁解决）
SERIALIZABLE	不存在	不存在	不存在

相关命令

-- 设置隔离级别 
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
-- 或者采用下面的方式设置隔离级别 
SET @@tx_isolation = 'REPEATABLE READ'; 
SET @@global.tx_isolation = 'REPEATABLE READ'; 
-- 查看全局隔离级别 
SELECT @@global.tx_isolation; 
-- 查看当前会话隔离级别 
SELECT @@session.tx_isolation;
SELECT @@tx_isolation; 
-- 手动给读加 S 锁 
SELECT ... LOCK IN SHARE MODE; 
-- 手动给读加 X 锁 
SELECT ... FOR UPDATE; 
-- 查看当前锁信息 
SELECT * FROM information_schema.innodb_locks;

锁

锁机制用于管理对共享资源的并发访问；用来实现事务的隔离级别 ；

锁类型

共享锁和排他锁都是行级锁；MySQL当中事务采用的是粒度锁；针对表（B+树）、页（B+树叶子节点）、行（B+树叶子节点当中某一段记录行）三种粒度加锁；

(意向共享锁和意向排他锁都是表级别的锁)

共享锁

事务读操作加的锁；对某一行加锁；

在 SERIALIZABLE 隔离级别下，默认帮读操作加共享锁；

在 REPEATABLE READ 隔离级别下，需手动加共享锁，可解决幻读问题；

在 READ COMMITTED 隔离级别下，没必要加共享锁，采用的是 MVCC；

在 READ UNCOMMITTED 隔离级别下，既没有加锁也没有使用 MVCC；

排他锁

事务删除或更新加的锁；对某一行加锁；

在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁；

意向共享锁

对一张表中某几行加的共享锁；

意向排他锁

对一张表中某几行加的排他锁；

锁兼容性

锁	S	X	IS	IX
S	兼容	冲突	兼容	冲突
X	冲突	冲突	冲突	冲突
IS	兼容	冲突	兼容	兼容
IX	冲突	冲突	兼容	兼容

由于innodb支持的是行级别的锁，意向锁并不会阻塞除了全表扫描以外的任何请求；

意向锁之间是互相兼容的；

IX 对共享锁和排他锁都不兼容；

IS 只对排他锁不兼容；

当想为某一行添加 S 锁，先自动为所在的页和表添加意向锁 IS，再为该行添加 S 锁；

当想为某一行添加 X 锁，先自动为所在的页和表添加意向锁 IX，再为该行添加 X 锁；

锁算法

Record Lock (对主键索引加锁)

记录所，单个行记录上的锁

Gap Lock

间隙锁，锁定一个范围，但不包含记录本身；全开区间；REPEATABLE READ级别以上支持间隙锁

如果 REPEATABLE READ 修改 innodb_locks_unsafe_for_binlog = 0 ，那么隔离级别相当于

退化为 READ COMMITTED；

-- 查看是否支持间隙锁，默认支持，也就是 innodb_locks_unsafe_for_binlog = 0; 
SELECT @@innodb_locks_unsafe_for_binlog;

Next-Key Lock

记录锁+间隙锁，锁定一个范围，并且锁住记录本身；左开右闭区间；

Insert Intention Lock

插入意向锁，insert操作的时候产生；在多事务同时写入不同数据至同一索引间隙的时候，并不需

要等待其他事务完成，不会发生锁等待。

假设有一个记录索引包含键值4和7，两个不同的事务分别插入5和6，每个事务都会产生一个加在

4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

锁兼容

锁	GAP	Insert Intention	Record	Next-key
GAP	兼容	兼容	兼容	兼容
Insert Intention	冲突	兼容	兼容	冲突
Record	兼容	兼容	冲突	冲突
Next-key	兼容	兼容	冲突	冲突

横向：表示已持有，纵向：表示正在请求

AUTO-INC Lock

自增锁，是一种特殊的表级锁，发生在 AUTO_INCREMENT 约束下的插入操作；采用的一种特殊

的表锁机制；完成对自增长值插入的SQL语句后立即释放；在大数据量的插入会影响插入性能，因

为另一个事务中的插入会被阻塞；从MySQL 5.1.22开始提供一种轻量级互斥量的自增长实现机

制，该机制提高了自增长值插入的性能；

锁对象

行级锁是针对表的索引加锁；索引包括聚集索引和辅助索引；

表级锁是针对页或表进行加锁；

考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况；

示例1

select * from t where id = 5 for update; lock in share mode 
-- id 为主键 Read committed 隔离级别 
-- 在主键 id = 5 行上加 X 锁 

-- id 是唯一索引 Read committed 隔离级别 
-- 在唯一索引id=5行上加X锁，在主键索引上对应行加X锁 

-- id 是非唯一索引 Read committed 隔离级别 
-- 在非唯一索引上所有id=5行加上X锁，对应的主键索引列加上X锁 

-- id 不是索引 Read committed 隔离级别 
-- 在聚集索引上扫描，所有行上加X锁，此处有个优化，不满足的行在加锁后，判断不满足即可释放锁 

-- id 为主键 repeatable read 隔离级别
-- 在主键id=5行上加X锁

-- id 是唯一索引 repeatable read 隔离级别
-- 在唯一索引id=5行上加X锁，在主键索引上对应列加X锁 

-- id 是非唯一索引 repeatable read 隔离级别 
-- 在非唯一索引上查找id=5行，找到则加上X锁和GAP锁，然后对应的聚集索引加上X锁; 没有找到则 加上GAP锁 

-- id 不是索引 repeatable read 隔离级别 
-- 在聚集索引上扫描，所有行加上X锁和GAP锁

示例2

-- 在 RR 下 
-- 不加任何锁
select .. from t; 
-- 扫描到任何索引行上加S锁（next-key lock） 在聚集索引上加X锁 
select...from t lock in share mode; 
-- 扫描到任何索引行上加X锁（next-key lock） 在聚集索引上加X锁 
select..from t for update; -- 扫描到任何索引行上加X锁（next-key lock） 在聚集索引上加X锁
update..where condition delete from..where condition 
-- 如果是间隙插入，先添加 insert intention lock, 后在该行上添加X锁; 
-- 如果是递增插入，添加 auto-inc lock 或者 轻量级的互斥锁； 
insert into ...