MySQL死锁情况

最近发现业务 DDL操作执行缓慢，后调查，发现以下两条SQL产生死锁问题

------------------------
LATEST DETECTED DEADLOCK
------------------------
180205 21:34:29
*** (1) TRANSACTION:
TRANSACTION 5 2578066233, ACTIVE 28 sec, process no 11909, OS thread id 47774425831744
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 3024, 2 row lock(s)
MySQL thread id 2834556361, query id 111894205001 10.62.62.29 app Searching rows for update
update table set a = 0 where b = 1 and time >=  '2018-02-05 21:04:00'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1251 page no 93600 n bits 144 index `PRIMARY` of table `log`.`table` trx id 5 2578066233 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 5 2578061276, ACTIVE 38 sec, process no 11909, OS thread id 47774340634944 updating or deleting, thread declared inside InnoDB 0
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 2834556164, query id 111894196703 10.62.62.9 app Updating
update table set a = 2,c = 'qiuzhongming',d = '2018-02-05 21:33:51' where id in (166707555,166707562,166707598,166707549,166707592,166707527,166707588,166707569,166707546,166707526,166707532,166707551,166707560,166707577,166707579,166707583,166707589,166707596,166707530,166707535,166707557,166707566,166707576,166707578,166707595,166707541,166707568)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1251 page no 93600 n bits 144 index `PRIMARY` of table `log`.`table` trx id 5 2578061276 lock_mode X locks rec but not gap

原因：事务1  由于update条件中>=' 2018-02-05 21:04:00 ',这条语句执行结束后将把满足这个时间范围内的所有的主键都锁起来，等commit后再释放。

          事务2  此SQL通过主键去更新，由于前者没有释放，而这个时候where条件中的主键有些被锁了，等待释放，所以导致执行缓慢。

以下是我做的一个测试：

事务1：

mysql>  show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_create_date` (`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from test1;
+----+----------+---------------------+
| id | name     | create_date         |
+----+----------+---------------------+
|  1 | xbb      | 2018-01-01 00:00:00 |
|  2 | xsg      | 2018-01-02 00:00:00 |
|  3 | mechieal | 2018-01-03 00:00:00 |
|  4 | stxm     | 2018-01-04 00:00:00 |
+----+----------+---------------------+
4 rows in set (0.00 sec)



mysql> SET AUTOCOMMIT =0;
Query OK, 0 rows affected (0.00 sec)

mysql>  update test1 set name ='icey' where name='mechieal' and create_date>='2018-01-02';     
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务二：

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test1 set name ='xbb' where id =3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 
mysql> update test1 set name ='xbb' where id =4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test1 set name ='xbb' where id =2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test1 set name ='xbb' where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0