pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedatomic multi-threaded upsert
6+ messages / 4 participants
[nested] [flat]
* atomic multi-threaded upsert
@ 2010-11-24 20:16 Mikhail V. Puzanov <misha.puzanov@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Mikhail V. Puzanov @ 2010-11-24 20:16 UTC (permalink / raw)
To: pgsql-sql
Hi, All
I'm trying to make a kind of upsert for the following table:
CREATE TABLE sequences (
section VARCHAR( 50 ) NOT NULL,
name VARCHAR( 50 ) NOT NULL,
counter BIGINT NOT NULL
);
CREATE UNIQUE INDEX IDX_SEQUENCES_SN ON sequences(section, name);
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'
);
It works OK when executed in single thread.
When such pairs of queries are executed in parallel (3-10 threads,
1 JDBC connection for each thread, with autoCommit set),
occasionally I get the following error:
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?
2) Are there some patterns for such kind of task?
Thanks.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: atomic multi-threaded upsert
@ 2010-11-24 21:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mikhail V. Puzanov <misha.puzanov@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2010-11-24 21:21 UTC (permalink / raw)
To: Mikhail V. Puzanov <misha.puzanov@gmail.com>; +Cc: pgsql-sql
"Mikhail V. Puzanov" <misha.puzanov@gmail.com> writes:
> -- increment and get the counter if exists
> UPDATE sequences SET counter = counter + 1
> WHERE section = 'testSection' AND name = 'testKey'
> RETURNING counter;
Seems that what you have here is a bad manual implementation of a
sequence. Why don't you use a real sequence object and nextval()?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: atomic multi-threaded upsert
@ 2010-11-25 02:12 KM <km@xacrasis.netx>
parent: Mikhail V. Puzanov <misha.puzanov@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: KM @ 2010-11-25 02:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: atomic multi-threaded upsert
@ 2010-11-25 06:51 MIkhail Puzanov <misha.puzanov@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: MIkhail Puzanov @ 2010-11-25 06:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
2010/11/25 Tom Lane <tgl@sss.pgh.pa.us>
> "Mikhail V. Puzanov" <misha.puzanov@gmail.com> writes:
> > -- increment and get the counter if exists
> > UPDATE sequences SET counter = counter + 1
> > WHERE section = 'testSection' AND name = 'testKey'
> > RETURNING counter;
>
> Seems that what you have here is a bad manual implementation of a
> sequence. Why don't you use a real sequence object and nextval()?
>
>
>
Well, if I use the real sequence, I'll need unpredictable number of them
and will have to store mapping (section, name)->sequenceName, and
also will have to create those sequences dynamically the similar way.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: atomic multi-threaded upsert
@ 2010-11-25 07:24 MIkhail Puzanov <misha.puzanov@gmail.com>
parent: KM <km@xacrasis.netx>
0 siblings, 0 replies; 6+ messages in thread
From: MIkhail Puzanov @ 2010-11-25 07:24 UTC (permalink / raw)
To: KM <km@xacrasis.netx>; pgsql-sql
2010/11/25 KM <km@xacrasis.netx>
> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: atomic multi-threaded upsert
@ 2010-11-27 15:08 Mikhail V. Puzanov <misha.puzanov@gmail.com>
parent: MIkhail Puzanov <misha.puzanov@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Mikhail V. Puzanov @ 2010-11-27 15:08 UTC (permalink / raw)
To: pgsql-sql
On 25.11.2010 12:51, MIkhail Puzanov wrote:
>
>
> 2010/11/25 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>
>
> "Mikhail V. Puzanov" <misha.puzanov@gmail.com
> <mailto:misha.puzanov@gmail.com>> writes:
> > -- increment and get the counter if exists
> > UPDATE sequences SET counter = counter + 1
> > WHERE section = 'testSection' AND name = 'testKey'
> > RETURNING counter;
>
> Seems that what you have here is a bad manual implementation of a
> sequence. Why don't you use a real sequence object and nextval()?
>
>
>
> Well, if I use the real sequence, I'll need unpredictable number of them
> and will have to store mapping (section, name)->sequenceName, and
> also will have to create those sequences dynamically the similar way.
>
Finally the problem solved with application level locks (that works for
only
one application server, but suits our needs for now).
This also works
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
but we need some more or less cross-DBMS approach.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-11-27 15:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-24 20:16 atomic multi-threaded upsert Mikhail V. Puzanov <misha.puzanov@gmail.com>
2010-11-24 21:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-25 06:51 ` MIkhail Puzanov <misha.puzanov@gmail.com>
2010-11-27 15:08 ` Mikhail V. Puzanov <misha.puzanov@gmail.com>
2010-11-25 02:12 ` KM <km@xacrasis.netx>
2010-11-25 07:24 ` MIkhail Puzanov <misha.puzanov@gmail.com>
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