MySQL死锁的案例分享

本篇内容介绍了“死锁的案例分享”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

两个死锁的小例子：

死锁案例1

      比如自增列的死锁，一般的死锁得是4条DML语句互相牵制，我们可以做整合，简化，使得死锁的场景变得复杂起来。我们简单来模拟一下。   

create table t8

(c1 int auto_increment,

 c2 int default null,

primary key(c1),

unique key (c2)

)ENGINE=InnoDB  ;

#session1

Begin;

insert into t8 values(null,10);

#session2

insert into t8 values(null,10);

#session1

insert into t8 values(null,9);

死锁案例2

   比如我们难度升级，两条delete语句导致的死锁。

create Table: CREATE TABLE `d` (

  `i` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`i`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into d values(1);

##session1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select *from d where i=1 lock in share mode;

+---+

| i |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

##session2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select *from d where i=1 lock in share mode;

+---+

| i |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

#session1

mysql> delete from d where i=1;

Query OK, 1 row affected (10.80 sec)

##session2

mysql> delete from d where i=1;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql>