MySQL - 数据库锁机制

2022-09-28 本文已影响0人右耳菌

1. 术语简介

行级锁(存储引擎实现)
- 行共享锁、独占锁
- 间隙锁
- Next-Key锁
- 插入意向锁
- Predicate Locks
表级锁
- MDL元数据锁(metadata lock)
- 意向共享锁、意向排它锁
- 表共享锁、独占锁
- 自增锁

2. 行锁之独占和共享

共享锁(S锁,读锁)
共享(s)锁允许持有锁的事务进行读取。
例:select… lock in share mode、insert into select…语句,对SELECT的表上扫描到的数据加LOCK_S锁
数据行被事务添加S锁后，其他事务可以添加S锁，但是不能添加×锁
排他锁(X锁，写锁,独占锁)
锁允许持有锁的事务更新或删除行。
例: update、delete、select … from update
数据行被事务添加X锁后，其他事务不能再为该行数据添加任意类型的锁。

注：只要有锁就会有共享和排他之分

3. 间隙锁

gap间隙锁的定义:间隙锁是对索引记录之间的间隙的锁定。使用唯一特性的字段查询一行数据不使用间隙锁。

NEXT-KEY 锁
单记录锁和间隙锁的组合，锁定一个范围、以及锁定记录本身;
InnoDB默认情况下用这个来实现范围的锁定。

即遍历过的值都进行了锁定，含其中的间隙。但是如主键这种唯一值的，不会使用间隙锁。

其他的相关描述

单纯的间隙锁，【锁不存在的数据】
【Innodb中的实现】NEXT-KEY锁，特殊的间隙锁实现，单记录锁和间隙锁的组合
简单理解: 锁定遍历过的范围，锁定遍历过的已存在记录
特例: 使用唯一特性的字段查询一行数据不使用间隙锁（如主键）
为啥叫NEXT-KEY？要查询出需要的N条数据，需要遍历N+1次;(一直找一直找，找到一条不满足条件的为止:)

【注意】把事务的隔离级别降级为读提交(Read Committed，RC)，间隙锁则会自动失效

插入意向锁
间隙锁的一种插入意图锁是由INSERT操作在插入行之前设置的一种间隙锁
多个事务，在同一个索引，同一个范围区间进行插入记录的时候，如果插入的位置不冲突，不会阻塞彼此

如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。
间隙锁只存在特定的隔离级别中。

4. Predicate Locks

Innodb支持对包含空间列的列进行SPATIAL索引。
多维数据中没有绝对排序概念，不好界定锁定范围。

NEXT-KEY 锁机制不能很好地支持REPEATABLE READ或SERIALIZABBLE事务隔离级别。
为了支持具有SPATIAL索引的表的隔离级别，InnoDB引入了Predicate Locks。

Spatial索引包含最小外接矩形值，因此InnoDB通过在用于查询的MBR值上设置Predicate Locks来强制对索引进行一致读取，其他事务不能插入或修改与查询条件匹配的行

简单理解: 以查询条件为依据来加锁，而不是通过数据本身的信息。

5. Server层的MDL锁

元数据锁不依赖任何存储引擎。此锁不需要显示调用，只要有事务在执行，对应的连接就会取得元数据锁。当事务执行的时候理论上是不能容忍表结构在中途发生改变。

分为MDL 读锁和写锁两种。读锁和写锁互斥;

select和DML语句申请读锁。MDL读锁之间不冲突，所以多个select和DML语句可以同时执行;

DDL语句申请写锁，获取写锁时需要等待读锁释放，且申请写锁会阻塞后续所有MDL锁的获取;

6. 表锁之独占和共享

共享锁(S)、独占锁(X)
当加了LOCK_X表级锁时，所有其他的表级锁请求都需要等待。
执行LOCK TABLE tbname READ时，会加LOCK_S锁;
表空间discard或者import、LOCK TABLE tbname WRITE时需要加LOCK_X锁;
意向共享锁(IS)
事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁。
意向互斥锁(IX)
事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁。

7. 表锁之自增锁

Auto-inc锁是一种特殊的表级锁，和AUTO_INC、表级S锁以及X锁不相容。
AUTO_INC的加锁逻辑和InnoDB的锁模式相关。具体行为受到参数innodb_autoinc_lock_mode的影响。

模式-0-传统锁定模式
以SQL为单位，执行insert…SQL时，加表级自增锁，执行结束释放锁，保证连续性。例如:批量插入很耗时，这期间其他SQL可能就拿不到锁了。
模式-1-自动模式(InnoDB默认)
未知数量的批量插入采用传统模式，以SQL为单位;
普通的插入(mysql能计算出行数)，采用轻量级的互斥锁，分配自增值后就释放。
模式-2
分配自增值时，加锁，分配完毕释放锁。因为事务并行执行，可能导致批量插入时自增值不连续。

如果觉得有收获，欢迎点赞和评论，更多知识，请点击关注查看我的主页信息哦~