Mysql的锁类型、锁模式、加锁方式

最新推荐文章于 2024-07-18 22:33:35 发布

jzzw

最新推荐文章于 2024-07-18 22:33:35 发布

阅读量6.2k

点赞数 4

分类专栏： Mysql

本文链接：https://blog.csdn.net/u014710633/article/details/90486467

版权

Mysql 专栏收录该内容

44 篇文章 2 订阅

订阅专栏

Mysql的锁：

锁类型(lock_type)：

表锁：
通过Mysql服务实现，加锁：lock table xxx read/write，解锁：unlock tables;
当会话将表加上锁后，在锁释放之前，会话只能访问这些加锁的表

表锁里又可以分为读锁和写锁。

表锁的加锁规则：
   读锁：
       1.持有读锁的会话可以读表，但不能写表;
       2.允许多个会话同时持有读锁，其他会话就算没有给表加读锁，也是可以读表的，但是不能写表，申请写锁时会阻塞
   写锁：
       1.持有写锁的会话既可以读表，也可以写表;
       2.只有持有写锁的会话才可以访问该表，其他会话访问该表会被阻塞，申请读锁或写锁时都会阻塞

表锁的释放规则：
   1.unlock tables释放
   2.会话在持有表锁的情况下执行lock table语句，会释放掉之前持有的锁
   3.会话在持有表锁的情况下执行start transaction或者begin开启一个新事务，会释放掉之前持有的锁
   4.会话连接断开，会释放掉之前持有的锁

行锁：
由存储引擎(其实也就InnoDB有)实现，作用是为了提高并发能力

行锁的类型：
Mysql源码：

#define LOCK_TABLE  16  /* table lock */
#define LOCK_REC    32  /* record lock */
 
/* Precise modes */
#define LOCK_ORDINARY   0   
#define LOCK_GAP    512 
#define LOCK_REC_NOT_GAP 1024   
#define LOCK_INSERT_INTENTION 2048

LOCK_ORDINARY(Next-Key Lock)：
记录锁+GAP锁，锁一条记录及其之前的间隙，左开右闭区间，闭的那个就是记录(InnoDB的默认加锁方式是Next-Key Lock，当索引有唯一性质时，Next-Key Lock自动降级为Record Lock)

LOCK_GAP：
间隙锁，锁两记录直接的间隙(GAP)，防止insert插入，目的是为了避免幻读

LOCK_REC_NOT_GAP：
记录锁，只锁记录不锁GAP

LOCK_INSERT_INTENTION:
插入意向锁，插入记录时使用，是LOCK_GAP的一种特例，和表级意向锁是两个概念，插入意向锁之间不冲突，但是插入意向锁和Next-Key Lock、GAP LOCK冲突，也正是这种冲突，阻止了记录的插入，从而避免了幻读。

锁模式(lock_mode)：

Mysql源码：

/* Basic lock modes */
enum lock_mode {
    LOCK_IS = 0, /* intention shared */
    LOCK_IX,    /* intention exclusive */
    LOCK_S,     /* shared */
    LOCK_X,     /* exclusive */
    LOCK_AUTO_INC,  /* locks the auto-inc counter of a table in an exclusive mode*/
    ...
};

首先我们介绍意向锁这个概念：
意向锁：
为了方便检测表锁和行锁的冲突而引入的锁，可以算是表级锁，当事务需要读或者某条记录时，需要先在表上加意向锁，然后才能在记录上加读锁或写锁;
LOCK_IS：读意向锁;
LOCK_IX：写意向锁;

LOCK_S：读锁，又称共享锁;
LOCK_X：写锁，又称排他锁;

LOCK_AUTO_INC：
自增锁,当表中有自增列的时候会用到，保证自增列值的唯一性，一种特殊类型的表锁，有如下特点：
   1.互不兼容，同一张表任意时刻都最多只能有一个自增锁
   2.不遵循2PC，锁在insert语句执行结束时释放(可以提高并发插入性能)
   3.自增值不会回滚，所以自增列的值可能会不连续

显然，AUTO_INC表锁会导致并发插入的效率降低，为了提高插入的并发性，MySQL从5.1.22版本开始，引入了一种可选的轻量级锁(mutex)机制来代替AUTO_INC锁，可以通过参数innodb_autoinc_lock_mode控制分配自增值时的并发策略。
innodb_autoinc_lock_mode = 0(traditional lock mode)
       使用传统的 AUTO_INC 表锁，并发性比较差。
innodb_autoinc_lock_mode = 1(consecutive lock mode)
       MySQL 默认采用这种方式，是一种比较折中的方法。
       MySQL 将插入语句分成三类：Simple inserts、Bulk inserts、Mixed-mode inserts。
       通过分析 INSERT 语句可以明确知道插入数量的叫做 Simple inserts，譬如最经常使用的 INSERT INTO table VALUE(1,2) 或 INSERT INTO table VALUES(1,2), (3,4);
       通过分析 INSERT 语句无法确定插入数量的叫做 Bulk inserts，譬如 INSERT INTO table SELECT 或 LOAD DATA 等;
       还有一种是不确定是否需要分配自增值的，譬如 INSERT INTO table VALUES(1,'a'), (NULL,'b'), (5, 'C'), (NULL, 'd') 或 INSERT ... ON DUPLICATE KEY UPDATE，这种叫做 Mixed-mode inserts。

       Bulk inserts 不能确定插入数使用表锁;
       Simple inserts 和 Mixed-mode inserts 使用轻量级锁 mutex，只锁住预分配自增值的过程，不锁整张表;
       Mixed-mode inserts 会直接分析语句，获得最坏情况下需要插入的数量，一次性分配足够的自增值，缺点是会分配过多，导致浪费和空洞。
       这种模式的好处是既平衡了并发性，又能保证同一条 INSERT 语句分配的自增值是连续的。
innodb_autoinc_lock_mode = 2(interleaved lock mode)
       全部都用轻量级锁 mutex，并发性能最高，按顺序依次分配自增值，不会预分配。
       缺点是不能保证同一条 INSERT 语句内的自增值是连续的，这样在复制(replication)时，如果binlog_format为statement-based(基于语句的复制)就会存在问题，因为是来一个分配一个，同一条 INSERT 语句内获得的自增值可能不连续，主从数据集会出现数据不一致。所以在做数据库同步时要特别注意这个配置。

这几种锁的兼容性(S\X\IS\IX\AI)：
   1.意向锁之间互不冲突;
   2.S锁只和S/IS锁兼容，和其他锁都冲突;
   3.X锁和其他所有锁都冲突;
   4.AI锁只和意向锁兼容;

加锁操作：

先了解一些概念：
MVCC(Multi-Version Concurrency Control)：
基于多版本的并发控制协议，是可以提高系统并发能力

MVCC中的读：
快照读(Snapshot Read)：普通的select读，读数据记录的快照版本，不加锁
   select * from t where xxx;
当前读(Current Read)：特殊的select读，读数据记录的最新版本，且在读取完成后需要保证数据不被其他事务修改，因此要加锁
   select * from t where xxx for update;---------X锁
   select * from t where xxx lock in share mode;---------S锁
   update / insert / delete---------X锁

InnoDB与MySQL Server的交互，是一条一条进行的，因此加锁也是一条条加上去的。
update / delete流程：
Mysql发起当前读--->InnoDB进行当前读，返回数据并加锁--->Mysql Server发起修改--->InnoDB修改，返回修改状态--->循环
insert流程：
涉及到唯一性索引的校验，所以要发起当前读

两阶段锁协议(Two-Phase Locking、2PC)：
事务中涉及到锁操作分为两阶段：加锁阶段和解锁阶段，且两阶段无交集

RR / RC 隔离级别下，针对当前读时锁的状态：
Read Uncommited：当前读时，对读取到的记录加锁(记录锁)，存在幻读现象。
Read Committed：当前读时，对读取到的记录加锁(记录锁)，同时要对读取的范围加锁，保证新的满足查询条件的记录不能够插入(间隙锁)，不存在幻读现象。

表的几种访问方式下的加锁操作：

1.主键访问：
RC模式：
只需要给主键上查询对应的记录加锁即可，如无满足条件的则不加锁
RR模式：
   1.等值查询：加Next-key LOCK，如果键值满足条件，Next-key LOCK退化为行锁，如果不满足条件，Next-key LOCK退化为GAP锁
2.范围查询：加Next-key LOCK，一直遍历到第一个不满足查询条件的键值为止，且锁不退化(也就是说它甚至会把第一个不满足查询条件的那条记录也锁上。。)
2.唯一性二级索引访问：
   和主键访问的区别是一个是锁主键索引，一个是锁二级索引+对应的主键记录
3.非唯一性二级索引访问：
   RC模式：
       只需要给二级索引上对应的记录和对应主键索引上的记录加锁
   RR模式：
  1.等值查询：加Next-key LOCK，一直遍历到第一个不满足查询条件的键值为止，然后把这个Next-key LOCK退化为GAP锁
2.范围查询：加Next-key LOCK，一直遍历到第一个不满足查询条件的键值为止，且锁不退化(也就是说它甚至会把第一个不满足查询条件的那条记录也锁上。。)
4.无索引访问(也可以叫做主键索引全扫描、全表扫描)：
   RC模式：
       主键上所有记录行加锁，返回给Server层，Server层进行过滤，对不满足条件的记录，调用unlock_row方法放锁
   RR模式：
       主键上所有记录行和GAP加锁，返回给Server层，Server层进行过滤，且事务结束之前不会释放(保证binlog记录顺序的正确性，且要遵守两段锁协议)
       设置innodb_locks_unsafe_for_binlog参数为ON可以使用semi-consistent read(和rc级别那样，对不符合条件的记录及时解锁)，但是会带来其他问题(禁用了GAP锁)，不建议使用。

针对一个复杂的SQL，首先需要提取其where条件。
Index Key确定的范围，需要加上GAP锁；
Index Filter给定的条件何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6后支持了Index Condition Pushdown，则在index上过滤。若不支持ICP，不满足 Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁；
Table Filter过滤条件，无论是否满足，都需要加X锁。