Re: Weird insert issue

2015-06-28 6:37 GMT+02:00 Larry Meadors <larry(dot)meadors(at)gmail(dot)com>:

> I'm running this SQL statement:
>
> insert into Favorite (patronId, titleId)
> select 123, 234
> where not exists (
> select 1 from Favorite where patronId = 123 and titleId = 234
> )
>
> It normally runs perfectly, but will rarely fail and I just can't see
> any way that it could. :-|
>
> The exception I get is that the unique key (patronid+titleid) was violated.
>
> Is it possible that the statement is getting run twice and that the
> timing is such that the first one succeeds and the second tries to do
> the insert and fails because the select part of the SQL ran before the
> first insert completed? I'd expected that each of the two would be
> single operations, but this error is making me rethink that.
>

sure - it is expected behave

http://www.postgresql.org/docs/9.4/static/transaction-iso.html

you can protect it against this issue with locking - in this case you can
try "for update" clause

http://www.postgresql.org/docs/9.4/static/explicit-locking.html

insert into Favorite (patronId, titleId)
select 123, 234
where not exists (
select 1 from Favorite where patronId = 123 and titleId = 234 for update
)

Regards

Pavel

>
> Any thoughts?
>
> Larry
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>