通过MVCC和锁来支持事务的隔离性,控制并发安全性,Innodb提供8种锁。Innodb的锁可以按照两个维度进行划分,根据可以根据锁的粒度将innodb分为行锁和表锁,根据锁之间的兼容性可以分为共享锁(S)和排它锁(X)。
按照锁粒度对Inndb中锁进行划分:
在表锁和行锁中都存在共享锁和排它锁,表锁中共享锁/排它锁加锁方式:
LOCK TABLES table_name write/read;
行锁中共享锁和排它锁加锁方式:
SELECT * FROM table_name LOCK IN SHARE MODE; - -共享锁
SELECT * FROM table_name FOR UPDATE; - - 排它锁
1 共享锁(S)和排它锁(X)
2 意向锁(IS/IX)
为了避免加锁冲突,加表级锁时需要检查表中是否存在行级锁,逐个遍历效率太慢,所以加行级锁的同时,同时加表级意向锁,缩短加行级锁时间。
3 行级锁
当WHERE 查询条件是唯一索引(UNION )列(逻辑规定了查询出来的结果不大于1时),会在查询单行记录加行级锁。
4 间隙锁
间隙锁解决了行级锁在多事物同时读写时出现的幻读问题。例如事务TX1根据age 查询内容,在TX1还没提交情况下,在TX1查询区间内插入一条新的记录,因为间隙锁的原因,INSERT操作会被阻塞,保证TX1在两次相同查询得到相同的结果。
5 next-key锁
行锁和间隙锁对应解决两类幻读问题,next-key Lock 是行锁和间隙锁的结合,next-key可以同时解决两类幻读问题。
6 自增锁(auto_inc lock)
被auto_increment修饰的列能够自增,并且所在列不允许重复。
create table roles(
id int auto_increment primary key,
name varchar(10),
age int
);
插入分类 | sql 语句 |
---|---|
简单插入 | INSERT INTO roles values (‘tom’,12) |
批量插入 | INSERT INTO roles(name,age) from roles where age > 20 |
混合插入 | INSERT INTO roles(id,name,age) values (“spider,man”), (10,‘jerry’,12) |
自增锁有三种模式,可以通过修改innodb_autoinc_lock_mode的值切换三种模式:
innodb_autoinc_lock_mode | 简单插入 | 批量插入 | 混合插入 | 备注 |
---|---|---|---|---|
0 | 给表加锁,语句结束释放锁 | 同左 | 同左 | 能够保证基于主从复制数据一致性 |
1 | 使用mutex控制并发,得到id值即释放锁(不用等到语句结束) | 目标和数据源同一个表,当数据源获得选数据第一列的共享锁时,开始给目标源加行级锁直到语句结束;当目标和数据源同一个表,当数据源获得所有选择数据的共享锁是,开始给整个表加锁,直到结束 | 一下子创建和values值相等的n个连续id,丢失id数等于已经分配记录ID数 | 能够保证基于主从复制数据一致性,mysql 8.0之前默认值 |
2 | 用mutex | 用mutex,只保证获取到id全局唯一增长,多个事务在语句执行中可以交替执行,但是同一个语句中会出现获取到Id不连续 | 同左 | 可以保证row-based 和meta-data based主从数据复制一致性,8.0之后默认值 |
7 插入意向锁(insert intention)
插入意向锁是一种特殊的间隙排它锁,多个事务在一个间隙插入记录时,每个事务首先尝试在获得待插列的间隙排它锁,如果多个事务插入记录索引id不重复,则彼此之间不会形成阻塞,提高了插入并发性。
8 空间索引的谓词锁
三位空间数据不能使用B+树进程存储,一般使用R Tree 存储空间数据的索引,对应的,对空间索引加锁需要用到谓词锁。
参考
1、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks