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
SELECTonce 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
Is this expected behavior or a bug?
(Tested on MySQL 5.7.25)