本篇内容介绍了“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>
“MySQL死锁的案例分享”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!