0

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:

  1. Open two connections.
  2. On connection 1, execute up to the pause. This succeeds.
  3. On connection 2, execute up to the pause. This gets stuck waiting for a lock.
  4. On connection 1, execute the rest. This succeeds.
  5. Connection 2 has now returned the previous id!
  6. 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)

0

This is expected behaviour. If you've started a select query, you want those results to be consistent to the start of that query (or transaction). Because you're specifying FOR UPDATE, you're indicating the start of a transaction. When you commit that's the end of your transaction, which is why step 6 returns the updated value.

If another session updates a row that is involved in your select and commits it, your session uses rollback data to reconstruct the data as of the start time of your transaction.

  • Right. So I want to get the value at the time I have acquired the lock - the value that I know is not going to be changed by another process, since I now have the lock. Is issuing the SELECT once more the way to go about this? Or is there a better way? – Timo Jan 24 at 15:12
0

You don't need any complexity to simply do

INSERT ...;
SELECT LAST_INSERT_ID();

The value of LAST_INSERT_ID() is held in the connection, so it is impervious to any other actions going on.

Why do you need MAX(id)?

Case 1: You will use it in some other insert. Then the transaction is perfectly fine this way:

BEGIN;
INSERT ...;
SELECT @id := LAST_INSERT_ID();  -- and put into local variable or @variable
INSERT something else ... VALUES (..., @id, ...);
COMMIT;

Case 2: Well, explain your case.

  • Here is the case: Multiple instances of a process will insert items. The real primary key is more like name, sequence_number. For a certain name, the process adds contiguous sequence numbers. Auto-increment is not satisfactory, because the sequence numbers are on a per-name basis (among other reasons). The instances of the process must not interfere. So one gets the greatest existing sequence number for a given name, write-locking it. It then inserts the next sequence. Once it completes, the next process may acquire the lock, continuing where the previous left off. – Timo Jan 25 at 9:08
  • @Timo - That is a different scenario from what your question asked -- It specifically had an auto_inc as the PK. I suggest you abandon this Q&A (since it has some discussion) and start a new Question with the details you just mentioned! – Rick James Jan 25 at 18:44
  • I see your point. In any case, perhaps you could still answer the main question from a general perspective: To get the MAX(id) value after acquiring the lock (assuming the previous lock owner has changed it), is there another way than performing an extra SELECT? – Timo Jan 28 at 9:34
  • @Timo - LAST_INSERT_ID does not need to go to the server; it is held with the connection. – Rick James Jan 29 at 5:59
0

I have figured out the explanation for the behavior, by doing some experiments. I have used connection 1 to perform some other changes rather than insert a new row. The combined results reveal how to process works.

  • If connection 1 updates the description for the id, connection 2 will see the updated description.
  • If connection 1 updates the id to a smaller value, e.g. from 10 to 9, connection 2 will return the new value, 9.
  • If connection 1 updates the id to a greater value, e.g. from 10 to 11, connection 2 will skip that value and instead return the greatest id that is smaller than what its original value was. In this example, it returns id 9 if that exists.
  • If connection 1 updates the id to a greater value, e.g. from 10 to 11, and then inserts a new row in place of the old one, e.g. with id 10, connection 2 will select the newly inserted row.
  • If connection 1 locks a MAX(id) of 10, and connection 2 selects a hard-coded id of 9, then the locks do not overlap, and both can proceed independently.

We can conclude that the MAX(id) is determined using a reverse scan, and that the locks are on actual (index) rows. This makes sense. Our isolation level of READ COMMITTED does not acquire gap locks. REPEATABLE READ might, but it causes the transaction of connection 2 to fail entirely, so there is no exploring that scenario any deeper.

Concluding the exact workings

Connection 2 starts reverse scanning until it hits a lock on a certain row.

It will wait for that lock to be released, and continue its scan from (and including) whatever id it left off at. Not whatever row, but whatever id.

  • As a result, if there is no longer a row at that id, the scan continues beyond it, to smaller ids.
  • As a result, if the row has been updated, the updated values are visible (if selecting more than just the id).
  • As a result, if the row has been moved and a new one inserted for its id, that new row is selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.