MySQL锁介绍
按照锁的粒度
MySQL主要包含三种类型(级别)的锁定机制
-
全局锁:锁的是整个database。由MySQL的SQL layer层实现的
-
表级锁:锁的是某个table。由MySQL的SQL layer层实现的
-
行级锁:锁的是某行数据,也可能锁定行之间的间隙。由某些存储引擎实现,比如InnoDB
按照锁的功能
共享锁和排他锁
-
共享锁Shared Locks(S锁)
兼容性:加了S锁的记录,允许其他事务再加S锁,不允许其他事务再加X锁
加锁方式:select…lock in share mode
-
排他锁Exclusive Locks(X锁)
兼容性:加了X锁的记录,不允许其他事务再加S锁或者X锁
加锁方式:select…for update
全局锁
全局锁就对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的MDL的写语句,DDL语句,已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性
加全局锁的命令为:flush tables with read lock;
释放全局锁的命令为:unlock tables;
,或者断开加锁session的连接,自动释放全局锁
MySQL表级锁
表级锁介绍
MySQL的表级锁有四种
-
表读、表写锁
-
元数据锁(meta data lock,MDL)
-
意向锁 Intention Locks(InnoDB)
-
自增锁(AUTO-INC Locks)
表读S、表写锁X
表锁相关命令
-
MySQL 实现的表级锁定的争用状态变量
show status like 'table%';
-
table_locks_immediate:产生表级锁定的次数;
-
table_locks_waited:出现表级锁定争用而发生等待的次数;
-
-
表锁有两种表现形式
-
表共享读锁(Table Read Lock)
-
表独占写锁(Table Write Lock)
-
-
手动增加表锁
lock table 表名称 read(write),表名称2 read(write),其他;
-
查看表锁情况
show open tables;
-
删除表锁
unlock tables;
元数据锁
元数据锁介绍
MDL不需要显式使用,在访问一个表的时候会被自动加上。MDL的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的
因此,在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁
-
读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查
-
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行
自增锁(AUTO-INC Locks)
AUTO-INC锁是一种特殊的表级锁,发生涉及AUTO_INCREMENT列的事务性插入操作时产生
MySQL行级锁
行级锁介绍
MySQL的行级锁,是由存储引擎来实现的,这里我们主要讲InnoDB的行级锁
InnoDB行锁是通过给索引上的索引项加锁来实现的,因此InnoDB这种行锁实现特点意味着:只有通过索引条件检索的数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
-
InnoDB的行级锁,按照锁定范围来说,分为四种:
-
记录锁(Record Locks):锁定索引中一条记录
-
间隙锁(Gap Locks):要么锁住索引记录中间的值,要么锁住第一个索引记录前面的值或者最后一个索 引记录后面的值
-
临键锁(Next-Key Locks):是索引记录上的记录锁和在索引记录之前的间隙锁的组合(间隙锁+记录 锁)
-
插入意向锁(Insert Intention Locks):做insert操作时添加的对记录id的锁
-
-
InnoDB的行级锁,按照功能来说,分为两种:
-
共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
-
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁
-
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁,事务可以通过以下语句显示给记录集加共享锁或排他锁
手动添加共享锁(S)
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
手动添加排他锁(x)
SELECT * FROM table_name WHERE ... FOR UPDATE
意向锁 Intention Locks
意向锁介绍
InnoDB也实现了表级锁,也就是意向锁,意向锁是mysql内部使用的,不需要用户干预。意向锁和行锁可以共存,意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时,需要先检索该范是否某些记录上面有行锁
意向锁的作用
当我们需要加一个排他锁时,需要根据意向锁去判断表中有没有数据行被锁定(行锁)
-
如果意向锁是行锁,则需要遍历每一行数据去确认
-
如果意向锁是表锁,则只需要判断一次即可知道有没数据行被锁定,提升性能
意向锁和共享锁、排他锁的兼容关系
意向锁相互兼容,因为IX、IS只是表明申请更低层次级别元素(比如 page、记录)的X、S操作
因为上了表级S锁后,不允许其他事务再加X锁,所以表级S锁和X、IX锁不兼容
上了表级X锁后,会修改数据,所以表级X锁和 IS、IX、S、X(即使是行排他锁,因为表级锁定的行肯定包括行级速订的行,所以表级X和IX、行级X)不兼容
注意:上了行级X锁后,行级X锁不会因为有别的事务上了IX而堵塞,一个mysql是允许多个行级X锁同时存在的,只要他们不是针对相同的数据行
记录锁(Record Locks)
-
记录锁, 仅仅锁住索引记录的一行,在单条索引记录上加锁
-
record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引
所以说当一条sql没有走任何索引时,那么将会在每一条聚合索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的
-- 加记录共享锁
select * from t1_simple where id = 1 lock in share mode;
-- 加记录排它锁
select * from t1_simple where id = 1 for update;
间隙锁(Gap Locks)
-
区间锁, 仅仅锁住一个索引区间(开区间,不包括双端端点)
-
在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身
-
间隙锁可用于防止幻读,保证索引间的不会被插入数据
临键锁(Next-Key Locks)
-
record lock + gap lock, 左开右闭区间,例如(5,8]
-
默认情况下,innodb使用next-key locks来锁定记录。select … for update
-
但当查询的索引含有唯一属性的时候,Next-Key Lock 会进行优化,将其降级为Record Lock,即仅锁住索引本身,不是范围
-
Next-Key Lock在不同的场景中会退化
行锁加锁规则
-
主键索引
-
等值查询
-
命中记录,加记录锁
-
未命中记录,加间隙锁
-
-
范围查询
-
没有命中任何一条记录时,加间隙锁
-
命中1条或者多条,包含where条件的临键区间,加临键锁
-
-
-
辅助索引
-
等值查询
-
命中记录,命中记录的辅助索引项+主键索引项加记录锁,辅助索引项两侧加间隙锁
-
未命中记录,加间隙锁
-
-
范围查询
-
没有命中任何一条记录时,加间隙锁
-
命中1条或者多条,包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁
-
-
插入意向锁(Insert Intention Locks)
-
插入意向锁是一种Gap锁,不是意向锁,在insert操作时产生
-
在多事务同时写入不同数据至同一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待
-
假设有一个记录索引包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突
-
插入意向锁不会阻止任何锁,对于插入的记录会持有一个记录锁
锁相关参数
Innodb所使用的行级锁定争用状态查看
show status like 'innodb_row_lock%';
- Innodb_row_lock_current_waits:当前正在等待锁定的数量;
- Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
- Innodb_row_lock_time_avg:每次等待所花平均时间;
- Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
- Innodb_row_lock_waits:系统启动后到现在总共等待的次数;