0

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.

Michael Harvey
  • 228
  • 3
  • 10
  • As an alternative, consider using optimistic concurrency. See [my answer here](https://stackoverflow.com/questions/24331420/django-how-to-wrap-a-bulk-update-insert-operation-in-transaction/24334777#24334777) for a bit more detail. As I said there: "This kind of approach can work really well if collisions are rare, and really badly if they are frequent." – Kevin Christopher Henry Mar 08 '19 at 00:50
  • Yes, I don't expect collisions often, so I will actually plan to use this. Thank you so much for your help. – Michael Harvey Mar 08 '19 at 23:41

2 Answers2

1

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.

Kevin Christopher Henry
  • 46,175
  • 7
  • 116
  • 102
  • I don't think this is right because in my particular case, the SQL django generates queries for all the rows in MyModel, not just the one. So while it is true that the second query will see an inconsistent snapshot, it will still scan for all rows, not just the one, and indeed pick up the new one ~because~ the snapshot is inconsistent. What do you think? – Michael Harvey Mar 07 '19 at 04:46
  • @MichaelHarvey: The issue is that the query is not rerun after the lock is released; the rows have already been selected. Look in the documentation I linked to for the example of "undesirable results in Read Committed mode". If you were right that the query is re-run, the results would be different than described. – Kevin Christopher Henry Mar 07 '19 at 07:55
  • my response is too long to type here, so I added it to the bottom of my answer. Please let me know what you think if you are interested in continuing this discussion. Thank you for your input. – Michael Harvey Mar 07 '19 at 20:23
  • I just retested this by getting a lock and doing an insert from a shell, and trying to do the select_for_update then insert from the django app. The app failed because it didn't read the new id, and tried to insert the old id. Your answer is correct. – Michael Harvey Mar 08 '19 at 00:38
0

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.

Michael Harvey
  • 228
  • 3
  • 10
  • You are probably not using `REPEATABLE READ`. That's the MySQL default, but Django sets its transactions to `READ COMMITTED` [by default](https://docs.djangoproject.com/en/dev/ref/databases/#mysql-isolation-level). It's possible to override this in settings, but you should certainly be cautious about that: "Django works best with and defaults to read committed rather than MySQL’s default, repeatable read. Data loss is possible with repeatable read." – Kevin Christopher Henry Mar 07 '19 at 07:58
  • I checked the db settings and verified it's REPEATABLE READ. I am not the original owner of this Db, so I assume someone else must have overrode it. I will look into whether changing it is a good option. Thanks for info. – Michael Harvey Mar 07 '19 at 20:27
  • It doesn't matter what the database setting is, because this can be set by the client. And every time Django opens a connection it sets the isolation level to what is specified in the settings (or, by default, `READ COMMITTED`). See [here](https://github.com/django/django/blob/88619e6129fd8c6668242f1acc1465ca175d2948/django/db/backends/mysql/base.py#L239). – Kevin Christopher Henry Mar 07 '19 at 21:59
  • Perhaps the difference is that my answer is based on the PostgreSQL documentation, and your test is with MySQL. Their [documentation](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html) doesn't have enough detail for me to tell. – Kevin Christopher Henry Mar 07 '19 at 22:25
  • Oh, in that case I am probably using READ COMMITTED then, because I don't see the isolation level explicitly set in the settings file. – Michael Harvey Mar 08 '19 at 00:42