> "you MUST lock on insert to get gapless sequences" > Not me :-). The OP must do it. So, what problem here? Deadlocks? > Again, if deadlocks are so dangerous, why the LOCK command exists?
It's not deadlocks, it's concurrent updates that are the trouble. If you don't lock, you run the risk for two records being assigned the same number concurrently.
Thanks for clarify, but I know why resources must be locked when they are used concurrently :-). See my previous post about SELECT FOR UPDATE ... and I don't see the problem with it. As well as with the LOCK command.
With a unique constraint added into the mix (and there should be one) that means that one of the transactions will fail the unique constraint check on commit.
It's possible to catch that in the client and redo the transaction with a new ID, but if that's not acceptable (for example because it matters which transaction got the ID first) then you need to lock records.
Sure.
Alban Hertroys
-- If you can't see the forest for the trees, cut the trees and you'll see there is no forest.
