Re: serialization errors when inserting new records

On Sat, 22 Jan 2005, Tino Wildenhain wrote:

> > INSERT INTO test (id, name)
> > SELECT COALESCE(MAX(id)+1, 1), 'name' FROM test
> >
> > Ofcourse this gives problems when two clients are inserting a record at
> > the same time. (duplicate primary keys) But, i can't use a sequence in my
> > application (the pk consists of more than just a sequence)
> >
> > one solution would be to do a 'LOCK TABLE test IN SHARE MODE' before
> > inserting. This solves my problem but i'm not sure if its the
> > best way to deal with this kind of concurrency problems ? Is there a
> > better way ?
>
> Of course. The solution to this problem is inherent to a good database
> and has therefore been done long long ago ;)

I agree that a serial would be better.

But I think there are situations where a serial isn't convenient
Like when you want an primary key which consists of the current
year and an sequence number. Like ('05', 1), ('05', 2), ('05', 3) etc.
With a sequence you must write extra code to reset the sequence every year
and you get into trouble if someone inserts data from the previous year...
A 'MAX(id)+1' is much simpler and cleaner then.

Ralph.