mysql insert 锁

本文通过实例详细解析了在多线程环境下,使用InnoDB引擎的MySQL数据库发生死锁的具体场景,包括死锁的产生原因、检测机制以及解决策略。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

多线程插入mysql时会发生死锁。

数据库test中,id1和id2是联合主键

当前数据库test内容为空。mysql引擎是:InnoDB,隔离级别为REPEATABLE-READ

 

情况一:

线程A开启事务:

mysql> start transaction;
Query OK, 0 rows affected
mysql> 

线程B开启事务:

mysql> start transaction;
Query OK, 0 rows affected
mysql> 

线程A,插入数据:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('1', '1'),('2', '2'),('3','3');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql>

线程A没有commit,所以数据('1', '1'),('2', '2'),('3','3')被上锁,

线程B这时也插入数据:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('2', '2'),('4','4');
等待中....

因为数据('2','2')被上锁,所以等待线程A释放锁,此时数据('4','4')还没有被上锁。线程A可以插入('4','4');

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('1', '1'),('2', '2'),('3','3');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT ignore into test values ('5', '5'),('4', '4')
;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> 

线程A提交:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('1', '1'),('2', '2'),('3','3');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT ignore into test values ('5', '5'),('4', '4')
;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected

mysql> 

线程B提交:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('2', '2'),('4','4');
Query OK, 0 rows affected
Records: 2  Duplicates: 2  Warnings: 2

mysql> commit;
Query OK, 0 rows affected

情况二:

线程A开启事务:

mysql> start transaction;
Query OK, 0 rows affected
mysql> 

线程B开启事务:

mysql> start transaction;
Query OK, 0 rows affected
mysql> 

线程A,插入数据:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('1', '1'),('2', '2'),('3','3');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql>

线程A没有commit,所以数据('1', '1'),('2', '2'),('3','3')被上锁,

线程B这时也插入数据:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('4', '4'),('2','2'),('5','5');
等待中....

因为数据('2','2')被上锁,所以等待线程A释放锁,此时数据('4','4')已经成功执行被上锁。线程A插入('4','4');会造成死锁

A线程插入('4','4'),('5','5'):

mysql> INSERT ignore into test values ('4', '4'),('6', '6')
;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> 

此时B线程检测到死锁:

mysql> INSERT ignore into test values ('4', '4'),('2','2'),('5','5');
1213 - Deadlock found when trying to get lock; try restarting transaction
mysql>

也就是说

A线程执行insert ignore into test values ('1', '1'),('2', '2');能执行成功,会给('1', '1'),('2', '2')加锁

B线程执行insert ignore into test values ('3', '3'),('2', '2'),('4','4');检测('3','3')没有加锁,然后给('3','3')加上锁,('2','2')检测到锁,被A线程锁住,等待锁释放,这时('4','4')这条数据没有加锁。

接下来:

  1. 如果A线程执行insert ignore into test values ('3', '3');时 检测到 ('3', '3')有锁,被B线程锁住,互相等待释放锁,这时就形成了死锁。
  2. 如果A线程执行insert ignore into test values ('4', '4');时 没检测到('4','4')有锁,可以正常执行。这时如果A线程commit,B线程就会提示重复数据。但是不形成死锁。

 死锁的方向

线程B死锁:

线程A执行:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('1', '1'),('2', '2');
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql>

线程B执行:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT into test values ('3', '3'),('2','2');
等待中....

然后线程A执行:

mysql> INSERT ignore into test values ('3', '3');
Query OK, 1 row affected

mysql> 

这时B线程检测到死锁,结束等待,并且自动回滚,线程A正常执行。

B检测到死锁:

mysql> INSERT into test values ('3', '3'),('2','2');
1213 - Deadlock found when trying to get lock; try restarting transaction
mysql> 

========

A线程执行:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('1', '1'),('2', '2'),('9','9'),('8','8');
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql>

B线程执行:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT into test values ('3', '3'),('2','2');
等待中

然后A线程执行:

mysql> INSERT ignore into test values ('3', '3');
Query OK, 1 row affected

mysql>

B线程检测到死锁,停止等待,自动回滚:

ysql> INSERT into test values ('3', '3'),('2','2');
1213 - Deadlock found when trying to get lock; try restarting transaction
mysql> 

线程A死锁:

线程A:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('1', '1'),('2', '2');
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql>

线程B:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT into test values ('3', '3'),('4','4'),('2','2');
等待中

这时A线程执行:

mysql> INSERT ignore into test values ('3', '3');
1213 - Deadlock found when trying to get lock; try restarting transaction
mysql>

检测到死锁,死锁在A线程这边,A线程自动回滚,B线程结束等待,正常提交

mysql> INSERT into test values ('3', '3'),('4','4'),('2','2');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> 

========

线程A执行:

mysql> start transaction;
Query OK, 0 rows affected

mysql> INSERT ignore into test values ('1', '1'),('2', '2');
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql>

线程B执行:

mysql> start transaction;
Query OK, 0 rows affected

mysql> insert into test values ('3','3'),('4','4'),('5','5'),('2','2');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql>

这时A线程执行:

mysql> INSERT ignore into test values ('3', '3');
1213 - Deadlock found when trying to get lock; try restarting transaction
mysql>

 A线程检测到死锁,并且自动回滚,释放锁,B线程结束等待。

猜想:谁加锁的数据少,那么谁就检测到死锁,就先释放锁。

例如:线程A执行:

mysql> start transaction;
Query OK, 0 rows affected

mysql> insert ignore into test values ('1','1'),('2','2');
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql>

线程A加锁数据:('1','1'),('2','2')

线程B:

mysql> start transaction;
Query OK, 0 rows affected

mysql> insert into test values ('3','3'),('4','4'),('5','5'),('2','2');
等待中....

线程B加锁数据:('3','3'),('4','4'),('5','5')

然后A线程执行:

mysql> insert ignore into test values ('6','6'),('7','7'),('8','8'),('9','9'),('3','3');

此时A线程加锁数据为:('1','1'),('2','2'),('6','6'),('7','7'),('8','8'),('9','9')

A线程加锁数据多于B线程,所以B线程检测到死锁,主动释放锁,

B线程:

mysql> insert into test values ('3','3'),('4','4'),('5','5'),('2','2');
1213 - Deadlock found when trying to get lock; try restarting transaction
mysql>

说明谁加锁的数据少,那么谁就检测到死锁,就先释放锁。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值