It seems that if SELECT ... FOR UPDATE
has to wait for a lock, and meanwhile another thread changes what would be the result, and commits, then the initial query returns the old result, i.e. from before the change.
Is this expected behavior or a bug? It certainly does not seem useful.
It is easy to demonstrate. Let's take the following table:
CREATE TABLE `orders`(
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`description` VARCHAR(50),
PRIMARY KEY (`id`)
) ENGINE=INNODB;
And this sequence of statements:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT MAX(id)
FROM orders
FOR UPDATE;
# PAUSE HERE
INSERT INTO orders (description)
VALUES ('bla');
COMMIT;
Now we do the following:
- Open two connections.
- On connection 1, execute up to the pause. This succeeds.
- On connection 2, execute up to the pause. This gets stuck waiting for a lock.
- On connection 1, execute the rest. This succeeds.
- Connection 2 has now returned the previous id!
- On connection 2, execute the
SELECT
once more. Now it returns the updated value.
So to get behavior that seems sensible and desirable, it seems that we are forced to execute the SELECT
twice.
Is this expected behavior or a bug?
(Tested on MySQL 5.7.25)