From: KM <km@xacrasis.netx>
Subject: Re: atomic multi-threaded upsert
References: <4CED72BB.3070905@gmail.com>
User-Agent: slrn/pre1.0.0-18 (Linux)
Message-ID: <4cedc5f0$0$22107$742ec2ed@news.sonic.net>
Date: 25 Nov 2010 02:12:00 GMT
Lines: 34
Organization: Sonic.Net
To: pgsql-sql@postgresql.org

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.

-- 
KM