From: | MIkhail Puzanov <misha(dot)puzanov(at)gmail(dot)com> |
---|---|
To: | KM <km(at)xacrasis(dot)netx>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: atomic multi-threaded upsert |
Date: | 2010-11-25 07:24:22 |
Message-ID: | AANLkTikBR2Ceqj1pkVeYb0P3emOnzJDoi-3SYqiducHJ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
2010/11/25 KM <km(at)xacrasis(dot)netx>
> On 2010-11-24, "Mikhail V. Puzanov" <misha(dot)puzanov(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Carla | 2010-11-25 11:40:40 | Re: insert from a select |
Previous Message | MIkhail Puzanov | 2010-11-25 06:51:39 | Re: atomic multi-threaded upsert |