Mysql之死锁案例

删除后插入死锁问题

环境准备

  1. 数据库版本

    $ mysql -V
    mysql  Ver 14.14 Distrib 5.6.35, for macos10.12 (x86_64) using  EditLine wrapper
    或者
    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.6.35    |
    +-----------+
    
  2. sql语句

    DROP TABLE IF EXISTS `t_lock`;
    CREATE TABLE `t_lock` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `a` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `unique_a` (`a`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    

开启监控

  1. 开启监控

    //第一种方式:基于系统参数
    set GLOBAL innodb_status_output=ON  开启
    set GLOBAL innodb_status_output=OFF 关闭
    //第二种方式:创建innodb_monitor的数据表
    CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
    DROP TABLE innodb_monitor;
    

步骤

  1. 数据库信息相关操作

    # mysql -u root -p
    # use lock
    //从innodb status获取更多的锁信息
    # set GLOBAL innodb_status_output_locks=ON;
    

    查看已有数据

    mysql> select * from t_lock;
    +----+------+
    | id | a    |
    +----+------+
    |  1 | a    |
    |  2 | b    |
    +----+------+
    2 rows in set (0.00 sec)  
    

    查看mysql事务隔离级别

    //查看mysql系统的事务隔离级别
     mysql> SELECT @@global.tx_isolation;
     +-----------------------+
     | @@global.tx_isolation |
     +-----------------------+
     | REPEATABLE-READ       |
     +-----------------------+
     1 row in set (0.00 sec)
        
    

    查看mysql会话的事务隔离级别

    mysql> SELECT @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)
     
    mysql> SELECT @@session.tx_isolation;
    +------------------------+
    | @@session.tx_isolation |
    +------------------------+
    | REPEATABLE-READ        |
    +------------------------+
    1 row in set (0.00 sec)
    

    查看是否自动提交事务(默认是自动提交)

    mysql> select @@autocommit;
    +--------------+
    | @@autocommit |
    +--------------+
    |            1 |
    +--------------+
    1 row in set (0.00 sec)
    

    设置不自动提交事务

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)
    
  2. 事务执行

    session1session2
    mysql> begin; @1
    mysql> begin; @11
    mysql> delete from t_lock where a = ‘aa’;  @2
    mysql> delete from t_lock where a = ‘aa’; @12
    mysql> insert into t_lock(a) values (‘aa’); @13 (等待锁)
    insert into t_lock(a) values ( ‘aa’);   @3 死锁
        @13:此处执行时,命令行处于等待状态。
        当@3执行完毕时,出现`ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction`异常时
        @13此时打印`Query OK, 1 row affected (4.19 sec)`
    
  3. 查看死锁日志

        ------------------------
        LATEST DETECTED DEADLOCK
        ------------------------
        2019-02-20 14:38:27 700009ce6000
        *** (1) TRANSACTION:
        //事务编号6719766,活跃11s   
        TRANSACTION 6719766, ACTIVE 11 sec inserting
        mysql tables in use 1, locked 1
        ///LOCK WAIT表示正在等待锁,获得3个锁结构
        LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
        MySQL thread id 3, OS thread handle 0x700009c62000, query id 95 localhost root update
        insert into t_lock(a) values ('aa')
        *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
        //等待insert intention(插入意向锁) waiting
        RECORD LOCKS space id 4921 page no 4 n bits 72 index `unique_a` of table `lock`.`t_lock` trx id 6719766 lock_mode X locks gap before rec insert intention waiting
        Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
         0: len 1; hex 62; asc b;;
         1: len 8; hex 0000000000000002; asc         ;;
        
        *** (2) TRANSACTION:
        TRANSACTION 6719765, ACTIVE 18 sec inserting
        mysql tables in use 1, locked 1
        3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
        MySQL thread id 9, OS thread handle 0x700009ce6000, query id 96 localhost root update
        insert into t_lock(a) values ( 'aa')
        *** (2) HOLDS THE LOCK(S):
        //locks gap before rec  表示为gap锁
        RECORD LOCKS space id 4921 page no 4 n bits 72 index `unique_a` of table `lock`.`t_lock` trx id 6719765 lock_mode X locks gap before rec
        Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
         0: len 1; hex 62; asc b;;
         1: len 8; hex 0000000000000002; asc         ;;
        
        *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
        // 等待insert intention(插入意向锁) waiting
        RECORD LOCKS space id 4921 page no 4 n bits 72 index `unique_a` of table `lock`.`t_lock` trx id 6719765 lock_mode X locks gap before rec insert intention waiting
        Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
         0: len 1; hex 62; asc b;;
         1: len 8; hex 0000000000000002; asc         ;;
         //回滚事务2
         *** WE ROLL BACK TRANSACTION (2)
      
    

    当session1执行删除语句时,由于条件a = 'aa'的记录不存在。session1 获得2个锁结构,分别是排他锁(行级锁)、gap锁(行级锁)。session2执行删除时,由于条件a = 'aa'的记录不存在,session2也获得2个锁结构。session1, session2 都在等待插入意向锁, 插入意向锁与gap锁冲突,双方都没有释放gap锁,又都在等待插入意向锁,死锁发生。

    session1session2
    begin;
    begin;
    delete from t_lock where a = ‘aa’;
    delete from t_lock where a = ‘aa’; 等待
    insert into t_lock(a) values ( ‘aa’);
    死锁错误

解决方案

  1. 在不修改隔离级别的情况下(RR),修改业务代码逻辑,删除记录之前,先select,确认该记录存在,再执行delete删除该记录。

插入更新导致的死锁

  1. 表结构

    DROP TABLE IF EXISTS `t_test`;
    CREATE TABLE `t_test` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `a` varchar(10) CHARACTER SET utf8 NOT NULL,
      `b` varchar(10) CHARACTER SET utf8 NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
      BEGIN;
    INSERT INTO `t_test` VALUES (1, 'a1', 'b1');
    INSERT INTO `t_test` VALUES (2, 'a2', 'b2');
    INSERT INTO `t_test` VALUES (3, 'a3', 'b3');
    INSERT INTO `t_test` VALUES (4, 'a4', 'b4');
    COMMIT;
    
    
  2. 第一种情况,因为没有索引,所以会锁表

    时间序Session1Session2
    1BEGIN;
    2BEGIN;
    3insert into t_test(a,b)
    select a,b from t_test
    where a = “a1”;
    4update t_test set b = “b11” where a = “a1”;
    阻塞
    5update t_test set b = “b11”
    where a = “a2”;
    6Deadlock found when trying to get lock; try restarting transaction。出现死锁
  3. 第二种情况,如果给a列加一列索引,此时复现死锁

    时间序Session1Session2
    1BEGIN;
    2BEGIN;
    3insert into t_test(a,b)
    select a,b from t_test
    where a = “a1”;
    4update t_test set b = “b11” where a = “a1”;
    阻塞
    5update t_test set b = “b11”
    where a = “a1”;
    6Deadlock found when trying to get lock; try restarting transaction。出现死锁
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值