MySQL十三：小一万字+14张图读懂锁机制

转载

MySQL中的锁有很多种，各种锁应用在不同的地方。「MySQL依靠锁机制可以让多个事务更新一行数据的时候串行化」。

MySQL中锁总的来说有两种概念：Lock和Latch

  • Latch

    称为闩锁（轻量级的锁），因为Latch要求锁定的时间非常短。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。在InnoDB引擎中，Latch又分为mutex（互斥量）和rwlock（读写锁）。

  • Lock

    「Lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行」。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

    图片

一、锁的分类

实际上MySQL的锁在不同的维度上划分是多种多样的，在特地的场景下，发挥不一样的作用，下面来看看锁的分类。

  • 「锁定的粒度（加锁的范围）划分」

    • 「全局锁」

      「对整个数据库加锁」。

      应用场景是「做全库的逻辑备份」。

    • 「表锁」

      「每次操作锁住整张表」。锁定粒度大，发生锁冲突的概率最高，并发度最低。

      应用在MyISAM、InnoDB、BDB 等存储引擎中。

    • 「页锁」

      「每次锁定相邻的一组记录」，锁定粒度、开销和加锁时间都界于表锁和行锁之间，并发度一般。

      应用在BDB 存储引擎中

    • 「行锁」

      每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。

  • 「操作类型划分」

    • 「读锁（S锁）」

      共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。

    • 「写锁（X锁）」

      排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

    • 「意向锁」

      InnoDB支持多粒度的锁，允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持 一种特有锁「意向锁」。分为意向读锁（IS锁）、意向写锁（IX锁）。

  • 「锁的机制上划分」

    • 「乐观锁」

      操作数据时不会对操作的数据进行加锁，只是对记录的版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。

    • 「悲观锁」

      在对一条数据修改的时候，为了避免同时被其他事物修改，在修改数据之前先锁定，再修改数据的方式。共享锁和排他锁是悲观锁的不同实现。

图片

二、全局锁

2.1 什么是全局锁

「全局锁，即对整个数据库实例加锁」。一般当我们需要让整个库处于只读状态的时候，可以给数据库加上全局锁。「加上全局锁之后其他线程的：数据更新语句（增删改）、数据定义语句（包括建表、修改表结构等）都会被阻塞」。

  • 「加锁方式」

    MySQL提供了一个加全局读锁的方法，命令

    Flush tables with read lock (FTWRL)

  • 「全局锁应用场景」

    「做全库逻辑备份」。即把整库每个表都select出来保存成文本。

「通过FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份。在备份过程中整个库完全处于【只读状态】」。但是整个库都只能读不能写，会有很大的弊端：

  • 如果在主库上备份。那么在备份期间都不能执行写入操作。
  • 如果在从库上备份。那么在备份期间，从库不能执行主库同步过来的 binlog，从而造成主从延时。

由此可见，做全库逻辑备份的时候加全局锁，对系统的影响非常的大，既然如此，为什么要加全局锁呢？

2.2 为什么要加全局锁

先来看一个案例：

假设现在数据库中现在有两张表：账户余额表，订单表，当我们下一个订单时，会扣减余额，同时在订单表中写入一个订单记录。

下面通过图解来说明对这两张表进行备份的过程，由于备份数据又先后顺序，所以分两种情况来看

  • 「先备份账户余额表，再备份订单表」

    图片

    由图可以看出：先备份账户余额表，再备份订单表导致备份数据中账户余额没扣钱，但是订单有了，商家血亏，消费者乐的不行，这肯定是不允许发生的。

  • 「先备份订单表，再备份账户余额表」

    图片

    由图可以看出：先备份订单表，再备份账户余额表导致备份数据中账户余额扣了钱，但是订单没有了，商家白嫖，消费者肯定不干，这肯定也是不允许发生的。

案例结论：

「通过上述案例说明，不加锁的话，备份系统备份的得到的数据不一致的，其实就是数据一个逻辑时间点的，这个读视图【Read View】是逻辑不一致的」。

2.3 不加全局锁行不行

「通过上述的描述，我们知道在做数据备份的时候，需要加全局锁（FTWRL）来保证数据的一致性，但是由于FTWRL需要关闭所有表对象，数据库禁止写入，执行命令时容易导致数据库hang住」。

Q：既然加全局锁会影响业务，危害大，那做备份的时候有没有不用FTWRL，又能保证数据一致性的方法呢？A：有，方法是由有的，但是有局限性

  • 「不加全局锁怎么保证数据一致性」

    • 在之前文章《MVCC多版本并发控制》的3.3可重复读（RR）MVCC实现原理一节中有提到，「可重复读隔离级别下【同一个事务里面，多次查询，都只会产生一个共用Read View】」，「因此我们将事物的隔离级别调整为可重复读（RR）时是可以得到一致性视图的，而一致性视图通过MVCC能够确保数据的逻辑一致性」。

    • 「MySQL官方提供了一个逻辑备份工具是【mysqldump】。当mysqldump使用参数 -single-transaction 时，备份数据之前会启动一个事务，在这个事务内生成一致性视图。基于MVCC，备份过程中数据是可以正常更新」。

      --single-transaction：设置事务的隔离级别为可重复读(REPEATABLE READ)

  • 「一致性视图局限性」

    「一致性视图实现的前提是事物的隔离级别是可重复读（RR），但是不是所有的引擎都支持事物的，如果使用的MyISAM引擎，一致性视图就无法使用，只能FTWRL命令」。

    MyISAM引擎不支持事务，总是能够拿到最新的数据

2.4数据库只读能否替换FTWRL

「FTWRL的本质就是给数据库加一个锁，禁止其他线程写入，也就是将数据库设置成了只读状态」。数据库设置为只读状态其实还有一种更简单的方式，直接使用以下命令即可：

set global readonly=true

但是一般不推荐使用readonly，两者比较，还是推荐使用FTWRL

  • 「readonly的值有时候会被用来做其他逻辑判断，修改global变量影响更大」

    判断一个库是主库还是备库

  • 「当备份过程中出现异常时：」

    • FTWRL命令会释放全局锁，数据库可以被其他线程正常读写
    • 设置为readonly时，由于时全局变量，数据库就会一直保持readonly状态，直到改变readonly的值

三、表锁

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

3.1表锁

「表级别的锁定是MySQL各【存储引擎中】最大颗粒度的锁定机制」。由于直接锁定一张表，所以获取锁和释放锁的速度很快，避免了死锁问题，但是出现锁定资源争用的概率也最高，并发量降低。

  • 「表锁的加锁语法」

    #隐式上锁（默认，自动加锁自动释放
insert、update、delete //上写锁
#显式上锁（手动）
lock table tableName read;//读锁
lock table tableName write;//写锁

  • 「表锁的释放锁语法」

    UNLOCK TABLES

    客户端断开的时候也会自动释放锁。

  • 「查看表上加过的锁」

    show open tables;

「MyISAM引擎默认的锁是表锁」。表锁一般是在数据库引擎不支持行锁的时候才会被用到的。

  • 「表级读锁」

    当前表加read锁，当前连接和其他的连接都可以读操作；但是当前连接写操作会报错，其他连接写操作会被阻塞。

  • 「表级写锁」

    当前表加write锁，当前连接可以对表做读写操作，其他连接对该表所有操作（读写操作）都被阻塞。

「表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞」。

3.2元数据锁（metadata lock，MDL)

当我们查询查询一个表中的数据时，另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构就不一致了，这肯定是允许。这里就用到了元数据锁

在MySQL 5.5版本中引入了MDL，「元数据锁(MDL) 不需要显式使用，在访问一个表的时候会被自动加上」。

  • 「当对一个表做增删改查的时候会加上【MDL读锁】」

    读锁之间不互斥，因此可以有多个线程同时对一张表增删改查操作。

  • 「当对一个表做结构变更的时候会加上【MDL写锁】」

    读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

四、页锁

  • 页级锁定是「MySQL中比较独特」的一种锁定级别，在其他数据库管理软件中并不常见。
  • 页级锁定和行级锁定一样，会发生死锁
  • 使用页级锁定的主要是BerkeleyDB存储引擎。
  • 开销和加锁时间界于表锁和行锁之间；
  • 锁定粒度界于表锁和行锁之间，并发度一般。

页锁应用于 BDB 引擎，一般很少见，了解一下即可，重点理解下行锁

五、行锁

5.1行锁是什么

「行锁顾名思义就是对数据行进行加锁。行锁的锁定颗粒度在 MySQL中是最细的，应用于 InnoDB 存储引擎，通过对索引数据页上的记录加锁实现的【即行锁是针对索引加锁】」。

  • 「行锁的优缺点」

    并发情况下，产生锁等待的概率较低，支持较大的并发数，但开销大，加锁慢，而且会出现死锁。

  • 「行锁的前提条件」

    「检索数据时需要通过索引，【因为 InnoDB 是通过给索引的索引项加锁来实现行锁的】」。

    • 在不通过索引条件查询的时候，InnoDB 会使用表锁，表锁会产生锁冲突
    • 「行锁是针对索引加锁」，所以即使访问的不同记录，只要使用的是同一索引项，也可能会出现锁冲突。

MySQL会比较不同执行计划，当全表扫描比索引效率更高时，InnoDB就使用表锁。因此不一定使用了索引就一定会使用行锁，也有可能使用表锁。

  • 「行锁会产生死锁」

    在我之前文章《索引分析》中的回表查询有提到，当我们走辅助索引的时候，会扫两遍索引树，如下：

    图片

    「实际上InnoDB 的行锁也是分为两步获得的：锁住主键索引，锁住非主键索引」。

    当两个事务同时执行时，

    一个锁住了主键索引，在等待其他索引；

    另一个锁住了非主键索引，在等待主键索引，

    这样就可能会发生死锁。

    「InnoDB可以检测到这种死锁，检测到后会让其中一个事务释放锁回退，另一个获取锁完成事务」。

5.2行锁的实现算法

前面讲到「InnoDB行锁是通过对 索引数据页上的记录加锁实现的」，接下来看看它具体是怎么实现，

InnoDB存储引擎有3种实现行锁的算法：

  • 「【Record Lock】：记录锁，单个行记录上的锁」

    RC、RR隔离级别都支持，如果表中没有主键和任何一个索引，那InnoDB会使用隐式的主键来进行锁定。

  • 「【Gap Lock】：间隙锁，锁定一个范围，但不包含记录本身」

    范围锁，锁定索引记录范围，确保索引记录的间隙不变，RR隔离级别支持

  • 「【Next-Key Lock】：Gap Lock与Record Lock的组合」

    锁定数据前后范围，并且锁定记录本身，RR隔离级别支持

「在RR隔离级别，InnoDB对于行的查询都是采用【Next-Key Lock】的组合锁定算法」，但是「在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock会降级为Record Lock，仅锁住索引本身而非范围」。

下面具体看下针对不同的sql语句采用的是那种加锁方式：

  • 查询语句类型一

    select ... from  ...

    「对于普通的select语句，InnoDB引擎采用MVCC机制实现非阻塞读，【InnoDB引擎不加锁】」。

  • 查询语句类型二

    select ... from ... lock in share mode

    「添加共享锁，InnoDB会使用Next-Key Lock锁进行处理，扫描如果有唯一索引，则降级为RecordLock锁」。

  • 查询语句类型三

    select ... from ... for update

    「添加排他锁，InnoDB会使用Next-Key Lock锁进行处理，扫描如果有唯一索引，则降级为RecordLock锁」。

  • 修改语句

    update ... from ... where  ...

    「InnoDB会使用Next-Key Lock锁进行处理，扫描如果有唯一索引，则降级为RecordLock锁」。

  • 删除语句

    delete ... from ... where

    「InnoDB会使用Next-Key Lock锁进行处理，扫描如果有唯一索引，则降级为RecordLock锁」。

  • 插入语句

    insert ... from  ...

    「InnoDB会在将要插入的那一行设置一个排他的RecordLock锁」。

六、读锁/写锁/意向锁

在前文中提到的锁类型按照「操作类型划分」有「读锁（S锁），写锁（X锁）」，其实它们与共享锁，排他锁是一个意思，只是不同叫法而已。

6.1共享锁（行级锁-读锁/S锁）

「共享锁（Shared Lock）又称为读锁，简称S锁，是一种行级锁」。

顾名思义：「共享锁就是多个事务对于同一数据共享一把锁，都能访问到数据，但是只能读不能修改」。

  • 「加锁方式」

    select ... from ... lock in share mode

  • 「释放方式」：

    commit;
rollback;

  • 「共享锁工作原理」

    「一个事务获取了一条记录的共享锁后，其他事务也能获得该记录对应的共享锁，但不能获得排他锁」。即一个事务使用了共享锁（读锁），其他事务只能读取，不能写入，写操作被阻塞。

6.2排他锁（行级锁-写锁/X锁）

「排他锁（EXclusive Lock）又称为写锁，简称X锁，是一种行锁也可以是表锁」。

顾名思义：「排他锁就是不能与其他锁并存，即当前写操作没有完成前，会阻断其他写锁和读锁」。

  • 「加锁方式」

    innodb引擎默认会在update，delete语句加上 for update

    SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ...  # 排他锁
DELETE FROM student WHERE ...  # 排他锁
UPDATE student SET ...  # 排他锁

  • 「释放方式」：

    commit;
rollback;

  • 「共享锁工作原理」

    「如一个事务获取了一条记录的排他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁（共享锁、排他锁），但是获取到排他锁的事务可以对数据进行读写操作」。

    这里要注意一下，其他事务不加锁的读是不会被阻塞的，阻塞的是加锁的读

  • 「排他锁为什么是一种行锁也是表锁」

    innodb引擎默认会在update，delete语句加上 for update

    「读锁，写锁都属于行级锁，行级锁的实现是依靠其对应的索引，如果没用到索引的查询，就会走表锁」。

    • 有索引：以索引列为条件更新数据，会存在间隙锁，行锁，页锁，而锁住一部分行。

    • 没有索引：更新数据时会锁住整张表。

    • 「可重复读隔离级别下」

    • 「串行化隔离级别下」

      读写数据都会锁住整张表

6.3意向锁（表锁）

6.1 意向锁是什么

「意向锁（Intention Lock）简称I锁，是一种表级锁」。

「InnoDB 实现了标准的行级锁，包括：共享锁（S锁）、排它锁（X锁）」，那么为什么需要引入意向锁呢？意向锁解决了什么问题？

假设，事务A获取了某一行记录的排它锁，事物A尚未提交,事务B想要获取表锁时，则事物B必须要确认表的每一行都不存在排他锁，需要进行全表扫描，效率很低，此时就引入意向锁

  • 如果事务A获取了某一行记录的排它锁，实际此时表存在两种锁，行记录的排他锁和表上的意向排他锁。
  • 如果事务B试图在该表加表级锁时，则会被意向锁阻塞，因此事物B不必检查各个页锁或行锁，而只需检查表上的意向即可。

图片

如上，数据库中存储数据，范围由大到小：表-->页-->行，加锁也是分别加在表-->页-->行中，当我们把锁加在更大一级范围时，也就不需要全表扫描下一级的某些锁，可以很大程度提升性能。

「锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，即意向锁」

通过上述描述我们知道「意向锁是加在表上，用于防止全表扫描的一种锁，即意向锁是表锁」。意向锁分为两种类型：

  • 「意向共享锁（intention shared lock）」简称IS锁，事务想要给某一个数据行加行级共享锁（S锁）之前必须先获取该表的IS锁（表级锁）
  • 「意向排他锁（intention exclusive lock）」简称IX锁，事务想要给某一个数据行加行级排他锁（X锁）之前必须先获取该表的IX锁（表级锁）

「【意向锁都是InnoDB存储引擎自己维护的,用户是无法操作意向锁的】」。

【「在为数据行加共享锁/排他锁之前，InooDB会先获取该数据行所在在数据表的对应意向锁(表级锁)」】，如果没有获取到，否则等待innodb_lock_wait_timeout超时后根据innodb_rollback_on_timeout决定是否回滚事务。

从锁粒度角度：InnoDB 允许行级锁与表级锁共存,而意向锁是表锁；

从锁模式角度：意向锁是一种独立类型，辅助解决记录锁效率不及的问题；

从兼容性角度：意向锁包含了共享/排他两种。

6.2 意向锁的兼容互斥性

  • 意向锁之间的兼容互斥性：意向锁之间是互相兼容的
意向共享锁（IS） 意向排他锁（IX）
意向共享锁（IS） 兼容 兼容
意向排他锁（IX） 兼容 兼容
  • 意向锁与其他锁兼容互斥性：意向锁与普通的排他锁/共享锁互斥
意向共享锁（IS） 意向排他锁（IX）
表级共享锁（S） 兼容 互斥
表级排他锁（X） 互斥 互斥

「上述的排他锁（X锁）共享锁（S锁）指的都是表锁，意向锁不会与行级的共享锁/排他锁互斥」

七、乐观锁/悲观锁

「乐观锁/悲观锁其实都是概念上的，只是在并发下防止数据被修改的一种加锁形式」。

7.1 悲观锁（Pessimistic Locking）

「对数据的修改抱有悲观态度的一种并发控制方式，悲观的认为自己(当前线程)拿到的数据是被修改过的，所以在操作数据之前先加锁」。

  • 「悲观锁的形式（类型）」

    「数据库的行锁、表锁、读锁、写锁、共享锁、排他锁等，以及syncronized 实现的锁都是悲观锁的范畴」。

图片

  • 「优点」

    「可以保证数据的独占性和正确性」。

  • 「缺点」

    「每次请求都需要加锁、释放锁，这个过程会降低系统性能」。

7.2 乐观锁

「乐观锁是对于数据冲突保持一种乐观态度，每次读取数据的时都认为其他线程不会修改数据，所以不上锁，只是在数据修改后提交时才通过【版本号机制或者CAS算法】来验证数据是否被其他线程更新」。

因为乐观锁中并没有【加锁和解锁】操作，因此乐观锁策略也被称为「无锁编程」。

  • 「乐观锁实现的关键点」：检测冲突

  • 「乐观锁实现方式」

    • 版本号机制（常用）
    • CAS算法实现

  • 「优点」

    「没有加锁和解锁操作，可以提高吞吐量」

  • 「缺点」

    乐观锁需要自己实现，且外部系统不受控制

  • 「乐观锁的应用」

    在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS。

  • 「适用场景」：读多写少

  • 「注意」

    乐观锁不是数据库提供的功能，需要开发者自己去实现。

    除了开发者自己手动实现乐观锁之外，很多数据库访问框架也封装了乐观锁的实现

    比如 hibernate框架，MyBatis框架的OptimisticLocker插件。

7.2.1版本号机制实现乐观锁

版本号机制有两种方式：使用版本字段（version）和使用时间戳（Timestamp），两者实现原理是一样的。

前文中提到「乐观锁需要开发者自己去实现，所以版本号实现时通过在表中加字段的形式实现的」。

  • 「使用版本字段（version）」

    「在数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1」。version 是用来查看被读的记录有无变化，防止记录在业务处理期间被其他事务修改。

  • 「使用时间戳（Timestamp）」

    与使用version版本字段基本一致，「同样需要给在数据表增加一个字段，字段类型使用timestamp时间戳，通过时间戳比较数据版本」。

图片

  • 「乐观锁实现案例」

    修改用户表中Id为1的用户姓名

    • 第一步：查询记录信息

      #使用版本字段（version)
select name,version from user where id=1;
#使用时间戳（Timestamp）
select name,timestamp from user where id=1;

    • 第二步：逻辑处理之后，修改姓名为张三

      #使用版本字段（version)
update user set name = '张三',version=version+1 where id=1 and version = #{version};#version 为第一步查询的值

#使用时间戳（timestamp）
update user set name = '张三',timestamp=now() where id=1 and timestamp = #{timestamp};

7.2.2CAS算法实现乐观锁

「CAS算法即compare and swap（比较与交换），是一种有名的无锁算法。即不使用锁的情况下实现多线程之间的变量同步，也就是无锁编程」。

  • 「特点」

    不加锁，即使没有线程被阻塞的情况下实现变量的同步，也叫非阻塞同步

  • CAS算法涉及到三个操作数

    「当且仅当V的值等于A时，CAS通过原子方式用新值B来更新V的值，否则不会执行任何操作」（比较和替换是一个原子操作），一般情况下是一个自旋操作，即不断的重试。

    图片

    • 变量当前内存值 V
    • 旧的预期值 A
    • 要写入的新值 B

  • 「CAS缺点」

    • 「ABA问题」

      当线程1读到某变量的值为A，在其逻辑处理的过程中，另外一个线程2将该变量的值从A先修改为B、然后又将其从B修改回A。此时，当线程1通过CAS操作进行新值写入虽然可以成功，而实际上线程1执行CAS操作时预期值的A 和读取该变量当前值的A已经不是同一个了，后者是线程2修改的

    • 「CPU开销大」

      虽然CAS算法是非阻塞的，但如果CAS操作一直不成功不断循环，会浪费CPU资源

    • 「只能保证一个共享变量的原子性」

      当对多个变量进行操作时，CAS算法无法保证原子性。

      可以将多个变量封装为一个对象再使用CAS算法（Java中的AtomicReference）

八、死锁和死锁检测

8.1 死锁是什么

「死锁是指两个或两个以上的事务在执行过程等中，因争夺资源而造成的一种相互等待的现象」。

  • 「死锁产生本质原因」

    • 系统资源有限
    • 进程推进顺序不合理

  • 「死锁产生的4个必要条件」

    • 「互斥条件（Mutual exclusion，简称Mutex）」资源要么被一个线程占用,要么是可用状态
    • 「不可抢夺（No preemption）」资源被占用后,除非占有线程主动释放,其他线程不能把它从该线程占用中抢夺
    • 「占有和等待（Hold and wait）」一个进程必须占有至少一个资源，并等待另一资源，而该资源被其他进程占用
    • 「循环等待（Circular wait）」一组等待进程{P0, P1…Pn-1, Pn}，P0等待资源被P1占有，P1等待资源被P2占有，Pn-1等待资源被Pn占有，Pn等待资源被P0占有，循环等待，则形成环形结构。

「死锁发生的以上四个条件缺一都无法导致死锁，而由于互斥条件是非共享资源所必须的，不仅不能改变，还应加以保证，所以恢复死锁主要是破坏产生死锁的其他三个条件」。

8.2 常见死锁现象和解决方案

8.2.1表级锁死锁

  • 「案例」

    有线程A、B分别需要访问用户表与订单表，访问表的时候都会加表级锁。线程A访用户表，并对用户表加锁（线程A锁住了用户表），然后又访问订单表；此时线程B先访问订单表，并对订单表加锁（线程B锁住了订单表），然后线程想访问用户表。

  • 「产生原因」

    上述案例由于线程B已经锁住订单表，线程A必须等待线程B释放订单表能继续，同样线程B要等线程A释放用户表才能继续，「线程A、B相互等待对方释放锁，就产生了死锁」。

    图片

  • 「解决方案」

    「这种死锁是由于程序的BUG产生的，比较常见，只能通过调整程序的逻辑来解决」。

    对于数据库的多表操作时，尽量按照相同的顺序进行处理，避免同时锁定两个资源，

    如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

8.2.2行级锁死锁

行级锁产生死锁有两种情况，一直是资源争夺，一种是行级锁升级为表级锁

  • 资源争夺

    • 「产生原因」

      当事务中某个查询没有走索引时，就会走全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），并发下多个线程同时执行，就可能会产生死锁和阻塞

    • 「解决方案」

      SQL语句中尽量不要有太复杂的多表关联查询，并通过执行对SQL语句进行分析，建立索引优化，避免全表扫描和全表锁定。

  • 行级锁升级为表级锁

    • 「产生原因」

      两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

      图片

    • 「解决方案」

      • 在同一个事务中，尽量一次锁定需要的所有资源
      • 将每个资源编号，通过资源编号的线性顺序来预防死锁，当一个进程占有编号为i的资源时，那么它下一次只能申请编号大于i的资源。

  • 图片

8.2.3共享锁转换为排他锁

  • 「案例」

    事务A有两个操作，首先查询一条纪录M，然后更新纪录M；此时事务B在事物A查询之后更新之前去更新纪录M，此时事物A获取了记录M的共享锁，事物B获取了记录M的排他锁， 事务B的排他锁由于事务A有共享锁，必须等A释放共享锁后才可以获取，事物B只能排队等待。

  • 「产生原因」

    案例中事物B已经进入等待，事物A更新M需要排他锁，而此时事务B已经有一个排他锁请求，并且正在等待事务A释放其共享锁，因此无法给事物A授予排他锁锁请求，事物A也进入排队等待

    注意：这里事物B还没有拿到M的排它锁，只是进入排队等到状态

  • 「解决方案」

    通过「手动实现乐观锁」进行控制，乐观锁的无锁机制可以避免长事务中的数据库加锁开销，增大并发量，提升系统性能。

8.3死锁排查

MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

  • 「查看近期死锁日志信息」

    show engine innodb status;

    图片

    通过以上命令查看近期死锁日志信息，然后使用执行计划进行SQL优化

  • 「查看锁状态变量」

    通过以下命令可以检查锁状态变量，从而分析系统中的行锁的争夺情况

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

「如果等待次数高，而且每次等待时间长，则需要对其进行分析优化」。

posted @ 2022-08-27 11:42  云扬四海  阅读(288)  评论(0)  编辑  收藏  举报