记一次线上SQL死锁事故：如何避免死锁？

背景

      之前我参与过一个项目，在项目初期，我们是没有将读写表分离的，而是基于一个主库完成     读写操作。在业务量逐渐增大的时候，我们偶然会收到体系的异常报警信息，DBA 关照我     们数据库出现了死锁异常。        按理说业务开始是比力简单的，就是新增订单、修改订单、查询订单等操作，那为什么会出     现死锁呢？经过日志分析，我们发现是作为幂等性校验的一张表经常出现死锁异常。我们和     DBA 讨论之后，开端怀疑是索引导致的死锁问题。后来我们在开发环境中模拟了相干操     作，果然重现了该死锁异常。  生成问题重现

          接下来我们就通过实战来重现下该业务死锁异常。起首，创建一张订单记载表，该表重要用        于校验订单重复创建：      

1. CREATE TABLE `order_record` (
2. `id` int(11) NOT NULL AUTO_INCREMENT,
3. `order_no` int(11) DEFAULT NULL,
4. `status` int(4) DEFAULT NULL,
5. `create_date` datetime(0) DEFAULT NULL,
6. PRIMARY KEY (`id`) USING BTREE,
7. INDEX `idx_order_status`(`order_no`,`status`) USING BTREE
8. ) ENGINE = InnoDB

复制代码

      为了能重现该问题，我们先将事务设置为手动提交。这里要注意一下，MySQL 数据库和           Oracle 提交事务不太一样，MySQL 数据库默认情况下是自动提交事务，我们可以通过以           下下令行查察自动提交事务是否开启：         

1. mysql> show variables like 'autocommit';
2. +---------------+-------+
3. | Variable_name | Value |
4. +---------------+-------+
5. | autocommit | ON |
6. +---------------+-------+
7. 1 row in set (0.01 sec)

复制代码

        下面就操作吧，先将 MySQL 数据库的事务提交设置为手动提交，通过以下下令行可以关              闭自动提交事务：            

1. mysql> set autocommit = 0;
2. Query OK, 0 rows affected (0.00 sec)

复制代码

             订单在做幂等性校验时，先是通过订单号检查订单是否存在，如果不存在则新增订单记载。                 知道具体的逻辑之后，我们再来模拟创建产存亡锁的运行 SQL 语句。起首，我们模拟新建                两个订单，并按照以下顺序执行幂等性校验 SQL 语句（垂直方向代表执行的时间顺序）：               

                       此时，我们会发现两个事务已经进入死锁状态。我们可以在 information_schema 数据库                    中查询到具体的死锁情况，如下图所示：                  

                           看到这，你可能会想，        为什么 SELECT 要加 for update 排他锁，而不是使用共享锁呢？        试                       想下，如果是两个订单号一样的哀求同时进来，就有可能出现幻读。也就是说，一开始事务                       A 中的查询没有该订单号，后来事务 B 新增了一个该订单号的记载，此时势务 A 再新增一                               条该订单号记载，就会创建重复的订单记载。面临这种情况，我们可以使用锁间隙算法来防                          止幻读。                避免死锁的步伐

                 知道了死锁问题源自哪儿，就可以找到符合的方法来避免它了。                                  避免死锁最直观的方法就是在两个事务相互等待时，当一个事务的等待时间凌驾设置的某一                          阈值，就对这个事务举行回滚，另一个事务就可以继承执行了。这种方法简单有效，在                          InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的。                                  别的，我们还可以将 order_no 列设置为唯一索引列。固然不能防止幻读，但我们可以使用                          它的唯一性来保证订单记载不重复创建，这种方式唯一的缺点就是当遇到重复创建订单时会                          抛出异常。                                  我们还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及                          ZooKeeper 来实现，运行效率比数据库更佳。                        

---

         保举阅读

         

• 基于用户故事的领域驱动DDD
  
• MySQL调优
  
• MySQL调优之事务：高并发场景下的数据库事务调优-CSDN博客
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。