数据库系列（8）-Mysql中的各种锁

ゞ浴缸里的玫瑰 2023-10-09 13:49 48阅读 0赞

#### 文章目录 ####

*   *  Mysql 锁分类
     *   *  按照粒度分类
         *   *  1. 全局锁
             *  2. 表级锁
             *  3. 页级锁
             *  4. 行级锁
         *  按属性分类
         *   *  1. 共享锁
             *  2. 排他锁
         *  按加锁方式分类
         *  按照算法分类
         *   *  1. 间隙锁
             *  2. 临键锁
             *  3. 记录锁
         *  按照模式分类
         *   *  1. 悲观锁
             *  2. 乐观锁
         *  按照状态分类
         *   *  1. 意向共享锁
             *  2. 意向排它锁

### Mysql 锁分类 ###

在之前，我们了解了数据库事务和各种事务隔离级别，在并发的情况下，数据库是通过锁的机制实现隔离级别。

数据库中存在各种各样的锁，我们在说某种锁的时候，一定要先说明，按照什么分类，分为哪些锁。

锁的目的和并发编程中锁的一样，是为了解决并发情况下，对同一个资源访问限制，旨在强制实施互斥排他、并发控制策略。

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16]

#### 按照粒度分类 ####

按锁的粒度分类，全局锁、表级锁、页级锁、行级锁。

##### 1. 全局锁 #####

全局锁就对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的MDL、DDL语句、更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

通过以下SQL 可以添加全局锁：

FLUSH TABLES WITH READ LOCK;

然后其他事务进行操作时，可以看到一直处于阻塞状态，只能读取数据：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
可以通过以下SQL 释放全局锁：

UNLOCK TABLES;

##### 2. 表级锁 #####

表级锁会对当前操作的整张表加锁，最常使用的 MyISAM 与 InnoDB 都支持表级锁定。

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

添加表锁，格式如下

lock tables xxx read/write；

比如以下方式表示，对表添加只读锁，表加上了表读锁之后，本线程和其他线程都可以读数据，本事务写数据会报错，其他事务写数据会阻塞。

LOCK TABLES account_tbl READ;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]

比如以下方式表示，对表添加只写锁，表加上了表写锁之后，当前线程的读操作会被阻塞，写可以正常执行；其他线程的读写操作都会被阻塞。

LOCK TABLES account_tbl WRITE;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 3]

元数据锁（Metadata Locking，简称：MDL锁），MySQL5.5版本引入了MDL锁，用于解决或者保证DDL操作与DML操作之间的一致性。

当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL写锁。

可以看到开启事务，进行查询操作时，对字段进行更新的操作会一直处理阻塞状态：

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 4]  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 5]

##### 3. 页级锁 #####

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

##### 4. 行级锁 #####

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

在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

**注意事项**：

*  在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。
 *  由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以即使是访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。
 *  当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
 *  即便在条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

#### 按属性分类 ####

按属性分类，可分为共享锁和排他锁。

##### 1. 共享锁 #####

`共享锁`，又称之为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。只有当数据上的读锁被释放后，其他事务才能对其添加写锁。共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免”不可重读”的问题的出现。

可以通过以下SQL 添加共享锁：

-- 开启事务
    START TRANSACTION;
    SELECT
    	* 
    FROM
    	account_tbl 
    WHERE
    	id = "11111111" LOCK IN SHARE MODE;

其他事务修改时，则会进入阻塞，但是是可以正常读取数据的，而且其他事务可以继续添加共享锁：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 6]  
但是在共享锁上再添加排它锁时，会进入阻塞，直到共享锁释放，或等待锁超时：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 7]

##### 2. 排他锁 #####

排他锁锁住一行数据后，其他事务不能再在其上加其他的锁。

`排它锁`，又称之为写锁、独占锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读锁，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免`”脏读”`问题的产生。

mysql InnoDB引擎默认的修改数据语句(update,delete,insert)，都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用`select ...for update`语句。

加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过`for update`和`lock in share mode`锁的方式查询数据，但可以直接通过`select ...from...`查询数据，因为普通查询没有任何锁机制。

**案例**：打开一个窗口，开启事务并添加一个排它锁  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 8]  
在其他事务中，继续添加其他锁，或者修改数据时，会进入阻塞，直到排他锁释放，或等待锁超时：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 9]

#### 按加锁方式分类 ####

按加锁方式划分，可分为自动锁、显示锁。

**隐式加锁**：

*  InnoDB自动加意向锁。
 *  对于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

#### 按照算法分类 ####

按照算法分类，可分为间隙锁、临键锁、记录锁。

##### 1. 间隙锁 #####

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

select * from goods where id between 1 and 10 for update;

即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

##### 2. 临键锁 #####

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

##### 3. 记录锁 #####

记录锁是封锁记录，记录锁也叫行锁，例如：

select \*from goods where `id`=1 for update;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

#### 按照模式分类 ####

按照模式分类，可分为悲观锁和乐观锁。

##### 1. 悲观锁 #####

悲观锁是基于一种悲观的态度类来防止一切数据冲突，它是以一种预防的姿态在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，然后才可以对数据进行操作，一般数据库本身锁的机制都是基于悲观锁的机制实现的。

**特点**：可以完全保证数据的独占性和正确性，因为每次请求都会先对数据进行加锁， 然后进行数据操作，最后再解锁，而加锁释放锁的过程会造成消耗，所以性能不高，还有增加产生死锁的风险。

悲观锁的实现，往往依靠数据库提供的锁机制。在数据库中，悲观锁的流程如下：

*  在对记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
 *  如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
 *  如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
 *  其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

拿比较常用的 MySql Innodb 引擎举例，来说明一下在 SQL 中如何使用悲观锁。

要使用悲观锁，必须关闭 MySQL 数据库的自动提交属性。因为 MySQL 默认使用 autocommit 模式，也就是说，当执行一个更新操作后，MySQL 会立刻将结果进行提交。(sql语句：set autocommit=0)

以电商下单扣减库存的过程说明一下悲观锁的使用：  
![在这里插入图片描述][4a92936cd42a40b9a33f8127b5a6d457.png]  
以上，在对id = 1的记录修改前，先通过 for update 的方式进行加锁，然后再进行修改。这就是比较典型的悲观锁策略。

如果以上修改库存的代码发生并发，同一时间只有一个线程可以开启事务并获得id=1的锁，其它的事务必须等本次事务提交之后才能执行。这样可以保证当前的数据不会被其它事务修改。

上面提到，使用 select…for update 会把数据给锁住，不过需要注意一些锁的级别，MySQL InnoDB 默认行级锁。行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

##### 2. 乐观锁 #####

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果冲突，则返回给用户异常信息，让用户决定如何去做。乐观锁适用于读多写少的场景，这样可以提高程序的吞吐量。

一般通过版本号机制实现乐观锁，在数据表中加上版本号字段 version，表示数据被修改的次数。当数据被修改时，这个字段值会加1。

举个简单的例子：假设帐户信息表中有一个 version 字段，当前值为 1 ，而当前帐户的余额（ balance ）为 100 。

*  操作员 A 此时准备将其读出（ version=1 ），并从其帐户余额中扣除 50（ 100-50 ）；
 *  操作员 A 操作的过程中，操作员 B 也读入此用户信息（ version=1 ），并从其帐户余额中扣除 20 （ 100-20 ）；
 *  操作员 A 完成修改工作，将数据版本号加1（ version=2 ），连同帐户扣除后余额（ balance=50 ），提交到数据库完成更新；
 *  操作员 B 完成了操作，也将版本号加1（ version=2 ）试图向数据库提交数据（ balance=80 ），但此时比对数据库记录版本发现，操作员 B 提交的数据版本号为 2 ，数据库记录的当前版本也为 2 ，不满足 “提交版本必须大于记录当前版本才能执行更新“ 的乐观锁策略。

因此，操作员 B 的提交被驳回。这样，就避免了操作员 B 用基于 version=1 的旧数据修改，最终造成覆盖操作员 A 操作结果的可能。

#### 按照状态分类 ####

按状态分为：意向共享锁、意向排它锁。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行。

意向锁的存在价值在于在定位到特定的行所持有的锁之前，提供一种更粗粒度的锁，可以大大节约引擎对于锁的定位和处理的性能，因为在存储引擎内部，锁是由一块独立的数据结构维护的，锁的数量直接决定了内存的消耗和并发性能。例如，事务A对表t的某些行修改（DML通常会产生X锁），需要对t加上意向排它锁，在A事务完成之前，B事务来一个全表操作（alter table等），此时直接在表级别的意向排它锁就能告诉B需要等待（因为t上有意向锁），而不需要再去行级别判断。

##### 1. 意向共享锁 #####

意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁。

`select ... lock in share mode`，要设置IS锁，事务要获得某些行的S锁，必须先获得表的IS锁。

##### 2. 意向排它锁 #####

意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁。

`select ... for update`，要设置IX锁，事务要获得某些行的X锁，必须先获得表的IX锁。

比如我们开启一个事务，对某条记录添加一个排他锁，此时表存在两把锁：表上的意向排它锁与 id 为 "11111111"的数据行上的排它锁。

-- 开启事务
    START TRANSACTION;
    SELECT
    	* 
    FROM
    	account_tbl 
    WHERE
    	id = "11111111" FOR UPDATE;

如果另外一个书屋T2想要添加共享锁时，此时检测到其他事务持有表的意向排他锁，就可以得知该表必然持有该表中某些数据行的排他锁，那么T2对表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排它锁。  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 10]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/90fcbde10ca545328ade459d899bf97d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/784a04f2f1b948ed92356d6035413889.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/6d725eeacfa64b68a33f627ecc48cfc3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/d30f100601994a958426fc13c7fdfd98.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/abf8a6d7f455434bb5c9e49cbcb77368.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/327e0ecc999f4a2ca0cd307732df61c5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://img-blog.csdnimg.cn/1dda2f96f67444e98f391e8862aa0bf8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://img-blog.csdnimg.cn/539bbdb848eb4bf6867268a0234a5bf9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 8]: https://img-blog.csdnimg.cn/2f0bf8b5d16a49e58cccdd91ce1681de.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 9]: https://img-blog.csdnimg.cn/1c5ceef88a6f4a61a85af6d4c9497996.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[4a92936cd42a40b9a33f8127b5a6d457.png]: https://img-blog.csdnimg.cn/4a92936cd42a40b9a33f8127b5a6d457.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 10]: https://img-blog.csdnimg.cn/8ccf9526b2bc4d8d811025ba1d3b16eb.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16