Re: Duplicate Unique Key constraint error

On Jul 10, 2007, at 13:22 , Harpreet Dhaliwal wrote:

> Transaction 1 started, saw max(dig_id) = 30 and inserted new
> dig_id=31.
> Now the time when Transaction 2 started and read max(dig_id) it was
> still 30
> and by the time it tried to insert 31, 31 was already inserted by
> Transaction 1 and hence the unique key constraint error.
>
> I thought this would be taken care by the database itself by
> locking the
> transactions but now I really don't know how does this locking
> takes place
> in postgres.

Why would the server lock the table? It can't know your intention is
to add one to the number returned and insert. If this is what you
want, you have to lock the table explicitly.

> Please guide me throug to get rid of this problem.

This exact reason is why sequences are often used for primary keys. I
recommend you change your primary key.

Michael Glaesemann
grzm seespotcode net