MySQL(八)MySQL锁

276 阅读8分钟

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%';

    Snipaste_2021-08-31_22-36-46.png

    • 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:系统启动后到现在总共等待的次数;