专注于 JetBrains IDEA 全家桶,永久激活,教程
持续更新 PyCharm,IDEA,WebStorm,PhpStorm,DataGrip,RubyMine,CLion,AppCode 永久激活教程

MySQL数据库innodb_rollback_on_timeout参数

在使用MySQL数据库时,有时会出现ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 这样的报错。而在一个事务中,如果其中一条sql执行时出现此报错,对本事务的其他脚本是否有影响呢,后面如果执行commit操作,报错之前语句的结果是否成功呢? 这个结果与隔离级别以及innodb_rollback_on_timeout参数设置有关。

注: MySQL默认隔离级别为 REPEATABLE-READ,innodb_rollback_on_timeout为OFF,本文基于innodb表(支持事务)进行测试。

1、 准备工作

1.1 测试环境

MySQL 8.0

1.2 创建测试表及预备数据

创建一张测试表,并插入一条记录

mysql> use testdb;
Database changed
mysql> create table test1(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values(1,'1wdrt5');
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  test1;
+
| id | name   |
+
|  1 | 1wdrt5 |
+
1 row in set (0.00 sec)

下面将根据不同的隔离级别及innodb_rollback_on_timeout启停情况进行测试。

2、 测试过程

2.1 隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =OFF

a) 测试过程:

 

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

mysql> select * from test1 where id=1 for update;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
+—-+——–+
1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
+—-+——–+
2 rows in set (0.00 sec)

 

 

 

 

 

 

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

mysql> select  * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
+—-+——–+
1 row in set (0.00 sec)

mysql> insert into test1 values(2,’2edft6′);
Query OK, 1 row affected (0.00 sec)

mysql> delete from  test1 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
+—-+——–+
2 rows in set (0.00 sec)

 

 

 

 

 

b) 测试结果:

隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =OFF (2个参数均为默认值)的情况下,即使事务中有超时回滚报错,超时前的sql不会回滚,依旧执行成功。

2.2 隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =OFF

a) 测试过程

session A  session B
mysql> show global variables like ‘transaction_isolation’;
+———————–+—————-+
| Variable_name         | Value          |
+———————–+—————-+
| transaction_isolation | READ-COMMITTED |
+———————–+—————-+
1 row in set (0.01 sec)

mysql> use testdb;
Database changed
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
+—-+——–+
2 rows in set (0.00 sec)

mysql> select * from test1 where id =1 for update;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
+—-+——–+
1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
+—-+——–+
3 rows in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
+—-+——–+
2 rows in set (0.00 sec)

mysql> insert into test1 values(3,’3eft6′);
Query OK, 1 row affected (0.00 sec)

mysql> delete from test1 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
+—-+——–+
3 rows in set (0.00 sec)

 

 

 

 

b)测试结果:

隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =OFF 情况下,即使事务中有超时回滚报错,超时前的sql不会回滚,依旧执行成功,同2者均为默认值的情况。

2.3 隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =ON

注: innodb_rollback_on_timeout不能在线修改,需要修改配置文件后重启生效

测试过程:

a) 修改配置文件,重启数据库

在my.cnf文件里添加innodb_rollback_on_timeout=on 再重启数据库即可生效

mysql> show global variables like 'transaction_isolation';
+
| Variable_name         | Value           |
+
| transaction_isolation | REPEATABLE-READ |
+
1 row in set (0.00 sec)

mysql> show global variables like 'innodb_rollback_on_timeout';
+
| Variable_name              | Value |
+
| innodb_rollback_on_timeout | ON    |
+
1 row in set (0.00 sec)

b) 事务测试过程

session A session B
mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
+—-+——–+
3 rows in set (0.00 sec)

mysql> select * from test1 where id=1 for update;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
+—-+——–+
1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
+—-+——–+
3 rows in set (0.00 sec)

mysql> use testdb;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
+—-+——–+
3 rows in set (0.00 sec)

mysql> insert into test1 values(4,’4rgy7′);
Query OK, 1 row affected (0.00 sec)

mysql> delete from test1 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
+—-+——–+
3 rows in set (0.00 sec)

c) 测试结果:

隔离级别REPEATABLE-READ & innodb_rollback_on_timeout =ON 的情况下,事务中有超时回滚报错时,超时前sql也会回滚。

2.4 隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =ON

a) 参数调整

mysql> set global  transaction_isolation='READ-COMMITTED';
mysql> exit
#  重新登录
mysql> show global variables like 'transaction_isolation';
+
| Variable_name         | Value          |
+
| transaction_isolation | READ-COMMITTED |
+
1 row in set (0.00 sec)

mysql> show global variables like 'innodb_rollback_on_timeout';
+
| Variable_name              | Value |
+
| innodb_rollback_on_timeout | ON    |
+
1 row in set (0.00 sec)

b) 测试过程

session A  session B
mysql> use testdb;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select  * from  test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
+—-+——–+
3 rows in set (0.00 sec)

mysql> select * from test1 where id =1 for  update;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
+—-+——–+
1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

mysql> select  * from  test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
|  5 | 5thu8  |
+—-+——–+
4 rows in set (0.00 sec)

 

 

 

mysql> use testdb;
Database changed
mysql> select  * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
+—-+——–+
3 rows in set (0.00 sec)

mysql> insert into test1 values(5,’5thu8′);
Query OK, 1 row affected (0.01 sec)

mysql> delete from test1 where id =1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select  * from test1;
+—-+——–+
| id | name   |
+—-+——–+
|  1 | 1wdrt5 |
|  2 | 2edft6 |
|  3 | 3eft6  |
|  5 | 5thu8  |
+—-+——–+
4 rows in set (0.00 sec)

c) 测试结果

隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =ON的情况下,即使事务中有超时回滚报错,超时前的sql不会回滚,依旧执行成功,同2者均为默认值的情况。

3、 小结

在MySQL8.0 中,仅有在隔离级别为READ-COMMITTED & innodb_rollback_on_timeout =ON情况下,事务中有超时回滚报错时,超时前sql也会回滚。

隔离级别 innodb_rollback_on_timeout 结果
REPEATABLE-READ OFF 超时回滚前的SQL不会自动回滚
READ-COMMITTED OFF 超时回滚前的SQL不会自动回滚
REPEATABLE-READ ON 超时回滚前的SQL自动回滚
READ-COMMITTED ON 超时回滚前的SQL不会自动回滚

TIPS:

1) 测试过程中可以查看information_schema.innodb_trx表观察事务情况,在不同的版本中事务情况不一样.例如,隔离级别REPEATABLE-READ & innodb_rollback_on_timeout=on的情况下,MySQL5.6 中整个事务回滚后会自动创建一个事务,而MySQL5.7则不会再自动创建事务。

2) 在生产环境使用中,建议将innodb_rollback_on_timeout 设置为ON。应用程序一定要做好事务控制,在一个事务出现异常时必须进行显式rollback

文章永久链接:https://tech.souyunku.com/37735

未经允许不得转载:搜云库技术团队 » MySQL数据库innodb_rollback_on_timeout参数

JetBrains 全家桶,激活、破解、教程

提供 JetBrains 全家桶激活码、注册码、破解补丁下载及详细激活教程,支持 IntelliJ IDEA、PyCharm、WebStorm 等工具的永久激活。无论是破解教程,还是最新激活码,均可免费获得,帮助开发者解决常见激活问题,确保轻松破解并快速使用 JetBrains 软件。获取免费的破解补丁和激活码,快速解决激活难题,全面覆盖 2024/2025 版本!

联系我们联系我们