Transactional isolation level needed for safely incrementing ids
- by Knut Arne Vedaa
I'm writing a small piece of software that is to insert records into a database used by a commercial application. The unique primary keys (ids) in the relevant table(s) are sequential, but does not seem to be set to "auto increment". Thus, I assume, I will have to find the largest id, increment it and use that value for the record I'm inserting.
In pseudo-code for brevity:
id = select max(id) from some_table
id++
insert into some_table values(id, othervalues...)
Now, if another thread started the same transaction before the first one finished its insert, you would get two identical ids and a failure when trying to insert the last one. You could check for that failure and retry, but a simpler solution might be setting an isolation level on the transaction. For this, would I need SERIALIZABLE or a lower level?
Additionally, is this, generally, a sound way of solving the problem? Are the any other ways of doing it?
