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

最新推荐文章于 2024-12-20 15:56:29 发布

原创最新推荐文章于 2024-12-20 15:56:29 发布 · 690 阅读

0 ·

CC 4.0 BY-SA版权

MYSQL 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一个关于MySQL中因事务隔离级别设置不当导致的查询阻塞问题。在一个使用InnoDB引擎的MySQL 5.5实例中，两个并发会话因为一个会话执行了长时间运行的更新操作而引发了阻塞。通过调整隔离级别为读已提交（READ-COMMITTED），解决了阻塞问题。

mysql:5.5

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'(会话级别);