多线程插入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')这条数据没有加锁。
接下来:
- 如果A线程执行insert ignore into test values ('3', '3');时 检测到 ('3', '3')有锁,被B线程锁住,互相等待释放锁,这时就形成了死锁。
- 如果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>
说明谁加锁的数据少,那么谁就检测到死锁,就先释放锁。