Re: Simulating sequences
> 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?
Subject
Written By
Posted
Re: Simulating sequences
July 09, 2014 03:13PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.