mysql中insert…select 死锁举例

请先看下上一篇日志 InnoDB的行锁模式及加锁方法。

对于普通SELECT的语句InnoDB不会加锁，但是insert into table1 … select … from table2 语句会给table2加锁，加行还是表锁跟索引有关。

测试表结构如下：

account 表建立组合索引（status, merchantId, customerId）

下面的数字（0-7）是测试事务sql语句的执行顺序。在事务1中执行完0，1在切换到事务2中执行2，3以此类推。

==================死锁示范1=======================

=======事务1======

0 mysql> start transaction;

1 mysql> insert into test(name,age) select cardNO,`status` from account where  merchantId=1 and customerId=1971; -- 表共享锁（没有用到索引）

4 mysql> update account set balance=balance+1 where merchantId=1 and customerId=1971;  --获取表排他锁，等待事务2（没有用到索引）

6 mysql> commit;

=======事务2======

2 mysql> start transaction;

3 mysql> insert into test(name,age) select cardNO,`status` from account where merchantId=1 and customerId=1972; -- 表共享锁（没有用到索引）

5 mysql> update account set balance=balance+1 where merchantId=1 and customerId=1972; --获取表排他锁，等待事务1，死锁（没有用到索引）

--错误提示：1213 - Deadlock found when trying to get lock; try restarting transaction

==================死锁示范2=======================

=======事务1======

0 mysql> start transaction;

1 mysql> insert into test(name,age) select cardNO,`status` from account where status='正常' and merchantId=1 and customerId=1971; --行共享锁（使用索引）

4 mysql> update account set balance=balance+1 where merchantId=1 and customerId=1971; --获取表排他锁，等待事务2（没有用到索引）

6 mysql> commit;

=======事务2======

2 mysql> start transaction;

3 mysql> insert into test(name,age) select cardNO,`status` from account where status='正常' and merchantId=1 and customerId=1972; --行共享锁（使用索引）

5 mysql> update account set balance=balance+1 where merchantId=1 and customerId=1972;--获取表排他锁，等待事务1，死锁（没有用到索引）

--错误提示：1213 - Deadlock found when trying to get lock; try restarting transaction

==================死锁示范3=======================

=======事务1======

0 start transaction;

1 insert into test(name,age) select cardNO,`status` from account where status='正常' and merchantId=1 and customerId=1971; --行锁（使用索引）

4 update account set balance=balance+1 where status='正常' and merchantId=1 and customerId=1971; --获取行排他锁，直接执行成功（使用索引）

6 commit;

=======事务2======

2 start transaction;

3 insert into test(name,age) select cardNO,`status` from account where status='正常' and merchantId=1 and customerId=1972; --行锁（使用索引）

5 update account set balance=balance+1 where status='正常' and merchantId=1 and customerId=1972;--获取行排他锁，直接执行成功（使用索引）

7 commit;