mysql 采用内嵌视图更新表引起的1205报错

mysql:5.5

mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |

innodb引擎

场景:

mysql> select * from t1;

+------+------+---------------------+
| c1   | c2   | c3                  |
+------+------+---------------------+
|    1 |    2 | 2017-11-08 10:24:19 |
|    3 |    4 | 2017-11-08 10:24:26 |
|    5 |    6 | 2017-11-08 10:24:32 |
+------+------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+------+------+---------------------+
| c1   | c2   | c3                  |
+------+------+---------------------+
|    5 |    6 | 2017-11-08 10:25:10 |
|    7 |    8 | 2017-11-08 10:25:18 |
+------+------+---------------------+


mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  KEY `idx01` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  KEY `idx01` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1,(select c1,sum(c2) 'c2' from t2 where c3 between '2017-11-08 00:00:00' and '2017-11-08 23:59:59' group by c1) as tmp2
    -> set t1.c2=tmp2.c2
    -> where t1.c1=tmp2.c1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0


卡住，发生阻塞.
会话二:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(10,11,now());

解决方式：
在执行时间较长的会话加入隔离级别设置，比如在会话1中加入:set tx_isolation='READ-COMMITTED'(会话级别);