mysql的并发处理机制_下篇 - 苏家小萝卜_H5之家 - 中国HTML5教程资源分享第一站

MySQL的并发处理机制，有MVCC及锁机制来处理，上篇简要说明了 MVCC及隔离级别，这篇来说说mysql下的锁。

温馨提示：下文有几个表格长度较长，右下角的博文导航目录会挡道，浏览时，可以点击导航目录的左下角按钮收缩目录：

如果转载，请注明博文来源：，版权归博客园苏家小萝卜所有。望各位支持！

1 Innodb的锁

在innodb中，有4种类型的锁：IX、X、IS及S锁，其说明如下：

类型说明场景

S 共享锁针对于RS隔离级别的查询或者添加Lock in share mode的SELECT查询而产生的锁

X 排它锁针对于update、delete、insert操作而产生的锁

IS 意向共享锁表级别的锁，在添加S锁之前对表格添加IS锁

IX 意向排他锁表级别的锁，在添加X锁之前对表格添加IX锁

1.1 锁定兼容情况

四个锁之间的兼容性，需要分成两种情况来讨论，锁粒度小于表级别的锁的兼容情况，表级的锁兼容情况。

锁粒度小于表级别的锁的兼容情况

对于这两行锁的兼容说明如下：

假设有一行数据，添加了行锁S锁，那么这个行数据，可以提供给其他事务进行S锁的申请和添加，但是不支持其他事务对这一行进行X锁的申请和添加。比如，事务A，对 pk100 这一行进行了查询操作并添加了S锁，那么其他事务仍然可以对这一行数据进行查询，但是不能对这行数据进行 UPDATE 跟 DELETE 操作，会处于锁等待情况，直到该事务A结束并释放S锁；

假设有一行数据，添加了X锁，那么这个行数据，不允许其他事务对这一行数据进行加锁。比如，事务A，对pk100这一行进行了UPDATE操作，那么其他事务在事务A没有结束之前，都无法对这一行数据申请 S锁。

表级的锁兼容情况

对于表级别的锁兼容性如下：

当一个表格持有IX表锁时，支持申请IS、IX表锁，但是不兼容S、X表锁。

比如，事务A对表格 tba 中 id=10 （id为主键）进行进行数据修改，这个时候，会对表格 tba 先申请一个 IX 表锁（申请成功），然后申请 id =10 的 X 行锁，申请成功，则事务A 持有 IX 表锁、id=10的X 行锁，此时事务B 查询 id=20的行，申请表锁 IS 成功，申请 id=20的 S 行锁成功；事务C 修改 id=30的行数据，申请表锁 IX 成功，申请 id=30的行锁 X成功；但是，事务D中，对整个表格发起update或者全表SELECT操作，需要申请 X表锁或者S表锁，正常情况下，应该要对表格的每一行数据进行查看，确保每一行数据的行锁情况，但是因为有了意向锁，事务D一看到 tba 持有了IX锁，则明白，tba 中某些行持有X锁，则会不兼容其他事务对tba 表锁S ，表锁X的申请。

为什么要引入意向表锁？

在没有意向锁的时候，如果事务T 需要给表格 A 添加一个S 表锁，那么就意味这这个表格内部的每一行数据，都不能有X锁，才能够申请 S 表锁成功，如果表格数据很多，一行行查找非常浪费加锁时间，这个时候，就出现了表格意向锁，当表格内部某些行发生 UPATE DELETE INSERT操作，则会对表格加上一个意向 IX 表锁，这样事务T在申请表格A的 S 表锁时，只需要检查表格 A 是否有 IX表锁，如果有，则意味内部有部分行数据持有X锁，则直接进入等待情况，如果表格没有 IX表锁，则直接申请S表锁成功，这是一个多么节约加锁时间的操作！

1.2 锁的级别

Table Lock

表锁，如果没有where条件、无可用索引或者获取的行记录过多，则会使用 table full scan，添加表锁

Record Lock

记录锁，如果执行计划使用了索引，则会根据索引的查找情况添加行锁

Gap Lock

在RR、RS隔离级别，发生在索引值之间，在连续的两个索引值之间添加锁，加锁后，这两个索引值之间，无法插入新的索引值，不包含行记录

Next-Key Lock

Record Lock 跟Gap Lock的组合，合体成为Next-KEY Lock

表锁、行锁都相对好理解，这里尝试简单说明下 GAP LOCK。

假设当前隔离级别为RR，表格 tbgap( id int auto_increment primary key not null , name varchar(50) , sort int , key ix_sort (sort)) engine=innodb;

表格数据如下：

在索引ix_sort上，一共有7个间隙，分别为（-∞，(1,6)），（(1,6)，(2,5)），（(2,5)，(3,2)），（(3,2)，(5,4)），（(5,4)，(6,1)），（(6,1)，(7,3)），（(7,3)，+∞），而根据实际的隔离级别及锁申请情况，加在这些间隙上的锁，则成为 GAP LOCK 。

1.3 锁与隔离级别（不考虑 lock in shar mode跟for update ） 2 锁的申请与释放过程

看SQL语句的锁情况，需要结合隔离级别、执行计划、表结构等，同一个SQL，不同的隔离级别、表结构、执行计划，其锁情况不一定是一样的！

本次模拟这3个表格，age列分别：无索引、有一般索引、有唯一索引。表结构结束及数据如下：

CREATE TABLE tb_no_index ( id int primary key not null auto_increment, age int not null, name varchar(100) );

CREATE TABLE tb_index ( id int primary key not null auto_increment, age int not null, name varchar(100) KEY ix_age(age) );

CREATE TABLE tb_unique_index ( id int primary key not null auto_increment, age int not null,name varchar(100) UNIQUE KEY ix_age(age) );

INSERT INTO tb_no_index(age) values(2),(9),(21),(4),(7),(25);

INSERT INTO tb_index(age) values(2),(9),(21),(4),(7),(25);

INSERT INTO tb_unique_index(age) values(2),(9),(21),(4),(7),(25);

每个表格IX_age的索引行数就据如下图展示：

age

每个表格主键上面的行数就据如下图展示：

age

name

null

2.1 Read Uncommitted

所有事务隔离级别设置： set session transaction isolation level read Uncommitted ;

RU是读未提交，不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 语句，均为读未提交，不加锁，存在脏读、不可重复读及幻读。

所有UPDATE、DELETE、INSERT获取当前读记录，加锁。

表格 SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

读不加锁，读未提交数据

可能有脏读、不可重复读及幻读

当前读，根据主键修改数据

tbname 加意向表锁 IX

id=4 加行锁 X

表格的age列无索引，所以update过程中，全表加X锁

支持semi-constent-read，如果有其他update语句修改其他行不堵塞，但是不支持 select ... for update

同左

tb_index

表格的age列有索引，update过程中

tb_index 加表格意向锁 IX

age索引上面，age=21 行添加行锁 X

再在主键上，给id=3 这一行数据，添加行数 X

表格的age列有索引，update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面，age=7，age=9 行添加行锁 X

再在主键上，给id=2，id=5 这一行数据，添加行数 X

tb_unique_index

同上

2.2 Read Committed

所有事务隔离级别设置： set session transaction isolation level read committed ;