MySQL MaxID returning stale value after new insert

1

@Rick.A the other variation of much the same thing you suggested is to have a separate table that tracks “id’s” (next to be inserted)

   create table id_tracker(
          tablename varchar(50)
          nextid        integer

then do

   begin transaction

   update table id_tracker set nextid = nextid + 1

   select nextid from id_tracker

   commit transaction

and this returned value from the last select is now the ID to use

the being transaction followed by the update will pause and serialize access to the ID table and things will proceed normally

this style has worked in any ACID compliant db since I first saw it in the early 1990’s when many db’s didnt support things like autoincrementing primary id columns etc

2

Not what I suggested to solve William’s problem of “needing” a Max(ID) (not sure about the reasons, I’m just accepting them), but what you are saying here is one usual way to emulate an AUTO INCREMENT feature present basically in all modern DBs. In MYSQL it is the AUTO_INCREMENT clause of the CREATE TABLE statement.

SQLITE even does not track an auto increment behind the scenes, it uses this exact feature in front of us :grinning_face_with_smiling_eyes: updating and reading a table called sqlite_sequence

PostgreSQL even has an entire set of features dedicated to create and maintaining sequences:

3

It was juts something else you might suggest
Its at least another possibility but it seems that he’s not open to any others and will have issues with MAX_ID in a busy environment for exactly the reasons you’ve already mentioned

4

I’ve mentioned the AUTO_INCREMENT clause there. He’s aware…
Even using an auto increment and sequence generators, people can make bad designs unable to set the proper values in some fields in a multi-user busy environment without proper locks and some serialization of processes. I think that the basis for understanding it, or at least a start point, are there. I’m trying not judging how wrong/right an unknown code is, just explained things, and moved on.