发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

面试：什么是死锁，如何避免或解决死锁；MySQL中的死锁现象，MySQL死锁如何

送只羊抵油钱

9

主题

9

帖子

27

积分

新手上路

Rank: 1

积分
27
显示全部楼层
文章目录
前言
一、死锁

  • 1.1 什么是死锁
  • 1.2 死锁产生的四个必要条件
  • 1.3 模拟产生死锁的代码
  • 1.4 死锁的产生原因
二、如何避免或解决死锁

  • 2.1 死锁预防
  • 2.2 死锁避免
  • 2.3 死锁检测
  • 2.4 死锁解除
三、数据库锁

  • 3.1 锁分类
  • 3.2 InnoDB中不同SQL语句设置的锁
  • 3.3 控制事务
四、MySQL中的死锁

  • 4.1 MySQL中的死锁现象
  • 4.2 MySQL中死锁如何解决

    • 4.2.1 MySQL的锁超时机制
    • 4.2.2 死锁检测算法 - wait-for graph
    • 4.2.3 如何预防/避免死锁产生

五、如何确保 N 个线程可以访问 N 个资源，同时又不导致死锁
前言
  1. -- 锁等待超时配置
  2. show variables like 'innodb_lock_wait_timeout';
  3. show variables like '%lock_wait_timeout%';
  4. +--------------------------+----------+
  5. | Variable_name            | Value    |
  6. +--------------------------+----------+
  7. | innodb_lock_wait_timeout | 50       |
  8. | lock_wait_timeout        | 31536000 |
  9. +--------------------------+----------+
  12. -- MySQL死锁定位
  13. -- 查看有哪些线程正在执行
  14. show processlist;
  15. -- 查看当前运行的所有事务
  16. select * from information_schema.INNODB_TRX;
  17. -- information_schema和锁相关的还有INNODB_LOCK_WAITS（查看锁争执双方）、INNODB_LOCKS（锁相关的信息）
复制代码
一、死锁

1.1 什么是死锁

死锁（Deadlock）是指两个或两个以上的线程（或进程）在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁。
很显然，如果没有外力的作用，那么死锁涉及到的各个进程都将永远处于封锁状态。可以想象成几个小船在某个狭窄水域中，彼此堵住，谁也无法前进。
1.2 死锁产生的四个必要条件

要理解死锁的产生，必须知道它的四个必要条件：

  • 互斥条件：资源被进程排他地占用。进程要求对所分配的资源（如打印机）进行排他性控制，即在一段时间内某资源仅为一个进程所占有。此时若有其他进程请求该资源，则请求进程只能等待。
  • 保持并等待条件：进程已经保持了至少一个资源，但又提出了新的资源请求，而该资源已被其他进程占有，此时请求进程被阻塞，但对自己已获得的资源保持不放。
  • 不可剥夺条件：进程所获得的资源在未使用完毕之前，不能被其他进程强行夺走，即只能由获得该资源的进程自己来释放（只能是主动释放)。
  • 循环等待条件：存在一种进程资源的循环等待链，链中每一个进程已获得的资源同时被链中下一个进程所请求。
注意：这四个条件是产生死锁的必要条件，也就是说只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。
1.3 模拟产生死锁的代码

下面通过一个实际的例子来模拟线程产生死锁
  1. public class DeadLockDemo {
  2.     private static Object resource1 = new Object();//资源 1
  3.     private static Object resource2 = new Object();//资源 2
  5.     public static void main(String[] args) {
  6.         new Thread(() -> {
  7.             synchronized (resource1) {
  8.                 System.out.println(Thread.currentThread() + "get resource1");
  9.                 try {
  10.                     Thread.sleep(1000);
  11.                 } catch (InterruptedException e) {
  12.                     e.printStackTrace();
  13.                 }
  14.                 System.out.println(Thread.currentThread() + "waiting get resource2");
  15.                 synchronized (resource2) {
  16.                     System.out.println(Thread.currentThread() + "get resource2");
  17.                 }
  18.             }
  19.         }, "线程 1").start();
  21.         new Thread(() -> {
  22.             synchronized (resource2) {
  23.                 System.out.println(Thread.currentThread() + "get resource2");
  24.                 try {
  25.                     Thread.sleep(1000);
  26.                 } catch (InterruptedException e) {
  27.                     e.printStackTrace();
  28.                 }
  29.                 System.out.println(Thread.currentThread() + "waiting get resource1");
  30.                 synchronized (resource1) {
  31.                     System.out.println(Thread.currentThread() + "get resource1");
  32.                 }
  33.             }
  34.         }, "线程 2").start();
  35.     }
  36. }
复制代码
输出
  1. Thread[线程 1,5,main]get resource1
  2. Thread[线程 2,5,main]get resource2
  3. Thread[线程 2,5,main]waiting get resource1
  4. Thread[线程 1,5,main]waiting get resource2
复制代码
线程A通过synchronized (resourcel)获得resource1的监视器锁，然后通过·Thread.sleep（1000）；让线程A休眠1s 为的是让线程B得到执行然后获取到resource2的监视器锁。线程A和线程B休眠结束了都开始企图请求获取对方的资源，然后这两个线程就会陷入互相等待的状态，这也就产生了死锁。
1.4 死锁的产生原因

（1）竞争资源引起进程死锁
当系统中供多个进程共享的资源如打印机、公用队列等等，其数目不足以满足诸进程的需要时，会引起诸进程对资源的竞争而产生死锁。
（2）可剥夺资源和不可剥夺资源

  • 可剥夺资源，是指某进程在获得这类资源后，该资源可以再被其他进程或系统剥夺。例如，优先权高的进程可以剥夺优先权低的进程的处理机。又如，内存区可由存储器管理程序，把一个进程从一个存储区移到另一个存储区，此即剥夺了该进程原来占有的存储区，甚至可将一进程从内存调到外存上，可见，CPU和主存均属于可剥夺性资源。
  • 不可剥夺资源，当系统把这类资源分配给某进程后，再不能强行收回，只能在进程用完后自行释放，如磁带机、打印机等。
（3）竞争不可剥夺资源
在系统中所配置的不可剥夺资源，由于它们的数量不能满足诸进程运行的需要，会使进程在运行过程中，因争夺这些资源而陷于僵局。
例如，系统中只有一台打印机R1和一台磁带机R2，可供进程P1和P2共享。假定PI已占用了打印机R1，P2已占用了磁带机R2，若P2继续要求打印机R1，P2将阻塞；P1若又要求磁带机，P1也将阻塞。
于是，在P1和P2之间就形成了僵局，两个进程都在等待对方释放自己所需要的资源，但是它们又都因不能继续获得自己所需要的资源而不能继续推进，从而也不能释放自己所占有的资源，以致进入死锁状态。
（4）竞争临时资源
上面所说的打印机资源属于可顺序重复使用型资源，称为永久资源。还有一种所谓的临时资源，这是指由一个进程产生，被另一个进程使用，短时间后便无用的资源，故也称为消耗性资源，如硬件中断、信号、消息、缓冲区内的消息等，它也可能引起死锁。
例如，SI，S2，S3是临时性资源，进程P1产生消息S1，又要求从P3接收消息S3；进程P3产生消息S3，又要求从进程P2处接收消息S2；进程P2产生消息S2，又要求从P1处接收产生的消息S1。
如果消息通信按如下顺序进行：
  1. P1: ···Relese（S1）；Request（S3）； ···
  3. P2: ···Relese（S2）；Request（S1）； ···
  5. P3: ···Relese（S3）；Request（S2）； ···
复制代码
并不可能发生死锁。但若改成下述的运行顺序：
  1. P1: ···Request（S3）；Relese（S1）；···
  3. P2: ···Request（S1）；Relese（S2）； ···
  5. P3: ···Request（S2）；Relese（S3）； ···
复制代码
则可能发生死锁。
二、如何避免或解决死锁

解决死锁的方法一般情况下有预防、避免、检测、解除：

  • 预防：采用某种策略，限制并发进程对资源的请求，从而使得死锁的必要条件在系统执行的任何时间上都不满足
  • 避免：在系统分配资源时，根据资源使用情况提前做出预测，从而避免死锁的发生
  • 检测：系统设有专门的机构，当死锁发生时，该机构能检测死锁发生并精确确定与死锁有关的进程和资源
  • 解除：与检测相配套的一种措施，将进程从死锁状态下解脱出来
2.1 死锁预防

对于死锁产生的四个必要条件，只要破坏其中一个条件，就可以预防死锁的发生：
破坏第一个条件 互底条件：使得资源是可以同时访问的，这是种简单的方法，磁盘就可以用这种方法管理，但是我们要知道，有很多资源往往是不能同时访问的，所以这种做法在大多数的场合是行不通的。
破坏第三个条件 不可剥夺/非抢占：也就是说可以采用剥夺式调度算法，但剥夺式调度方法目前一般仅适用于主存资源和处理器资源的分配，并不适用于所有的资源，会导致资源利用率下降。
所以一般比较实用的预防死锁的方法，是通过考虑破坏第二个条件和第四个条件。
（1）破坏持有等待：静态分配策略，一次性申请所有资源
静态分配策略可以破坏死锁产生的第二个条件（占有并等待）。所谓静态分配策略，就是指一个进程必须在执行前就申请到它所需要的全部资源，并且知道它所要的资源都得到满足之后才开始执行。进程要么占有所有的资源然后开始执行，要么不占有资源，不会出现占有一些资源等待一些资源的情况。
静态分配策略逻辑简单，实现也很容易，但这种策略严重地降低了资源利用率，因为在每个进程所占有的资源中，有些资源是在比较靠后的执行时间里采用的，甚至有些资源是在额外的情况下才使用的，这样就可能造成一个进程占有了一些几乎不用的资源而使其他需要该资源的进程产生等待的情况。
（2）破坏环路等待：层次分配策略，所有资源被分成多个层次。一个进程得到某资源后只能申请较高一层的资源；一个资源释放资源只能先释放较高层的资源
层次分配策略破坏了产生死锁的第四个条件（循环等待)。在层次分配策略下，所有的资源被分成了多个层次，一个进程得到某一次的一个资源后，它只能再申请较高一层的资源；当一个进程要释放某层的一个资源时，必须先释放所占用的较高层的资源，按这种策略，是不可能出现循环等待链的，因为那样的话，就出现了已经申请了较高层的资源，反而去申请了较低层的资源，不符合层次分配策略，证明略。
2.2 死锁避免

上面提到的破坏死锁产生的四个必要条件之一就可以成功 预防系统发生死锁，但是会导致低效的进程运行和资源使用率。而死锁的避免相反，它的角度是允许系统中同时存在四个必要条件，只要掌握并发进程中与每个进程有关的资源动态申请情况，做出明智和合理的选择，仍然可以避免死锁，因为四大条件仅仅是产生死锁的必要条件。
我们将系统的状态分为安全状态和不安全状态，每当在为申请者分配资源前先测试系统状态，若把系统资源分配给申请者会产生死锁，则拒绝分配，否则接受申请，并为它分配资源。

  • 如果操作系统能够保证所有的进程在有限的时间内得到需要的全部资源，则称系统处于安全状态，否则说系统是不安全的。很显然，系统处于安全状态则不会发生死锁，系统若处于不安全状态则可能发生死锁。
那么如何保证系统保持在安全状态呢？通过算法，其中最具有代表性的避免死锁算法就是Dijkstra的银行家算法，银行家算法用一句话表达就是：当一个进程申请使用资源的时候，银行家算法通过先试探分配给该进程资源，然后通过安全性算法判断分配后系统是否处于安全状态，若不安全则试探分配作废，让该进程继续等待，若能够进入到安全的状态，则就真的分配资源给该进程。
银行家算法详情可见：一句话+一张图说清楚一一银行家算法
死锁的避免（银行家算法）改善了资源使用率低的问题，但是它要不断地检测每个进程对各类资源的占用和申请情况，以及做安全性检查，需要花费较多的时间。
2.3 死锁检测

对资源的分配加以限制可以预防和避免死锁的发生，但是都不利于各进程对系统资源的充分共享。解决死锁问题的另一条途径是死锁检测和解除（这里突然联想到了乐观锁和悲观锁，感觉死锁的检测和解除就像是乐观锁，分配资源时不去提前管会不会发生死锁了，等到真的死锁出现了再来解决嘛，而死锁的预防和避免更像是悲观锁，总是觉得死锁会出现，所以在分配资源的时候就很谨慎)。
这种方法对资源的分配不加以任何限制，也不采取死锁避免措施，但系统定时地运行一个“死锁检测”的程序，判断系统内是否出现死锁，如果检测到系统发生了死锁，再采取措施去解除它。

进程-资源分配图中存在环路并不一定是发生了死锁。因为循环等待资源仅仅是死锁发生的必要条件，而不是充分条件。图2-22便是一个有环路而无死锁的例子。虽然进程P1和进程P3分别占用了一个资源R1和一个资源R2，并且因为等待另一个资源R2和另一个资源R1形成了环路，但进程P2和进程P4分别占有了一个资源R1和一个资源R2，它们申请的资源得到了满足，在有限的时间里会归还资源，于是进程P1或P3都能获得另一个所需的资源，环路自动解除，系统也就不存在死锁状态了。
检测系统是否产生死锁：

  • 若进程-资源分配图中无环路，则此时系统没有发生死锁
  • 若进程-资源分配图中有环路，且每个资源类仅有一个资源，则系统发生了死锁
  • 若进程-资源分配图中有换图，且资源类有多个资源，此时系统未必会发生死锁。若进程-资源分配图中能找出一个 既不阻塞又非独立的进程，该进程在有限时间内归还占用的资源，也就是把边消除了，重复此过程，直至在有限时间内消除所有边，则不会发生死锁（消除边的过程类似于 拓扑排序）。
2.4 死锁解除

当死锁检测程序检测到存在死锁发生时，应设法让其解除，让系统从死锁状态中恢复过来，常用的解除死锁的方法有以下四种：

  • 立即结束所有进程的执行，重新启动操作系统：方法简单，但以前所有工作全部作废，损失很大
  • 撤销涉及死锁的所有进程，解除死锁后继续运行：打破了死锁的循环等待条件，但将付出很大代价，例如有些进程可能已经计算了很长时间，由于被撤销而使产生的部分结果也被消除了，再重新执行时还要再次进行计算。
  • 逐个撤销涉及死锁的进程，回收资源直至死锁解除
  • 抢占资源，从涉及死锁的一个或多个进程中抢占资源，把夺得的资源再分配给涉及死锁的进程直至死锁解除
三、数据库锁

3.1 锁分类

MySQL的锁机制与索引机制类似，都是由存储引擎负责实现的，这也就意味着不同的存储引擎，支持的锁也并不同，这里是指不同的引擎实现的锁粒度不同。但除开从锁粒度来划分锁之外，其实锁也可以从其他的维度来划分，因此也会造出很多关于锁的名词，下面先简单梳理一下MySQL的锁体系：

  • 以锁粒度的维度划分

    • 全局锁：锁定数据库中的所有表。加上全局锁之后，整个数据库只能允许读，不允许做任何写操作
    • 表级锁：每次操作锁住整张表。主要分为三类

      • 表锁（分为表共享读锁 read lock、表独占写锁 write lock）
      • 元数据锁（meta data lock，MDL）：基于表的元数据加锁，加锁后整张表不允许其他事务操作。这里的元数据可以简单理解为一张表的表结构
      • 意向锁（分为意向共享锁、意向排他锁）：这个是InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

    • 行级锁：每次操作锁住对应的行数据。主要分为三类

      • 记录锁 / Record 锁：也就是行锁，一条记录和一行数据是同一个意思。防止其他事务对此行进行update和delete，在 RC、RR隔离级别下都支持
      • 间隙锁 / Gap 锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
      • 临键锁 / Next-Key 锁：间隙锁的升级版，同时具备记录锁+间隙锁的功能，左开右闭区间，在RR隔离级别下支持


  • 以互斥性的角度划分

    • 共享锁 / S锁：不同事务之间不会相互排斥、可以同时获取的锁
    • 排他锁 / X锁：exclusive，不同事务之间会相互排斥、同时只能允许一个事务获取的锁
    • 共享排他锁 / SX锁：MySQL5.7版本中新引入的锁，主要是解决SMO带来的问题

  • 以操作类型的维度划分

    • 读锁：查询数据时使用的锁
    • 写锁：执行插入、删除、修改、DDL语句时使用的锁

  • 以加锁方式的维度划分

    • 显示锁：编写SQL语句时，手动指定加锁的粒度
    • 隐式锁：执行SQL语句时，根据隔离级别自动为SQL操作加锁

  • 以思想的维度划分

    • 乐观锁：每次执行前认为自己会成功，因此先尝试执行，失败时再获取锁
    • 悲观锁：每次执行前都认为自己无法成功，因此会先获取锁，然后再执行

放眼望下来，是不是看着还蛮多的，但总归说来说去其实就共享锁、排他锁两种，只是加的方式不同、加的地方不同，因此就演化出了这么多锁的称呼。
各类锁的具体详解，可查阅 MySQL锁、加锁机制（超详细）—— 锁分类、全局锁、共享锁、排他锁；表锁、元数据锁、意向锁；行锁、间隙锁、临键锁；乐观锁、悲观锁
3.2 InnoDB中不同SQL语句设置的锁

InnoDB设置特定类型的锁如下：

  • SELECT ... FROM是一致性读，读取数据库的快照并且不设置锁，除非将事务隔离级别设置为 SERIALIZABLE。对于 SERIALIZABLE级别，搜索在遇到的索引记录上设置共享的临键锁。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
  • 对于SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE，会为扫描的行获取锁，并且预计会为不符合包含在结果集中的行释放锁（例如，如果它们不满足子句中给出的条件WHERE）。但是，在某些情况下，行可能不会立即解锁，因为结果行与其原始源之间的关系在查询执行期间丢失。例如，在一个 UNION，在评估表中扫描（并锁定）的行是否符合结果集之前，可能会将这些行插入到临时表中。在这种情况下，临时表中的行与原始表中的行的关系将丢失，并且原始表中的行直到查询执行结束才解锁。

    • SELECT ... LOCK IN SHARE MODE对搜索遇到的所有索引记录设置共享的临键锁。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
    • SELECT ... FOR UPDATE对搜索遇到的每个记录设置独占的临键锁锁定。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。 对于搜索遇到的索引记录， SELECT ... FOR UPDATE会阻止其他会话在某些事务隔离级别中执行 SELECT ... LOCK IN SHARE MODE或读取操作。一致读取会忽略对读取视图中存在的记录设置的任何锁定。

  • UPDATE ... WHERE ...对搜索遇到的每个记录设置独占的临键锁锁定。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
  • 当UPDATE修改聚集索引记录时，将对受影响的辅助索引记录进行隐式锁定。UPDATE在插入新的二级索引记录之前执行重复检查扫描时，以及插入新的二级索引记录时，该操作还会对受影响的二级索引记录获取共享锁 。
  • DELETE FROM ... WHERE ...对搜索遇到的每个记录设置独占的临键锁锁定。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
  • INSERT在插入的行上设置排它锁。该锁是索引记录锁，而不是临键锁（即没有间隙锁），并且不会阻止其他会话插入到插入行之前的间隙中。 在插入行之前，会设置一种称为插入意向锁的间隙锁。此锁表明插入的意图是，插入同一索引间隙的多个事务如果没有插入间隙内的同一位置，则无需互相等待。 如果发生重复键错误，则会在重复索引记录上设置共享锁。如果另一个会话已经拥有排它锁，则如果多个会话尝试插入同一行，则使用共享锁可能会导致死锁。
sql语句加锁SELECT ... FROM不加锁flush tables with read lock;加全局锁、获取全局锁lock tables 表名... read/write表锁，表共享读/写锁SELECT ... LOCK IN SHARE MODE共享的临键锁（搜索到的）/记录锁（唯一索引）SELECT ... FOR UPDATE排他的临键锁（搜索到的）/记录锁（唯一索引）UPDATE ... WHERE ...排他的临键锁（搜索到的）/记录锁（唯一索引）DELETE FROM ... WHERE ...排他的临键锁（搜索到的）/记录锁（唯一索引）INSERT排他的记录锁，插入行前的插入意向锁，唯一键冲突时设置共享锁insert、update、delete、select … for update（增、改、删、排他锁）意向排它锁，与表锁共享锁(read)及排他锁(write)都互斥INSERT ... ON DUPLICATE KEY UPDATE排他的记录锁，插入行前的插入意向锁，唯一键冲突时设置排他锁alter table ...（修改表结构）EXCLUSIVE（元数据排他锁），与其他的MDL都互斥delete 和update 更新的行不存在的时候会加 间隙锁。
3.3 控制事务

方式一 关闭事务自动提交，通过commit;
  1. # 1.查看/设置事务提交方式
  2. SELECT @@autocommit;
  3. SET @@autocommit=0;    #设为手动提交事务(1为自动提交，0为手动提交 执行完sql之后 运行commit;)
  4. # 2.提交事务   执行完sql之后 运行commit;
  5. COMMIT;
  6. # 3.回滚事务
  7. ROLLBACK;
复制代码
注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。
方式二 保持autocommit=1，不必设置autocommit=0。
  1. # 1.开启事务
  2. START TRANSACTION 或 BEGIN;
  3. ......     #执行语句
  4. # 2.提交事务。如果所有语句都成功执行，则提交事务
  5. COMMIT;
  6. # 3.回滚事务。如果在执行过程中发生错误，或者你想回滚事务，则使用ROLLBACK
  7. ROLLBACK;
复制代码
  1. -- 开启事务
  2. start transaction;
  3. -- 1. 查询张三余额
  4. select * from account where name = '张三';
  5. -- 2. 张三的余额减少1000
  6. update account set money = money - 1000 where name = '张三';
  7. -- 3. 李四的余额增加1000
  8. update account set money = money + 1000 where name = '李四';
  9. -- 如果正常执行完毕, 则提交事务
  10. commit;
  11. -- 如果执行过程中报错, 则回滚事务
  12. -- rollback;
复制代码
四、MySQL中的死锁

4.1 MySQL中的死锁现象

MySQL与Redis、Nginx这类单线程工作的程序不同，它属于一种内部采用多线程工作的应用，因而不可避免的就会产生死锁问题，比如举个例子：
  1. SELECT * FROM `wj_account`;
  2. +------------+---------+
  3. | user_name  | balance |
  4. +------------+---------+
  5. |    Jenny   | 6666666 |
  6. |    Lucy    | 8888888 |
  7. +------------+---------+
  9. -- T1事务：Lucy向Jenny转账
  10. UPDATE `wj_account` SET balance = balance - 888 WHERE user_name = "Lucy";
  11. UPDATE `wj_account` SET balance = balance + 888 WHERE user_name = "Jenny";
  13. -- T2事务：Jenny向Lucy转账
  14. UPDATE `wj_account` SET balance = balance - 666 WHERE user_name = "Jenny";
  15. UPDATE `wj_account` SET balance = balance + 666 WHERE user_name = "Lucy";
复制代码
上面有一张很简单的账户表，因为只是为了演示效果，所以其中仅设计了用户名和余额两个字段，紧接着有T1、T2两个事务，T1中Lucy向Jenny转账，而T2中则是Jenny向Lucy转账，也就是一个相互转账的过程，此时来分析一下：

  • ①T1事务会先扣减Lucy的账户余额，因此会修改数据，此时会默认加上排他锁。
  • ②T2事务也会先扣减Jenny的账户余额，因此同样会对Jenny这条数据加上排他锁。
  • ③T1减完了Lucy的余额后，准备获取锁把Jenny的余额加888，但由于此时Jenny的锁被T2事务持有，T1会陷入阻塞等待。
  • ④T2减完Jenny的余额后，也准备获取锁把Lucy的余额加666，但此时Lucy的锁被T1持有。
此时就会出现问题，T1等待T2释放锁、T2等待T1释放锁，双方各自等待对方释放锁，一直如此僵持下去，最终就引发了死锁问题，那先来看看具体的SQL执行情况是什么样的呢？如下：

如上图所示，一步步的跟着标出的序号去看，最终会发现：当死锁问题出现时，MySQL会自动检测并介入，强制回滚结束一个“死锁的参与者（事务）”，从而打破死锁的僵局，让另一个事务能继续执行。
看到这里有小伙伴会问了，为啥MySQL能自动检测死锁呀？其实这跟死锁检测机制有关，后续再细说。
注意：
但是要牢记一点，如果你也想自己做上述实验，那么千万不要忘了在创建表后，基于user_name创建一个主键索引：
  1. ALTER TABLE `wj_account` ADD PRIMARY KEY p_index(user_name);
复制代码
如果你不为user_name字段加上主键索引，那是无法模拟出死锁问题的，这是为什么呢？还记得之前在 《MySQL锁、加锁机制（超详细）—— 锁分类、全局锁、共享锁、排他锁；表锁、元数据锁、意向锁；行锁、间隙锁、临键锁；乐观锁、悲观锁》 中聊到的记录锁嘛？在InnoDB中，如果一条SQL语句能命中索引执行，那就会加行锁，但如果无法命中索引加的就是表锁。
在上述给出的案例中，因为表中没有显示指定主键，同时也不存在一个唯一非空的索引，因此InnoDB会隐式定义一个row_id来维护聚簇索引的结构，但因为update语句中无法使用这个隐藏列，所以是走全表方式执行，因此就将整个表数据锁起来了。
而这里的四条update语句都是基于wj_account账户表在操作，因此两个事务竞争的是同一个锁资源，所以自然无法复现死锁现象，也就是T1修改时，T2的第一条SQL也不能执行，会阻塞等待表锁的释放。
而当咱们显示的定义了主键索引后，InnoDB会基于该主键字段去构建聚簇索引，因此后续的update语句可以命中索引，执行时自然获取的也是行级别的排他锁。
4.2 MySQL中死锁如何解决

在之前关于死锁的并发文章中聊到过，对于解决死锁问题可以从多个维度出发，比如预防死锁、避免死锁、解除死锁等，而当死锁问题出现后该如何解决呢？一般只有两种方案：

  • 锁超时机制：事务/线程在等待锁时，超出一定时间后自动放弃等待并返回。
  • 外力介入打破僵局：第三者介入，将死锁情况中的某个事务/线程强制结束，让其他事务继续执行。
4.2.1 MySQL的锁超时机制

在InnoDB中其实提供了锁的超时机制，也就是一个事务在长时间内无法获取到锁时，就会主动放弃等待，抛出相关的错误码及信息，然后返回给客户端。但这里的时间限制到底是多久呢？可以通过如下命令查询：
  1. show variables like 'innodb_lock_wait_timeout';
  2. +--------------------------+-------+
  3. | Variable_name            | Value |
  4. +--------------------------+-------+
  5. | innodb_lock_wait_timeout | 50    |
  6. +--------------------------+-------+
复制代码
默认的锁超时时间是50s，也就是在50s内未获取到锁的事务，会自动结束并返回。那也就意味着当死锁情况出现时，这个死锁过程最多持续50s，然后其中就会有一个事务主动退出竞争，释放持有的锁资源，这似乎听起来蛮不错呀，但实际业务中，仅依赖超时机制去解除死锁是不够的，毕竟高并发情况下，50s时间太长了，会导致越来越多的事务阻塞。
那么咱们能不能把这个参数调小一点呢？比如调到1s，可以吗？当然可以，确实也能确保死锁发生后，在很短的时间内可以自动解除，但改掉了这个参数之后，也会影响正常事务等待锁的时间，也就是大部分未发生死锁，但需要等待锁资源的事务，在等待1s之后，就会立马报错并返回，这显然并不合理，毕竟容易误伤“友军”。
也正是由于依靠锁超时机制，略微有些不靠谱，因此InnoDB也专门针对于死锁问题，研发了一种检测算法，名为wait-for graph算法。
4.2.2 死锁检测算法 - wait-for graph

这种算法是专门用于检测死锁问题的，在该算法中会对于目前库中所有活跃的事务生成等待图，啥意思呢？以上述的死锁案例来看，在MySQL内部会生成一张这样的等待图：

也就是T1持有着「Lucy」这条数据的锁，正在等待获取「Jenny」这条数据的锁，而T2事务持有「Jenny」这条数据的锁，正在等待获取「Lucy」这条数据的锁，最终T1、T2两个事务之间就出现了等待闭环，因此当MySQL发现了这种等待闭环时，就会强制介入，回滚结束其中一个事务，强制打破该闭环，从而解除死锁问题【这个“等待图”只是为了方便理解画出来的，内部的实现其实存在些许差异】。
wait-for graph算法被启用后，会要求MySQL收集两个信息：

  • 锁的信息链表：目前持有每个锁的事务是谁。
  • 事务等待链表：阻塞的事务要等待的锁是谁。
每当一个事务需要阻塞等待某个锁时，就会触发一次wait-for graph算法，该算法会以当前事务作为起点，然后从「锁的信息链表」中找到对应中锁信息，再去根据锁的持有者（事务），在「事务等待链表」中进行查找，看看持有锁的事务是否在等待获取其他锁，如果是，则再去看看另一个持有锁的事务，是否在等待其他锁.....，经过一系列的判断后，再看看是否会出现闭环，出现的话则介入破坏。
案例理解：
上面这个算法的过程，听起来似乎有些晕乎乎的，但实际上并不难，套个例子来理解，好比目前库中有T1、T2、T3三个事务、有X1、X2、X3三个锁，事务与锁的关系如下：

此时当T3事务需要阻塞等待获取X1锁时，就会触发一次wait-for graph算法，流程如下：

  • ①先根据T3要获取的X1锁，在「锁的信息链表」中找到X1锁的持有者T1。
  • ②再在「事务等待链表」中查找，看看T1是否在等待获取其他锁，此时会得知T1等待X2。
  • ③再去「锁的信息链表」中找到X2锁的持有者T2，再看看T2是否在阻塞等待获取其他锁。
  • ④再在「事务等待链表」中查找T2，发现T2正在等待获取X3锁，再找X3锁的持有者。
经过上述一系列算法过程后，最终会发现X3锁的持有者为T3，而本次算法又正是T3事务触发的，此时又回到了T3事务，也就代表着产生了“闭环”，因此也可以证明这里出现了死锁现象，所以MySQL会强制回滚其中的一个事务，来抵达解除死锁的目的。
但出现死锁问题时，MySQL会选择哪个事务回滚呢？之前分析过，当一个事务在执行SQL更改数据时，都会记录在Undo-log日志中，Undo量越小的事务，代表它对数据的更改越少，同时回滚的代价最低，因此会选择Undo量最小的事务回滚（如若两个事务的Undo量相同，会选择回滚触发死锁的事务）。
同时，可以通过innodb_deadlock_detect=on|off这个参数，来控制是否开启死锁检测机制。
注意：死锁检测机制在MySQL后续的高版本中是默认开启的，但实际上死锁检测的开销不小，上面三个并发事务阻塞时，会对「事务等待链表、锁的信息链表」共计检索六次，那当阻塞的并发事务越来越多时，检测的效率也会呈线性增长。
4.2.3 如何预防/避免死锁产生

因为死锁的检测过程较为耗时，所以尽量不要等死锁出现后再去解除，而是尽量调整业务避免死锁的产生，一般来说可以从如下方面考虑：

  • 维持一定的锁定顺序：如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会，因为事务不会因为等待其他事务释放锁而相互阻塞。例如，如果有多个表或资源需要锁定，总是按照相同的顺序（如字典顺序）锁定这些资源。
  • 减小锁粒度：合理的设计索引结构，使业务SQL在执行时能通过索引定位到具体的几行数据，避免无索引行锁升级为表锁，尽量使用行级锁而不是表级锁，减小锁的粒度（SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化）
  • 使用低隔离级别：业务允许的情况下，也可以将隔离级别调低，因为级别越低，锁的限制会越小。
  • 调整业务SQL的逻辑顺序，较大、耗时较长的事务尽量放在特定时间去执行（如凌晨对账...）。
  • 缩短一个事务持有锁的时间：避免事务中的用户交互，保持事务简短并在一个批处理中；尽可能的拆分业务的粒度，一个业务组成的大事务，尽量拆成多个小事务，缩短一个事务持有锁的时间
  • 减少不必要的锁：如果没有强制性要求，就尽量不要手动在事务中获取排他锁，否则会造成一些不必要的锁出现，增大产生死锁的几率。
  • 减少事务持续时间、使用锁超时：尽量缩短事务的执行时间，长事务占用锁的时间越长，与其他事务发生冲突的可能性就越大；设置锁的超时时间，在等待锁超过设定的时间后将自动回滚，这不仅可以防止死锁，还可以避免一个事务无限期地等待资源
  • 如果业务处理不好，可以用分布式事务锁或者使用乐观锁
  • 监控和日志记录：实施监控和日志记录来跟踪死锁和性能瓶颈。这可以帮助识别导致死锁的具体事务和操作，从而进行针对性的优化。
  • 死锁检测和回滚：启用数据库的死锁检测功能，让数据库管理系统能够自动检测死锁并回滚某个事务来解锁。这通常是最后的手段，因为它可能导致数据不一致的问题。应当只在其他方法都无法实现时使用。
  • ........
其实简单来说，也就是在业务允许的情况下，尽量缩短一个事务持有锁的时间、减小锁的粒度以及锁的数量。
同时也要记住：当MySQL运行过程中产生了死锁问题，那这个死锁问题以后绝对会再次出现，当死锁被MySQL自己解除后，一定要记住去排除业务SQL的执行逻辑，找到产生死锁的业务，然后调整业务SQL的执行顺序，这样才能从根源上避免死锁产生。
五、如何确保 N 个线程可以访问 N 个资源，同时又不导致死锁

使用多线程的时候，一种非常简单的避免死锁的方式就是：指定获取锁的顺序，并强制线程按照指定的顺序获取锁。
因此，如果所有的线程都是以同样的顺序加锁和释放锁，就不会出现死锁了。
参考 JavaGuide操作系统常见面试题总结、MySQL死锁及源码分析！、全解MySQL之死锁问题分析、事务隔离与锁机制的底层原理剖析

来源:https://www.cnblogs.com/qhhfRA/p/18537569
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x

上一篇: 【GreatSQL优化器-01】const_table

下一篇: 没有了

发表于 3 天前

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4