mysql 的独占锁和排它锁_Mysql你可能不知道的锁

1. InnoDB锁的基本类型

我们前边说过，并发事务的读-读情况并不会引起什么问题，不过对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，所以就需要使用到不同的锁

1.1 共享锁

Shared Locks (共享锁)，简称S锁，我们获取了一行数据首先需要获得这条的读锁，所以它也叫做读锁。用 select ... lock in share mode; 的方式手工加上一把读锁。

SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

复制代码

当一个事务使用读取加了S锁的数据，在这个锁释放前，任何针对该数据的写操作将被阻塞，读操作放行。

1.2 独占锁

Exclusive Locks(独占锁)，也称之为排他锁，简称X锁若想要改动一条数据，就得获得这条数据的写锁，所以它也叫写锁。

SELECT * FROM student WHERE id=1 FOR UPDATE;

复制代码

只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

1.3 意向锁

当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。

意向锁是为了解决什么问题呢？

当我们给给表加上表锁时，首先需要判断表里面是否有没有其他事务锁住了某些行，如果不是两者不是读锁，肯定就不能加上表锁，这时候我们得扫描整张表数据才能知道是否有行锁，如果数据量大，效率就很低。

引入意向锁之后，我只要判断这张表上面有没有意向锁，如

果有，就直接返回失败。如果没有，就可以加锁成功

2. 行锁的原理

Mysql InnoDB锁住的是什么呢？

我们创建三张表，一张没有索引t1，一张有主键索引t2，一张有唯一索引t3。

2.1 没有索引的表

我们先假设InnoDB的锁锁住是一行数据

Transaction 1Transaction 2begin---

SELECT * FROM student where id=1 FOR UPDATE;---

---select * from t1 where id=3 for update;//blocked

---INSERT INTO t1 (id, name) VALUES (5, '5');//blocked

我们在两个会话里手动开启两个事务

在第一个事务里面，通过where id =1 锁住id=1的数据

在第二个事务里面，我们尝试给id=3的这一行数据加锁，这个加锁的操作被阻塞了。

我们再来操作一条不存在的数据，插入 id=5。它也被阻塞了。

我们可以推出整张表都被锁住了，InnoDB锁住的不是Record

2.2 有主键的索引的表

Transaction 1Transaction 2begin---

SELECT * FROM student where id=1 FOR UPDATE;---

---select * from t2 where id=1 for update; // blocked

---select * from t2 where id=4 for update; // OK

第一种情况，使用相同的id值去加锁，冲突；使用不同的id加锁，可以加锁成功。那么由此推出是不是锁住了id这个字段呢？

2.3 唯一索引

Transaction 1Transaction 2begin---

select * from t3 where name= '4' for update---

---select * from t3 where name = '4' for update;// blocked

---select * from t3 where id = 4 for update; //blocked

在第一个事务里面，我们通过 name 字段去锁定值是 4 的这行数据。

在第二个事务里面，尝试获取一样的排它锁，肯定是失败的

在这里我们怀疑InnoDB锁住的是字段，所以这次我换一个字段，用 id=4 去给这行数据加锁，又被阻塞了，说明锁住的是字段的这个推测也是错的，否则就不会出现第一个事务锁住了 name，第二个字段锁住 id 失败的情况。

从以上情况我们也能分析出，InnoDB锁住的是索引。

为什么没有索引会锁住整张表？

如果我们定义了主键(PRIMARYKEY)，那么InnoDB会选择主键作为聚集索引。

如果没有显式定义主键则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。

如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

所以，为什么锁表，是因为查询条件没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了

为什么给唯一索引的数据行加锁，主键索引也会被锁住?

之前我们也提过，在使用辅助索引的时候，所以的叶子节点中存储的该索引对应的主键值，最后还会利用该主键值去查找主键索引，然后得到数据。它跟我们检索数据的步骤是一样的，通过主键值找到主键索引，然后锁定。

3. 锁的算法

首先我们先建立一个student表

CREATE TABLE `student` (

`id` int(11) NOT NULL,

`stu_no` int(11) DEFAULT NULL,

`name` varchar(15) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `index_stu_no` (`stu_no`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制代码

插入4条数据

INSERT INTO `student` VALUES ('1', '3', 'test1');

INSERT INTO `student` VALUES ('5', '7', 'test2');

INSERT INTO `student` VALUES ('9', '12', 'test3');

INSERT INTO `student` VALUES ('15', '18', 'test3');

复制代码

此时记录有了，我们老看看有哪些常见的锁算法(下文select语句均为未提交事务状态)

Record Locks

记录锁，仅仅是把一条记录锁上。一般是通过主键或者唯一索引加锁。例如

select * from student where id =5 for update;//这就锁住了id=5的这条数据

复制代码

Gap Locks

间隙锁，锁住的是一个开区间。在RR隔离级别下，可通过参数innodb_locks_unsafe_for_binlog来控制间隙锁的开启。(0，开启，1关闭。在最新版已经废弃此参数)

该表的间隙有

(-∞,1)

(1,5)

(5,9)

(9,15)

(15,+∞)

现在来看看使用各类索引的情况

主键/唯一索引

select * from student where id =4 for update;

复制代码

将会锁住(1,5)这个区间

INSERT INTO `student` VALUES ('4', '8', 'test6');//block

update `student` set name = 'test1' where id =5;//success

复制代码

范围查询就会锁着这个范围中的间隙和这个所含有的记录

select * from student where id BETWEEN 4 and 10 for update;

复制代码

锁住(4,15)这个区间

INSERT INTO `student` VALUES ('4', '8', 'test6');//block

insert INTO `student` VALUES ('8', '11', 'test7');//block

update `student` set name = 'test1' where id =5;//block

insert INTO `student` VALUES ('13', '11', 'test8');//block

insert INTO `student` VALUES ('16', '18', 'test9');//success

复制代码

普通索引

在普通索引上进行加锁的时候，将有会产生以下几种间隙

(-∞,3)

(3,7)

(7,12)

(12,18)

(18,+∞)

当使用以下范围查询时候，锁住该范围区间的记录和间隙，将会锁住普通索引上(3,12)之间的记录。

select * from student where stu_no BETWEEN 5 and 8 for update;

复制代码

insert INTO `student` VALUES ('7', '3', 'test7');//success

insert INTO `student` VALUES ('8', '10', 'test7');//block

insert INTO `student` VALUES ('10', '13', 'test7');//success

复制代码

当使用等值查询的时候，除了锁定值外，还会会锁住该记录前后的间隙

例如以下的等值查询锁住(3,12)

当条件等于不存在的记录时，锁住的即是该间隙区间数据

select * from student where stu_no = 7 for update;

复制代码

insert INTO `student` VALUES ('8', '3', 'test7');//block

insert INTO `student` VALUES ('10', '10', 'test7');//block

insert INTO `student` VALUES ('11 '12', 'test7');//block

复制代码

Next-key Locks

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。