On 2010-11-24, "Mikhail V. Puzanov" <misha.puzanov@gmail.com> wrote: > Next, I execute the following two queries for that table: > > -- increment and get the counter if exists > UPDATE sequences SET counter = counter + 1 > WHERE section = 'testSection' AND name = 'testKey' > RETURNING counter; > > -- insert new counter if not exists > INSERT INTO sequences ( section, name, counter ) > SELECT 'testSection', 'testKey', 0 > WHERE NOT EXISTS ( > SELECT * FROM sequences > WHERE section = 'testSection' AND name = 'testKey' > );
> ERROR: duplicate key value violates unique constraint "idx_sequences_sn" > > 1) That looks weird actually, should the INSERT ... NOT EXISTS (...) be > executed > in atomic fashion? Also, AFAIK, UNIQUE INDEX locks rows before > insert (or not?). > Can it be related to JDBC? > Or it's the result of MVCC conflict resolution?
Perhaps -
Thread A UPDATEs, affecting no row. Thread B UPDATEs, affecting no row. Thread A INSERTs one row. Autocommit is on, so it commits the INSERT. Thread B attempts INSERT and fails on the duplicate.
Yeah, but my expectation was that only one INSERT occurs due to WHERE NOT EXISTS clause.
Seems, the task generally needs using pessimistic locks.
