Does this query guarantee me a 'race free' PK value?

Question

I was just reading How to avoid a database race condition when manually incrementing PK of new row.

There was a lot of good suggestions like having a separate table to get the PK values.

So I wonder if a query like this:

INSERT INTO Party VALUES(
    (SELECT MAX(id)+1 FROM 
        (SELECT id FROM Party) as x),
'A-XXXXXXXX-X','Joseph')

could avoid race conditions?

Is the whole statement guaranteed to be atomic? Isn't in mysql? postgresql?

Answer 1

The best way to avoid race conditions while creating primary keys in a relational database is to allow the database to generate the primary keys.

Answer 2

No, you still have a problem, as, if two queries try to increment at the same time there may be a situation where the inner select is done, then another query is processed.

Your best bet, if you want a guarantee, if you don't want the database doing it, is to have a unique key on there.

In the event that there is an error in inserting, then try your query again, and once the primary key is unique it will work.

In this case, your best bet is to first insert only the id and any other non-null columns, and then do an update to set the nullable columns to whatever is correct.

Answer 3

It would work on tables which use table-level locking (MyISAM), but on Innodb etc, it could deadlock or produce duplicate keys, I think, depending on the isolation level in use.

In any case doing this is an extremely bad idea as it won't work well in the general case, but might appear to work during low-concurrency testing. It's a recipe for trouble.

You'd be better off using another table and incrementing a value in there; that's more likely to be race-free / deadlock-free.