| Bug #114548 | UPDATE statements using subqueries behave weirdly under Read Uncommitted | ||
|---|---|---|---|
| Submitted: | 4 Apr 2024 6:08 | Modified: | 4 Apr 2024 15:47 |
| Reporter: | Dai Dinary | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.3.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | read uncommitted, subquery, transaction | ||
[4 Apr 2024 6:08]
Dai Dinary
[4 Apr 2024 9:32]
MySQL Verification Team
Hi Mr. Dinary, Thank you for your bug report. However, it is not clear what are you reporting here ..... READ-UNCOMMITTED, READ-COMMITTED and REPEATABLE-READ all have different locking schemes. Hence, locking will be totally different between each different isolation level. Hence, we do not see what bug are you actually reporting here. Not a bug.
[4 Apr 2024 15:47]
Dai Dinary
Thanks for your reply. However, I think it's a bug. Under Read Uncommitted and Read Committed, the UPDATE statement in transaction 2 matches the first row and updates the first row. The UPDATE statement in transaction 2 only locks the first row under Read Committed, but locks both the first and the second row under Read Uncommitted. Why does the UPDATE statement in transaction 2 lock more rows under Read Uncommitted than under Read Committed? Under Read Uncommitted, after the UPDATE statement in transaction 2 is executed, the transaction status is as follows. ------------ TRANSACTIONS ------------ Trx id counter 57692 Purge done for trx's n:o < 57690 undo n:o < 0 state: running but idle History list length 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421732379427624, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421732379425200, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421732379424392, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 57691, ACTIVE 4 sec fetching rows mysql tables in use 2, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 9, OS thread handle 140256927094528, query id 88 172.17.0.1 root executing UPDATE t1 SET value=100 WHERE c0 <= (SELECT MIN(value) FROM t0) Trx read view will not see trx with id >= 57692, sees < 57690 ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1593 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 57691 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000e15a; asc Z;; 2: len 7; hex 01000001f61513; asc ;; 3: len 4; hex 80000039; asc 9;; 4: len 4; hex 800000c8; asc ;; ------------------ TABLE LOCK table `test`.`t1` trx id 57691 lock mode IX RECORD LOCKS space id 1593 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 57691 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000000e15b; asc [;; 2: len 7; hex 02000001362d28; asc 6-(;; 3: len 4; hex 80000064; asc d;; 4: len 4; hex 80000008; asc ;; RECORD LOCKS space id 1593 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 57691 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000e15a; asc Z;; 2: len 7; hex 01000001f61513; asc ;; 3: len 4; hex 80000039; asc 9;; 4: len 4; hex 800000c8; asc ;; ---TRANSACTION 57690, ACTIVE 11 sec 4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 3 MySQL thread id 8, OS thread handle 140257258784512, query id 85 172.17.0.1 root TABLE LOCK table `test`.`t1` trx id 57690 lock mode IX TABLE LOCK table `test`.`t0` trx id 57690 lock mode IX RECORD LOCKS space id 1592 page no 4 n bits 72 index PRIMARY of table `test`.`t0` trx id 57690 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000000e15a; asc Z;; 2: len 7; hex 01000001f614cd; asc ;; 3: len 4; hex 800000a2; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000000e15a; asc Z;; 2: len 7; hex 01000001f614f0; asc ;; 3: len 4; hex 800000a2; asc ;; RECORD LOCKS space id 1593 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 57690 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000e15a; asc Z;; 2: len 7; hex 01000001f61513; asc ;; 3: len 4; hex 80000039; asc 9;; 4: len 4; hex 800000c8; asc ;; Under Read Committed, after the UPDATE statement in transaction 2 is executed, the transaction status is as follows. ------------ TRANSACTIONS ------------ Trx id counter 57710 Purge done for trx's n:o < 57708 undo n:o < 0 state: running but idle History list length 7 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421732379427624, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421732379425200, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421732379424392, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 57709, ACTIVE 5 sec 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1 MySQL thread id 9, OS thread handle 140256927094528, query id 107 172.17.0.1 root TABLE LOCK table `test`.`t1` trx id 57709 lock mode IX RECORD LOCKS space id 1593 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 57709 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000000e16d; asc m;; 2: len 7; hex 02000001b60df7; asc ;; 3: len 4; hex 80000064; asc d;; 4: len 4; hex 80000008; asc ;; ---TRANSACTION 57708, ACTIVE 19 sec 4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 3 MySQL thread id 8, OS thread handle 140257258784512, query id 103 172.17.0.1 root TABLE LOCK table `test`.`t1` trx id 57708 lock mode IX TABLE LOCK table `test`.`t0` trx id 57708 lock mode IX RECORD LOCKS space id 1592 page no 4 n bits 72 index PRIMARY of table `test`.`t0` trx id 57708 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000000e16c; asc l;; 2: len 7; hex 01000001120682; asc ;; 3: len 4; hex 800000a2; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000000e16c; asc l;; 2: len 7; hex 010000011206a5; asc ;; 3: len 4; hex 800000a2; asc ;; RECORD LOCKS space id 1593 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 57708 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000e16c; asc l;; 2: len 7; hex 010000011206c8; asc ;; 3: len 4; hex 80000039; asc 9;; 4: len 4; hex 800000c8; asc ;;
[5 Apr 2024 10:14]
MySQL Verification Team
Hi Mr. Dinary, The problem with your bug report is that it is very unclear. You have two transactions, but you did not precise what is the isolation level of each of the transactions. Next, you have the following sentence , repeated twice in your report: " UPDATE statement in transaction 2 updates the first row, but is blocked by the lock added to the second row. However, under Read Committed, UPDATE statement in transaction 2 is not blocked. " May be it is clear to you, but we do not know what how to decipher what you are trying to explain to us with the above description. There is only one UPDATE statement in the transaction 2 and it is BOTH blocking and non-blocking a second row. That is quite confusing. There is yet another factor that you should consider. You are using a nested query in both SELECT and UPDATE and that nested query is a dependent one, so it is run for every matching row in the outer DML, be it SELECT or UPDATE. We are waiting for the clarifications ....
[5 Apr 2024 13:03]
MySQL Verification Team
Hi Mr. Dinary, We have not been lazy and we have analysed thoroughly your bug report, with version 8.3. Indeed, this is not a bug at all. If you compare the locks with read committed and read uncommitted, you find out that uncommitted truly has an additional lock. Namely this one: + RECORD LOCKS space id 843 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 44941 lock_mode X locks rec but not gap + Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 + 0: len 4; hex 80000002; asc ;; + 1: len 6; hex 00000000af8d; asc ;; + 2: len 7; hex 010000018a1dff; asc ;; + 3: len 4; hex 80000064; asc d;; + 4: len 4; hex 80000008; asc ;; + This is truly and thoroughly in compliance with MVCC levels that you used. Not a bug.
