Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
None
-
MariaDB 12.3.3 on Linux/amd64
Description
A covering secondary-index LOCK IN SHARE MODE read returns a value committed after the transaction's consistent snapshot, while an equivalent PRIMARY-index locking read correctly raises ER_CHECKREAD (1020).
Steps to reproduce
Run the following as an account permitted to change event_scheduler and create events. The event performs the update in a separate transaction after the snapshot has opened.
Save this as case.sql:
DROP DATABASE IF EXISTS snapshot_test; |
CREATE DATABASE snapshot_test; |
USE snapshot_test; |
|
|
SET @old_event_scheduler=@@GLOBAL.event_scheduler; |
SET GLOBAL event_scheduler=ON; |
SET SESSION innodb_snapshot_isolation=ON; |
|
|
CREATE TABLE t1 |
(a INT PRIMARY KEY, b INT NOT NULL, KEY b_idx (b)) |
ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (1,10); |
|
|
CREATE EVENT update_b |
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 SECOND |
ON COMPLETION PRESERVE |
DO UPDATE t1 SET b=20 WHERE a=1; |
|
|
DELIMITER //
|
CREATE PROCEDURE run_test() |
BEGIN
|
DECLARE primary_err INT DEFAULT 0; |
DECLARE secondary_err INT DEFAULT 0; |
DECLARE primary_value INT DEFAULT NULL; |
DECLARE secondary_value INT DEFAULT NULL; |
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT; |
SELECT b INTO @snapshot_value FROM t1 WHERE a=1; |
DO SLEEP(3);
|
|
|
BEGIN |
DECLARE CONTINUE HANDLER FOR 1020 SET primary_err=1; |
SELECT b INTO primary_value |
FROM t1 FORCE INDEX(PRIMARY) |
LOCK IN SHARE MODE; |
END; |
|
|
BEGIN |
DECLARE CONTINUE HANDLER FOR 1020 SET secondary_err=1; |
SELECT b INTO secondary_value |
FROM t1 |
FORCE INDEX(b_idx) |
LOCK IN SHARE MODE; |
END; |
|
|
ROLLBACK; |
SELECT COUNT(*), MAX(b) INTO @current_count, @current_value |
FROM t1; |
SELECT @snapshot_value AS snapshot_value, |
@current_count AS current_count, |
@current_value AS current_value, |
primary_err, secondary_err,
|
primary_value, secondary_value;
|
END// |
DELIMITER ;
|
|
|
EXPLAIN SELECT b FROM t1 |
FORCE INDEX(b_idx) LOCK IN SHARE MODE; |
CALL run_test();
|
|
|
DROP DATABASE snapshot_test; |
SET GLOBAL event_scheduler=@old_event_scheduler; |
Execute it with:
mariadb < case.sql |
Actual result
EXPLAIN confirms that the secondary-index query is covered by b_idx:
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t1 index NULL b_idx 4 NULL 1 Using index
|
The procedure returns:
snapshot_value current_count current_value primary_err secondary_err primary_value secondary_value
|
10 1 20 1 0 NULL 20
|
The snapshot saw b=10, and the event subsequently committed b=20. The PRIMARY-index locking read raises error 1020, represented by primary_err=1. The equivalent covering secondary-index read does not raise the error; it returns the invisible current value 20, represented by secondary_err=0 and secondary_value=20.
Expected result
After another transaction commits b=20 outside an open snapshot that saw b=10, both equivalent locking reads should reject the invisible version with ER_CHECKREAD (1020). The covering secondary-index path should therefore set secondary_err=1 and must not return 20.
This occurs when snapshot isolation is combined with a covering secondary-index locking read after another transaction commits a change to the indexed value.