serialization errors when inserting new records

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 ?



Thanks in advance.

Ralph.