Re: serialization errors when inserting new records

Am Samstag, den 22.01.2005, 11:14 +0100 schrieb Ralph van Etten:
> Hoi,
>
> I searched the archives but couldn't find an answer to this:
>
> I have a table (simplyfied)
>
> CREATE TABLE test (
> id INT PRIMARY KEY,
> name VARCHAR(250)
> );
>
> I insert records with
>
> 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 ;)

See: http://borg.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-SERIAL

Regards
Tino