I want to create a model with ID equal to the current greatest ID for that model plus one (like auto-increment). I'm considering doing this with select_for_update to ensure there is no race condition for the current greatest ID, like this:

with transaction.atomic():
 greatest_id = MyModel.objects.select_for_update().order_by('id').last().id
 MyModel.objects.create(id=greatest_id + 1)

But I'm wondering, if two processes try to run this simultaneously, once the second one unblocks, will it see the new greatest ID inserted by the first process, or will it still see the old greatest ID?

For example, say the current greatest ID is 10. Two processes go to create a new model. The first one locks ID 10. Then the second one blocks because 10 is locked. The first one inserts 11 and unlocks 10. Then, the second one unblocks, and now will it see the 11 inserted by the first as the greatest, or will it still see 10 because that's the row it blocked on?

In the select_for_update docs, it says:

Usually, if another transaction has already acquired a lock on one of the selected rows, the query will block until the lock is released.

So for my example, I'm thinking this means that the second process will rerun the query for the greatest ID once it unblocks and get 11. But I'm not certain I'm interpreting that right.

Note: I'm using MySQL for the db.

No, I don't think this will work.

First, let me note that you should absolutely check the documentation for the database you're using, as there are many subtle differences between the databases that are not captured in the Django documentation.

Using the PostgreSQL documentation as a guide, the problem is that, at the default READ COMMITTED isolation level, the blocked query will not be rerun. When the first transaction commits, the blocked transaction will be able to see changes to that row, but it will not be able to see that new rows have been added.

It is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it does not see effects of those commands on other rows in the database.

So 10 is what will be returned.

Edit: My understanding in this answer is wrong, just leaving it for documentation's sake in case I ever want to come back to it.

After some investigation, I believe this will work as intended.

The reason is that for this call:

MyModel.objects.select_for_update().order_by('id').last().id

The SQL Django generates and runs against the db is actually:

SELECT ... FROM MyModel ORDER BY id ASC FOR UPDATE;

(the call to last() only happens after the queryset has already been evaluated.)

Meaning, the query scans over all rows both times it runs. Meaning the second time it runs, it will pick up the new row and return it accordingly.

I learned that this phenomenon is called a "phantom read", and is possible because the isolation level of my db is REPEATABLE-READ.

@KevinChristopherHenry "The issue is that the query is not rerun after the lock is released; the rows have already been selected" Are you sure that's how it works? Why does READ COMMITTED imply the select doesn't run after the lock is released? I thought the isolation level defines which snapshot of data a query sees when it runs, not ~when~ the query is run. It seems to me that whether the select happens before or after the lock is released is orthogonal to the isolation level. And by definition, doesn't a blocked query not select the rows until after it is unblocked?

For what it's worth, I tried to test this by opening two separate connections to my db in a shell and issuing some queries. In the first, I began a transaction, and got a lock 'select * from MyModel order by id for update'. Then, in the second, I did the same, causing the select to block. Then back in the first, I inserted a new row, and commited the transaction. Then in the second, the query unblocked, and returned the new row. This makes me think my hypothesis is correct.

P.S. I finally actually read the "undesirable results" documentation that you read and I see your point - in that example, it looks like it ignores rows that weren't preselected, so that would point to the conclusion that my second query wouldn't pick up the new row. But I tested in a shell and it did. Now I'm not sure what to make of this.