2010/11/25 KM <k...@xacrasis.netx> > On 2010-11-24, "Mikhail V. Puzanov" <misha.puza...@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.
