Description:
In Transaction 1, SELECT FOR UPDATE with a non-primary key query performs a current read and acquires a row-level lock on the corresponding primary key data.
If another transaction executes the same SELECT FOR UPDATE statement at this time, it will be block.
when Transaction 1 updates the primary key to a value smaller than the original, the update succeeds.
After Transaction 1 commits and releases the lock, Transaction 2 resumes execution. However, due to the change in the primary key, Transaction 2 initially fails to read the data.
Transaction 2 execute again will read right data.
in the above scenario, the updated primary key value is larger than the original, or if Transaction 2's SELECT FOR UPDATE is executed after the Transaction 2's update operation, Transaction 2 will be able to read the data normally after Transaction 1 commits.
Although the operational logic is the same in both cases, the results returned are inconsistent.
The trigger mechanism of the issue is clear and appears reasonable. This occurs because SELECT FOR UPDATE cannot anticipate what value the primary key will be updated to, and thus cannot pre-lock the future value—it only locks the current primary key value.
Due to the scanning order, the blocked transaction cannot scan backward to locate data with a smaller updated primary key value. Therefore, if the primary key is modified to a larger value, Transaction 2 can locate the updated data by scanning forward.
If SELECT FOR UPDATE is blocked by Transaction 1's update operation, it can still read the correct data once the lock is released.
From an application developer's perspective, the problem lies in the fact that despite the business logic being identical, the size of the modified primary key value and the timing of the blocking lead to inconsistent data retrieval results.
btw:A deadlock will occur in the above scenario under the RR isolation level, as expected.
How to repeat:
create table sbt(id int primary key,k int);
insert into sbt values(1,1),(2,2),(3,3);
T1:
session 1
begin;
select * from sbt where k=3 for update;
T2:
session 2
select * from sbt where k=3 for update; (blocked)
T3:
session 1
update sbt set id=0 where k=3;
commit;
T4:
session 2
return null result set;
select * from sbt where k=3 for update;
return 0,3
