MySQL - 锁详解

一七令
270 阅读9分钟

前言

锁的作用是在并发情况下通过加锁的机制实现各种隔离级别，MySQL 有着多种锁的存在，可以按照锁粒度、锁机制、算法分类、模式、状态、特殊的自增锁等几种类型进行分类。 在这里插入图片描述

按照粒度分类

按锁粒度可分为：全局锁、表级锁、页级锁、行级锁。

全局锁：对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 MDL、DDL、更新操作语句等事务提交将被阻塞。通常用于全库的逻辑备份。

# 可使用以下语句进行加锁
FLUSH TABLES WITH READ LOCK;

# 可使用以下语句进行释放锁
UNLOCK TABLES;

表级锁：对当前操作的整张表加锁，分为表锁、元数据锁（meta data lock，MDL)两种。

元数据锁是MySQL5.5版本引入，用于解决或者保证DDL操作与DML操作之间的一致性。当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL写锁。

# 表锁加锁
lock tables table_name read/write；

# 释放表锁
unlock tables;

# 可使用语句查看表是否加锁， In_use = 1时候代表该表上有锁存在。
SHOW OPEN TABLES;

e.g.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.01 sec)
 
mysql> lock tables test read;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show open tables;
+----------+---------------------------+--------+-------------+
| Database | Table                     | In_use | Name_locked |
+----------+---------------------------+--------+-------------+
| test     | test                      |      1 |           0 |
+----------+---------------------------+--------+-------------+
22 rows in set (0.00 sec)

页级锁：锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多；行级冲突少，但速度慢。因此采取了折中的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁。

行级锁：粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。只有 InnoDB 支持行级锁，分为共享锁和排他锁。

其中行级锁并不是直接锁记录，而是锁索引。索引分为主键索引（聚集（clustered）索引）和非主键索引（非聚集（unclustered）索引）两种，所以加锁分为两种情况：

  1. 使用主键索引，行级锁就会锁定这条主键索引；
  2. 使用非主键索引，行级锁先锁定该非主键索引，再锁定对应的主键索引。 在UPDATE、DELETE操作时，行级锁不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即临键锁（next-key locking）。

Innodb 是否使用行锁，需要注意以下几种情况：

  1. 不通过索引条件查询的时候，InnoDB使用的是表锁。
  2. 行锁是针对索引加的锁，所以尽管访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。
  3. 即便在条件中使用了索引字段，但是执行计划选择了全表扫描情况下，innodb 使用的是表锁而非行锁。

按机制分类

按属性分类，可分为共享锁（S 锁）和排他锁（X 锁）。

共享锁(Shared) 又称为S 锁、读锁，即多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。只有当数据上的读锁被释放后，其他事务才能对其添加写锁。

共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免 不可重读 的问题的出现。

# 开启共享锁
select * from table  lock in share mode;

排它锁（Exclusive） 又称为X 锁、写锁、独占锁。当一个事务对数据加上写锁后，其他事务既不能对该数据添加读锁，也不能对该数据添加写锁，写锁与其他锁都是互斥的。

写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免 脏读 问题的产生。

innodb 在发生 DML(update,delete,insert)) 语句时默认会数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用 select ...f or update语句。

按加锁方式分类

可分为隐式（自动）锁、显示锁。

隐式加锁：

  1. InnoDB 自动加意向锁。
  2. DML 语句自动添加排他锁。

显示加锁： 3. 共享锁（S）：SELECT * FROM table WHERE … LOCK IN SHARE MODE。 4. 排他锁（X) ：SELECT * FROM table WHERE … FOR UPDATE。

按算法分类

可分为记录锁、间隙锁、临键锁。

记录锁（Record Lock）：

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

记录锁也称之为行锁，锁住的是索引记录，如果使用索引作为条件命中了记录，那么就是记录锁。被锁住的记录不能被别的事务插入相同的索引键值，修改和删除。

间隙锁（Gap Lock）：

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

锁定一段范围内（左开右开的区间）的索引记录。使用间隙锁锁住的是一个区间，不仅仅是这个区间中的每一条数据。本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。间隙锁只存在可重复读隔离。

间隙锁是不互斥的，故不区分共享间隙锁或互斥间隙锁。即两个事务可以同时持有包含共同间隙的间隙锁，不管两个间隙锁的间隙区间完全一样，还是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。

在这里插入图片描述 如上图中：1、5、6、9代表是已经有的记录主键，中间的部分是空着的。

  1. 如 select * from table where id = 3 或 select * from where id between 1 and 5，这时候其他事务不能在区间（1,5）之间插入新的记录。
  2. 如 select * from table where id = 20，这时区间（9，+∞）之间是被锁住的。如 insert id = 13 的语句将会被阻塞。

临键锁（Next-key Lock）：

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

临键锁相当于记录锁+间隙锁的组合。是为了解决 幻读 问题而出现的。其锁住的范围是索引记录，和索引区间。

临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。 在这里插入图片描述 如上图中：1、5、6、9代表是已经有的记录主键，中间的部分是空着的。

  1. 如 select * from table where id > 2 and id < 9, 该范围内包含了存在和不存在的记录，故会锁定（1, 6]、（6,9] 区间，其他事务不能在该区间插入数据。

按模式分类

可分为悲观锁和乐观锁。其中乐观锁比较特殊，是借助业务逻辑来实现的锁机制。

乐观锁 乐观锁一般会在数据表中增加一个字段来表示是否被其他事务更新，比如更新状态、版本号等等。乐观假想数据不会被更新，当此次事务需要提交时候判断该条数据是否在本次事务开始后提交前被其他事务更改了。

如被更改了，则返回给业务逻辑判断如何处理。这样的优点就是能减少锁使用，提供性能。

悲观锁 悲观锁可以看作乐观锁的相对面，基于一种悲观的态度类来防止一切数据冲突，它是以一种预防的姿态在修改数据之前把数据锁住，然后再对数据进行读写。

在它释放锁之前任何请求都不能对其数据进行操作，直到前面锁持有者把锁释放后。其他需要对数据加锁的才可以对数据进行加锁进行操作，通常情况数据库本身锁的机制都是基于悲观锁的机制实现的。

其优缺点都很明显： 优点：可以完全保证数据的独占性和正确性。 缺点：加锁释放锁的过程会造成消耗，所以性能相对不高，且会增加产生死锁的风险。

按状态分类

可分为意向共享锁(IS)、意向排它锁(IX)。

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。

  1. Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
  2. Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

从上文不难得出：共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

  1. 获得行的共享锁必须先获得表的意向共享锁。
  2. 获得行的排他

共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

排他锁（X）意向排他锁（IX）共享锁（S）意向共享锁（IS）
排他锁（X ）互斥互斥互斥互斥
意向排他锁（IX）互斥可并行互斥可并行
共享锁（S）互斥互斥可并行可并行
意向共享锁（IS）互斥可并行可并行可并行

从上表加粗部分可看出意向锁之间不是互斥的，可以并行进行。

意向锁的存在价值在于在定位到特定的行所持有的锁之前，提供一种更粗粒度的锁，可以大大节约引擎对于锁的定位和处理的性能，因为在存储引擎内部，锁是由一块独立的数据结构维护的，锁的数量直接决定了内存的消耗和并发性能。

如：事务 A 对表 T 的进行 DML 操作（加X锁）这时也会对表 T 加意向排它锁，在事务 A 提交之前，加入有事务 B 也进行 DML 操作，此时表级别的意向排它锁就能告诉事务 B 需要等待（表 T 上有意向排他锁），而不需要再去行级别判断。

自增锁

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

从文中不难得出：自增锁是一种特殊的表级锁，由插入到带有AUTO_INCREMENT列的表中的事务使用。

当发生类 insert 语句时候，大致上可分为几种情况分析：

Simple inserts 可以预先确定要插入的行数（当语句被初始处理时）的语句。 这包括没有嵌套子查询的单行和多行 INSERT 和 REPLACE 语句，但不包括INSERT ... ON DUPLICATE KEY UPDATE。

Bulk inserts 事先不知道要插入的行数（和所需自动递增值的数量）的语句。 这包括INSERT ... SELECT、REPLACE ... SELECT和LOAD DATA语句，但不包括纯INSERT。

InnoDB 在处理每行时一次为 AUTO_INCREMENT 列分配一个新值。

Mixed-mode inserts

  1. Simple inserts 中部分指定了自增值。
  2. INSERT ... ON DUPLICATE KEY UPDATE。其中 AUTO_INCREMENT 列的分配值不一定会在 UPDATE 阶段使用，所以此类语言比较浪费自增键。

innodb 提供了自增锁三种锁定模式，由参数 innodb_autoinc_lock_mode 控制。

  1. innodb_autoinc_lock_mode=0 该模式下上面三种类型 insert 语句在开始时候都会得到一个表级别的 aotu_inc 锁，在语句结束时候才进行释放。注意是每条语句，一个事务中可能包含多条语句。基于语句复制(statement-based replication) 安全，自增值连续，并发不高。
  2. innodb_autoinc_lock_mode=1 Innod 默认模式，该模式在兼用 0 模式下对 Simple inserts 类进行优化。Simple inserts 在插入前可以知道具体的条数，故直接生成对应记录数的连续值，语句得到了相应的值后就可以提前释放锁。基于语句复制(statement-based replication) 安全，自增值连续，并发中等。
  3. innodb_autoinc_lock_mode=2 该模式下没有 auto_inc 锁，性能最好。但是基于二机制ROW语句复制或恢复是不安全的，mysql 推荐 binlog 为 row模式，故比较少用该模式。
avatar
文章
阅读
粉丝