| Bug #109196 | FOR UPDATE doesn't block FOR SHARE if secondary index is used | ||
|---|---|---|---|
| Submitted: | 24 Nov 2022 15:39 | Modified: | 25 Nov 2022 14:33 |
| Reporter: | Vojtech Rylko | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Nov 2022 13:24]
MySQL Verification Team
Hi Mr. Rylko, Thank you very much for your bug report. However, this is not a bug. SELECT ...... FOR SHARE waits just fine for the lock to be released. You have probably ran all this in auto-commit mode. Not a bug.
[25 Nov 2022 14:18]
Vojtech Rylko
Hi, I'm explicitly starting transactions in both sessions using `BEGIN`. And when in transaction, `SESSION 2: SELECT a FROM Test WHERE b=1 FOR SHARE` doesn't wait. But if I use `SESSION 2: SELECT c FROM Test WHERE b=1 FOR SHARE`, it *does* wait. According documentation, both queries should wait.
[25 Nov 2022 14:33]
MySQL Verification Team
Hi Mr. Rylko, You have not provided us with full info when you reported the bug. However, it is a bug. InnoDB shows the same locks in both cases, but behaviour is different. Verified.
[25 Nov 2022 14:34]
MySQL Verification Team
Relevant lock info is the same in both cases: LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421996172479432, not started 0 lock struct(s), heap size 1192, 0 row lock(s) ---TRANSACTION 421996172478440, not started 0 lock struct(s), heap size 1192, 0 row lock(s) ---TRANSACTION 6429, ACTIVE 247 sec 2 lock struct(s), heap size 1192, 1 row lock(s) MySQL thread id 8, OS thread handle 123145505894400, query id 19 localhost sinisa starting show engine innodb status TABLE LOCK table `test`.`test` trx id 6429 lock mode IX RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`test` trx id 6429 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 80000001; asc ;; 1: len 6; hex 00000000191c; asc ;; 2: len 7; hex 81000001060110; asc ;; 3: len 4; hex 80000001; asc ;; 4: len 4; hex 80000001; asc ;;
Description: Documentation says > SELECT ... FOR UPDATE > For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE This isn't true: The `SELECT ... FOR UPDATE` *doesn't* block `SELECT ... FOR SHARE` if secondary index is used - see `How to repeat`. This means SELECT ... FOR UPDATE *doesn't* behave same as UPDATE statement, as UPDATE statement *does* block `SELECT ... FOR SHARE` in such cases - see comment in `How to repeat`. How to repeat: -- Prepare table with secondary index on 'b', and insert a row CREATE TABLE Test ( a INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL, UNIQUE INDEX b_idx(b) ); INSERT INTO Test SET a=1, b=1, c=1; -- Obtain exclusive lock in one session SESSION 1: BEGIN; SESSION 1: SELECT * FROM Test WHERE a=1 FOR UPDATE; -- Obtain shared lock in another session SESSION 2: BEGIN ; SESSION 2: SELECT a FROM Test WHERE b=1 FOR SHARE; -- Returns immediately, isn't blocked! (Would be blocked if SESSION 1 issued actual UPDATE on row `a=1`.) Suggested fix: The SELECT ... FOR UPDATE should lock all associated index entries (as said in documentation).