From: KM <km@xacrasis.netx>
To: pgsql-sql@postgresql.org
Subject: Re: atomic multi-threaded upsert
Date: 25 Nov 2010 02:12:00 GMT
Message-ID: <4cedc5f0$0$22107$742ec2ed@news.sonic.net> (raw)
References: <4CED72BB.3070905@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: km@xacrasis.netx
Subject: Re: atomic multi-threaded upsert
In-Reply-To: <4cedc5f0$0$22107$742ec2ed@news.sonic.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox