Bug #111154 The timout duration is not equal to the value of innodb_lock_wait_timeout

• Submitted: 25 May 2023 13:36
• Modified: 25 May 2023 13:41
• Reporter: Zeng Zihao
• Status: Unsupported
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.7.42
• OS: Any
• CPU Architecture: Any

[25 May 2023 13:36] Zeng Zihao

Description:
If we set the innodb_lock_wait_timeout = 2, the actual timeout duration is 3s. The timeput duration is 2s in MySQL 8.0.28, but wrong result in the 5.7.42.

How to repeat:
Session 1：

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> set innodb_lock_wait_timeout=2;
Query OK, 0 rows affected (0.00 sec)

mysql> use test;
Database changed
mysql> drop table if exists RANGE_ISOLATION_LEVEL_RR_INTER_WITH_SERI_002;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table RANGE_ISOLATION_LEVEL_RR_INTER_WITH_SERI_002(c_id int,c_d_id int NOT NULL,c_w_id  bigint NOT NULL);
Query OK, 0 rows affected (0.02 sec)

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

mysql> INSERT INTO RANGE_ISOLATION_LEVEL_RR_INTER_WITH_SERI_002 VALUES (1,1,234213542352134);
Query OK, 1 row affected (0.00 sec)

Session 2：

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> set innodb_lock_wait_timeout=2;
Query OK, 0 rows affected (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

mysql> select now(3);
+-------------------------+
| now(3)                  |
+-------------------------+
| 2023-05-25 21:30:25.533 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from RANGE_ISOLATION_LEVEL_RR_INTER_WITH_SERI_002;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select now(3);
+-------------------------+
| now(3)                  |
+-------------------------+
| 2023-05-25 21:30:28.534 |
+-------------------------+
1 row in set (0.00 sec)

[25 May 2023 13:38] Zeng Zihao

The timout duration is not equal to the value of innodb_lock_wait_timeout

[25 May 2023 13:41] MySQL Verification Team

Hi Mr. Zihao,

Thank you for your bug report.

However, version 5.7 is not in maintenance mode, which means we process only those reports that are dealing with crashing bugs or with security bugs.

Even our 8.0 version is now in the regular maintenance mode, which means that only future 8.1 version receives new features.

Unsupported.