[MySQL] MySQL事务的隔离性是如何实现的

• 共享锁（Shared Lock，简称S锁），在事务需要读取一条记录时，需要先获取改记录的S锁
  
• 排他锁（Exclusive Lock，简称X锁），在事务要改动一条记录时，需要先获取该记录的X锁
  

• select … lock in share mode，对读取的记录加S锁
  
• select … for update ，对读取的记录加X锁
  
• 在事务中读取记录，对读取的记录加S锁
  
• 事务隔离级别在 SERIALIZABLE 下，对读取的记录加S锁
  

• Record Lock：对单个记录加锁
  
• Gap Lock：间隙锁，锁住记录前面的间隙，不允许插入记录
  
• Next-key Lock：同时锁住数据和数据前面的间隙，即数据和数据前面的间隙都不允许插入记录
  

CREATE TABLE `girl` (
`id` int(11) NOT NULL,
`name` varchar(255),
`age` int(11),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into girl values
(1, '西施', 20),
(5, '王昭君', 23),
(8, '貂蝉', 25),
(10, '杨玉环', 26),
(12, '陈圆圆', 20);

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ 利用主键进行等值查询
-- 对id=8的记录加S型Record Lock
select * from girl where id = 8 lock in share mode;
 
-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ 利用主键进行等值查询
-- 对id=8的记录加X型Record Lock
select * from girl where id = 8 for update;

-- REPEATABLE READ 利用主键进行等值查询
-- 但是主键值并不存在
-- 对id=8的聚集索引记录加Gap Lock
SELECT * FROM girl WHERE id = 7 LOCK IN SHARE MODE;

• Infimum记录，表示该页面中最小的记录
  
• upremum记录，表示该页面中最大的记录
  

-- REPEATABLE READ 利用主键进行范围查询
-- 对id=8的聚集索引记录加S型Record Lock
-- 对id>8的所有聚集索引记录加S型Next-key Lock（包括Supremum伪记录）
SELECT * FROM girl WHERE id >= 8 LOCK IN SHARE MODE;

• 对id=8的聚集索引记录加S型Record Lock
  
• 对id>8的所有聚集索引记录加S型Next-key Lock（包括Supremum伪记录）
  

lock tables t read

lock tables t write

• 别的事务可以继续获得该表的S锁
  
• 别的事务可以继续获得表中某些记录的S锁
  
• 别的事务不可以继续获得该表的X锁
  
• 别的事务不可以继续获得表中某些记录的X锁
  

• 别的事务不可以继续获得该表的S锁
  
• 别的事务不可以继续获得表中某些记录的S锁
  
• 别的事务不可以继续获得该表的X锁
  
• 别的事务不可以继续获得表中某些记录的X锁
  

• m_ids：在生成ReadView时，当前系统中活跃的事务id列表
  
• min_trx_id：在生成ReadView时，当前系统中活跃的最小的事务id，也就是m_ids中的最小值
  
• max_trx_id：在生成ReadView时，系统应该分配给下一个事务的事务id值
  
• creator_trx_id：生成该ReadView的事务的事务id
  

• 如果被访问版本的trx_id=creator_id，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
  
• 如果被访问版本的trx_id<min_trx_id，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问
  
• 被访问版本的trx_id>=max_trx_id，表明生成该版本的事务在当前事务生成ReadView后才开启，该版本不可以被当前事务访问
  
• 被访问版本的trx_id是否在m_ids列表中
  
• 4.1 是，创建ReadView时，该版本还是活跃的，该版本不可以被访问。顺着版本链找下一个版本的数据，继续执行上面的步骤判断可见性，如果最后一个版本还不可见，意味着记录对当前事务完全不可见
  
• 4.2 否，创建ReadView时，生成该版本的事务已经被提交，该版本可以被访问
  

CREATE TABLE `girl` (
`id` int(11) NOT NULL,
`name` varchar(255),
`age` int(11),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 系统中有两个事务id分别为100，200的事务正在执行
  
• 执行select语句时生成一个ReadView，mids=[100,200]，min_trx_id=100，max_trx_id=201，creator_trx_id=0（select这个事务没有执行更改操作，事务id默认为0）
  
• 最新版本的name列为西施，该版本trx_id值为100，在mids列表中，不符合可见性要求，根据roll_pointer跳到下一个版本
  
• 下一个版本的name列王昭君，该版本的trx_id值为100，也在mids列表内，因此也不符合要求，继续跳到下一个版本
  
• 下一个版本的name列为貂蝉，该版本的trx_id值为10，小于min_trx_id，因此最后返回的name值为貂蝉
  

• 系统中有一个事务id为200的事务正在执行（事务id为100的事务已经提交）
  
• 执行select语句时生成一个ReadView，mids=[200]，min_trx_id=200，max_trx_id=201，creator_trx_id=0
  
• 最新版本的name列为杨玉环，该版本trx_id值为200，在mids列表中，不符合可见性要求，根据roll_pointer跳到下一个版本
  
• 下一个版本的name列为西施，该版本的trx_id值为100，小于min_trx_id，因此最后返回的name值为西施