You are correct. Trying to do this at all - even on the database side creates a race condition.
If you’re going to do this, you need to lock the table to perform the operation.
This topic has been discussed here a couple times before, I think you can find the discussions searching for “table lock”. In fact, one of the threads was a discussion between us about 20 months ago - F() expression on update // using Max() aggregate -- race condition