vlambda博客
学习文章列表

「尺有所短，寸有所长；不忘初心，方得始终。」

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：有，方法是由有的，但是有局限性

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

• 在之前文章的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 意向锁的兼容互斥性

• 意向锁之间的兼容互斥性：意向锁之间是互相兼容的

• 意向锁与其他锁兼容互斥性：意向锁与普通的排他锁/共享锁互斥

「上述的排他锁（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：系统启动后到现在总共等待的次数

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