Re: Simulating sequences

[Rick James]

> I thought auto-increment only worked on the primary key, thus I would expect it on the 'id' column. Does it work because you put an index on 'id'?

A common misconception. AUTO_INCREMENT needs only the field to be first in some index for it to auto-inc. If the index is not UNIQUE (or PRIMARY), then it is possible to insert a duplicate, but no one ever writes code to deliberately INSERT a duplicate AUTO_INCREMENT value.

> Did you mean to make 'blah' the primary key?

Yes. That makes it optimal to get to the desired row and do the task.

> SET id = LAST_INSERT_ID(id + 1)

That is needed to make the UPDATE atomic -- the SELECT after it to get the id is tied to the session, so does not need to be transactionally tied to the UPDATE.

I really suspect that READ_COMMITTED is the cause of the failure. Why do you need that mode?