| Bug #105988 | The problem about predicate lock in Serializable isolation level | ||
|---|---|---|---|
| Submitted: | 28 Dec 2021 7:22 | Modified: | 11 Jan 2022 14:09 |
| Reporter: | Siyang Weng | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7.27/8.0.27 | OS: | Red Hat (Red Hat 4.8.5-39) |
| Assigned to: | CPU Architecture: | x86 (3.10.0-1127.10.1.el7.x86_64) | |
[28 Dec 2021 7:22]
Siyang Weng
[28 Dec 2021 7:29]
Siyang Weng
The schema is: create table table0 (pkId integer, pkAttr0 integer, coAttr0_0 integer, primary key(pkAttr0));
[31 Dec 2021 6:32]
Siyang Weng
The similar problem can also be found in version 8.0.27. The schema is: create table table0 (pkId integer, pkAttr0 integer, coAttr0_0 integer, primary key(pkAttr0)) The general logs related with this problem are shown below: Time Id Command Argument 2021-12-30T05:41:11.592577Z 8278 Query SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 2021-12-30T05:41:11.592529Z 8273 Query SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE -- System executes other queries and the isolation level in 8278 remains unchanged. ... 2021-12-30T05:41:12.498916Z 8278 Query START TRANSACTION 2021-12-30T05:41:12.499661Z 8278 Execute insert into `table0`(`pkId`, `pkAttr0`, `coAttr0_0`) values(91, 91, 48959) 2021-12-30T05:41:12.500518Z 8278 Execute insert into `table0`(`pkId`, `pkAttr0`, `coAttr0_0`) values(1040, 1040, 60781) 2021-12-30T05:41:12.502026Z 8278 Execute insert into `table0`(`pkId`, `pkAttr0`, `coAttr0_0`) values(403, 403, 42217) 2021-12-30T05:41:12.503111Z 8278 Execute insert into `table0`(`pkId`, `pkAttr0`, `coAttr0_0`) values(408, 408, 22650) -- Transaction 8278 inserts record into table0 where pkAttr0 = 225 2021-12-30T05:41:12.559220Z 8273 Query START TRANSACTION 2021-12-30T05:41:12.559950Z 8273 Execute select `pkAttr0`, `coAttr0_0` from `table0` where ( `pkAttr0` = 408 ) -- Unexpected！This query should be blocked untill the transaction 8278 rollbacked. 2021-12-30T05:41:12.504601Z 8278 Execute insert into `table0`(`pkId`, `pkAttr0`, `coAttr0_0`) values(452, 452, 2684) 2021-12-30T05:41:12.720278Z 8278 Query rollback We share all general log in this Github repository https://github.com/wengsy150943/bug_report_attachment/tree/main/mysql/8.0-001 .
[10 Jan 2022 14:55]
MySQL Verification Team
Hi Mr. Weng, Thank you for your bug report. However, this does not seem to be a bug. But, we have to check it out. Make sure that first transaction is finished and then, only then you start second transaction. It is also very important for us to know whether autocommit is ON or OFF. It is important to note that this isolation level behaves like REPEATABLE READ, only with added locks. Last, but not least, try not to use brackets around the expressions when those are not needed.
[11 Jan 2022 6:36]
Siyang Weng
Thanks for your response! As you mentioned, we have confirmed that these two transactions overlap and both of these transactions' autocommit is OFF. Taking the above case in MySQL 8.0.27 as an example, transaction 8278 start at 2021-12-30T05:41:12.498916Z(Line 5972) and end at 2021-12-30T05:41:12.720278Z with a rollback(Line 6283). However, transaction 8273 has a select query executed at 2021-12-30T05:41:12.559950Z(Line 6158), which read the same data of transaction 8278. We also check that both of these transactions' autocommit is OFF(transaction 8278 at Line 4096 and transaction 8273 at Line 4103).
[11 Jan 2022 14:09]
MySQL Verification Team
Hi Mr. Weng, Actually, this isolation level works just fine with 8.0.27. SELECT query hangs !!!!!! Here it is: mysql> select `pkAttr0`, `coAttr0_0` from `table0` where ( `pkAttr0` = 408 ); There is no OK and no nothing ....... just hanging ...... when I commit first transaction, it runs ....... Can't repeat.
