​MYSQL锁机制

MYSQL锁机制

MYSQL的innodb引擎可以支持实例锁(数据库锁)，表锁(lock table,mdl锁)，行锁(record-lock)，间隙锁（gap-lock）,临键锁(next-key lock)，实例锁锁和表锁很少用到，有一点需要注意的是线上环境的表结构修改会加mdl写锁，阻塞表的增删改查，因此需要注意。

创建表test_lock，其中id为主键,number为唯一索引,age为普通索引。

create table test_lock(
    id int(4) auto_increment,
    age int(4),
    number int(4),
    primary key (id),
    key (age),
    unique key (number)
);

唯一索引

唯一索引上的等值查询,加锁为行锁。

Session1中 将number为30的数据年龄增加1，且事务未提交。

Session2中修改number为20和40的数据均可成功，且插入number为31的数据可以成功,但是修改number为30的数据被阻塞，这是因为此时number为30的行被Session1的修改持有写锁，无法修改。

普通索引

普通索引上的等值查询,加锁为next-key lock，且因为索引不唯一,会一直扫描到第一个不满足条件的为止,最后一个next-key 退化为gap lock。

Session1将age为20的行的number增加1，且事务未提交。

Session2将age为18(前一条)和23(后一条)的行number增加1都可以成功,但是插入age为19(前一行)和21(后一行的数据均会被阻塞),这是因为age是非唯一索引，Session1修改age为20的行会先锁住20这个next-key(18,20]，因为索引不唯一，需要继续向后扫描到23这个next-key，因为23不满足 age=20条件,因此这个next-key lock退化为gap lock，只锁定了(18,20],和(20,23)这两个区间,因此18和23的更新可以成功,19和21的插入被阻塞，这一点与唯一索引有区别。

非索引列

首先删除test_lock表number列的唯一索引。

drop index idx_number on test_lock

没有索引的情况下mysql会顺序扫描主键索引,因此会锁定整张表。

Session1将number为100的行age增加1且未提交。

此时Session无论是更新id为1或6的数据都会被阻塞，甚至插入数据也会被阻塞，整张表的写操作都会处于阻塞状态，因此更新数据的where条件不走索引的话，是一个高危操作。

总结

1.加锁的基本单位是 next-key lock。

2.查找过程中访问到的对象都会加锁。

3.索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

4.索引上的等值查询，向后遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

5.唯一索引上的范围查询会访问到不满足条件的第一个值为止。

6.锁是加在索引上的。