How to deal with duplicate IDs in a multi-user application without table-level usage?

Question

PHP

Imagine a banal relational structure with integer identifiers that do not carry a semantic load. Well, you need to write a web application with this database that works (including adding new elements). You cannot use auto-increment at the table definition level. The application is multi-user, so if you just request the maximum ID, the situation with it can change faster than you can send an insert request based on this information. What are the options here? We do not use any libraries like ORM / Persistance.
The PHP language is conditionally chosen as the most understandable to everyone, in principle, any other is possible, the main thing is that the whole logic is visible from the answer (and not just "we take such and such a library and it will take care of everything by itself") and it can be implemented without problems on , roughly speaking, any language.

Answer 1

Why ask for the maximum id? Make the field AUTOINCREMENT and it will increment itself without conflicts.

Answer 2

Use
From Documentation :
The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values ​​of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
So the requests of other clients will not affect the result in any way.

Answer 3

The option in the forehead is to make your own table for storing current counters.
In the transaction UPDATE ids SET id = id + 1, SELECT ids.id
And the resulting id is used for insertion.
Or use to store redis counters.

Answer 4

if you just request the maximum ID, the situation with it can change faster than you can send an insert request based on this information. What are the options here?

one transaction, and they are consecutive.

Answer 5

Use UUID/GUID, just for this purpose