Re: How to create "auto-increment" field WITHOUT a sequence object?

On 3 Jul 2011, at 16:10, Dmitriy Igrishin wrote:

> "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.

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.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:1257,4e109f6512095013212184!