Thread: Concurrency issue under very heay loads

----- Original Message -----

From: Raji Sridar (raji)

To: pgsql-general@postgresql.org ; pgsql-performance@postgresql.org

Sent: Thursday, July 16, 2009 10:29 AM

Subject: [PERFORM] Concurrency issue under very heay loads

Hi,

 

We use a typical counter within a transaction to generate order sequence number and update the next sequence number. This is a simple next counter - nothing fancy about it.  When multiple clients are concurrently accessing this table and updating it, under extermely heavy loads in the system (stress testing), we find that the same order number is being generated for multiple clients. Could this be a bug? Is there a workaround? Please let me know.

 

Thanks

Raji

On Wed, Jul 15, 2009 at 10:59 PM, Raji Sridar (raji)<raji@cisco.com> wrote:
> Hi,
>
> We use a typical counter within a transaction to generate order sequence
> number and update the next sequence number. This is a simple next counter -
> nothing fancy about it.  When multiple clients are concurrently accessing
> this table and updating it, under extermely heavy loads in the system
> (stress testing), we find that the same order number is being generated for
> multiple clients. Could this be a bug? Is there a workaround? Please let me
> know.

As others have said, a serial is a good idea, HOWEVER, if you can't
have gaps in sequences, or each customer needs their own sequence,
then you get to lock the rows / table / etc that you're mucking with
to make sure you don't issue the same id number twice.  It's easy to
test your code by hand by running pieces of it in two different psql
sessions in such a way as to induce the race condition (or not if
you've got it right).

Raji Sridar wrote:
> We use a typical counter within a transaction to generate
> order sequence number and update the next sequence number.
> This is a simple next counter - nothing fancy about it.  When
> multiple clients are concurrently accessing this table and
> updating it, under extermely heavy loads in the system
> (stress testing), we find that the same order number is being
> generated for multiple clients. Could this be a bug? Is there
> a workaround? Please let me know.

Please show us your code!

Yours,
Laurenz Albe

2009/7/16 Raji Sridar (raji) <raji@cisco.com>:
> Hi,
>
> We use a typical counter within a transaction to generate order sequence
> number and update the next sequence number. This is a simple next counter -
> nothing fancy about it.  When multiple clients are concurrently accessing
> this table and updating it, under extermely heavy loads in the system
> (stress testing), we find that the same order number is being generated for
> multiple clients. Could this be a bug? Is there a workaround? Please let me
> know.
>
> Thanks
> Raji

You'll not have this problem if you use serial type.

On Thu, 2009-07-16 at 00:11 -0600, Scott Marlowe wrote:

> As others have said, a serial is a good idea, HOWEVER, if you can't
> have gaps in sequences, or each customer needs their own sequence,
> then you get to lock the rows / table / etc that you're mucking with
> to make sure you don't issue the same id number twice.

These days can't you just UPDATE ... RETURNING the sequence source
table? Or is there some concurrency issue there I'm not seeing? Other
than the awful impact on concurrent insert performance of course, but
you're stuck with that using any gapless sequence.

--
Craig Ringer

>-----Original Message-----
>From: pgsql-performance-owner@postgresql.org
>
>We use a typical counter within a transaction to generate
>order sequence number and update the next sequence number.
>This is a simple next counter - nothing fancy about it.  When
>multiple clients are concurrently accessing this table and
>updating it, under extermely heavy loads in the system (stress
>testing), we find that the same order number is being
>generated for multiple clients. Could this be a bug? Is there
>a workaround? Please let me know.

Are you using "for update" in your select statements?   Are you setting
an appropriate transaction isolation level?

A better way to do this is with a sequence instead.  This is guaranteed
to give you a unique value:
select nextval('address_serial_num_seq');

eric