innodb_rollback_on_timeout 理解-详细内容

文章内容

2023/1/10 15:25:33,作者: 黄兵

innodb_rollback_on_timeout 理解

参数 Innodb_rollback_on_timeout 将控制发生超时故障时事务的行为。

如果指定了 –innodb-rollback-on-timeout=OFF（默认），则 InnoDB 仅回滚事务超时的最后一条语句。
如果指定了 –innodb-rollback-on-timeout=ON，事务超时会导致 InnoDB 中止并回滚整个事务。

让我们用以下场景进行测试：

Innodb_rollback_on_timeout = OFF 事务
Innodb_rollback_on_timeout = ON 的事务

测试环境

我出于测试目的创建了此表。

mysql> select * from rollback_on;
+----+-----------------+---------------------+
| id | name            | c_date              |
+----+-----------------+---------------------+
|  1 | jc              | 2020-07-23 00:44:09 |
|  2 | sri             | 2020-07-23 00:44:09 |
|  3 | hercules7sakthi | 2020-07-23 00:44:09 |
+----+-----------------+---------------------+
3 rows in set (0.01 sec)

下面我分享了测试这两种情况的常用步骤。

常见步骤

创建两个 MySQL 会话（s1 和 s2）
在 s1，创建事务并执行更新“name = 'sakthi' where id=2”。不要提交事务。
在 s2，创建另一个事务并执行更新“name = 'herc' where id=3”。不要提交事务。
在 s1，再次执行另一个更新“name = 'sakthi' where id=3”。它将产生超时错误，因为行 (id=3) 已被 s2 持有并锁定。
在 s1，一旦第二次更新出现超时错误，就提交事务。
验证结果并将其与实际数据进行比较。

场景 1 – Innodb_rollback_on_timeout = OFF 的事务

#my.cnf
innodb_rollback_on_timeout = OFF
innodb_lock_wait_timeout = 10

在 s1，

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

mysql> update rollback_on set name='sakthi' where id=2;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在 s2，

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

mysql> update rollback_on set name='herc' where id=3;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在 s1，

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

mysql> update rollback_on set name='sakthi' where id=2;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update rollback_on set name='sakthi' where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 

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

mysql> select * from rollback_on;
+----+-----------------+---------------------+
| id | name            | c_date              |
+----+-----------------+---------------------+
|  1 | jc              | 2020-06-21 18:28:03 |
|  2 | sakthi          | 2020-06-21 18:28:03 |
|  3 | hercules7sakthi | 2020-06-21 18:28:03 |
+----+-----------------+---------------------+
3 rows in set (0.00 sec)

概括：

在 s1，我们在事务中有两个更新。
一个更新完成，另一个更新失败。
提交事务后，可以在完成更新的实际表中看到修改后的数据。
它说明，如果 innodb_rollback_on_timeout=OFF，则整个事务不会在超时失败的情况下回滚。它将对已完成的 SQL 应用更改。

场景 2 – Innodb_rollback_on_timeout = ON 的事务

#my.cnf
innodb_rollback_on_timeout = ON
innodb_lock_wait_timeout = 10

在 s1，

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

mysql> update rollback_on set name='sakthi' where id=2;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在 s2，

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

mysql> update rollback_on set name='herc' where id=3;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在 s1，

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

mysql> update rollback_on set name='sakthi' where id=2;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update rollback_on set name='sakthi' where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 

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

mysql> select * from rollback_on;
+----+-----------------+---------------------+
| id | name            | c_date              |
+----+-----------------+---------------------+
|  1 | jc              | 2020-06-21 18:28:03 |
|  2 | sri             | 2020-06-21 18:28:03 |
|  3 | hercules7sakthi | 2020-06-21 18:28:03 |
+----+-----------------+---------------------+
3 rows in set (0.00 sec)