Smalltalk › Pharo › Pharo Smalltalk Users

RDBMS Atomic Counter?

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

9 messages Options

Esteban A. Maringolo

RDBMS Atomic Counter?

Hi,

A few weeks ago Sven was asking for a lock-free in-image atomic counter.

Today I'm in need of implementing a DB backed (PGSQL) counter for
business forms numbering, and maybe there are some toughts you're
willing to share :)

I can't find any other way than a row lock for each counter, but maybe
there is something better (I avoid locks as much as possible).

Any thoughts to share here?

Regards!

Stephan Eggermont-3

Re: RDBMS Atomic Counter?

You might be much happier with your locks if you grab blocks of
numbers at a time.

Stephan

Sven Van Caekenberghe-2

Re: RDBMS Atomic Counter?

In reply to this post by Esteban A. Maringolo

If you are thinking about an RDBMS like postgresql, why not just use sequences ?

http://www.neilconway.org/docs/sequences/

Especially the INSERT ... RETURNING ... ; is useful.

On 16 Apr 2014, at 21:52, Esteban A. Maringolo <[hidden email]> wrote:

> Hi,
>
> A few weeks ago Sven was asking for a lock-free in-image atomic counter.
>
> Today I'm in need of implementing a DB backed (PGSQL) counter for
> business forms numbering, and maybe there are some toughts you're
> willing to share :)
>
> I can't find any other way than a row lock for each counter, but maybe
> there is something better (I avoid locks as much as possible).
>
> Any thoughts to share here?
>
> Regards!
>

marten

Re: RDBMS Atomic Counter?

In reply to this post by Esteban A. Maringolo

I think this is simply a case of optimistic locking - there is a general
way and very often db specific ways

first you get the current active value ... (one row)

currentValue from "select counterfield from tablename"

then you update the table (single row)

update
tablename
set
counterfield = currentValue + 1
where
counterfield = currentValue

commit

then you look how many rows have been changed (=1) and assume, that
commit was successful and then you know if you were successful ...
otherwise you must retry ...

this is a simple, with worst performance but portale approach ...

Marten

Am 16.04.2014 21:52, schrieb Esteban A. Maringolo:

--
Marten Feldtmann

EstebanLM

Re: RDBMS Atomic Counter?

but I’m with Sven here, you should use sequences (unless there is something that prevents you to do it… ?).

Esteban

On 17 Apr 2014, at 11:50, [hidden email] wrote:

> I think this is simply a case of optimistic locking - there is a general
> way and very often db specific ways
>
> first you get the current active value ... (one row)
>
> currentValue from "select counterfield from tablename"
>
>
> then you update the table (single row)
>
>
> update
> tablename
> set
> counterfield = currentValue + 1
> where
> counterfield = currentValue
>
> commit
>
> then you look how many rows have been changed (=1) and assume, that
> commit was successful and then you know if you were successful ...
> otherwise you must retry ...
>
> this is a simple, with worst performance but portale approach ...
>
> Marten
>
>
> Am 16.04.2014 21:52, schrieb Esteban A. Maringolo:
>> Hi,
>>
>> A few weeks ago Sven was asking for a lock-free in-image atomic counter.
>>
>> Today I'm in need of implementing a DB backed (PGSQL) counter for
>> business forms numbering, and maybe there are some toughts you're
>> willing to share :)
>>
>> I can't find any other way than a row lock for each counter, but maybe
>> there is something better (I avoid locks as much as possible).
>>
>> Any thoughts to share here?
>>
>> Regards!
>>
>
>
> --
> Marten Feldtmann
>

Esteban A. Maringolo

Re: RDBMS Atomic Counter?

2014-04-17 7:21 GMT-03:00 Esteban Lorenzano <[hidden email]>:
> but I’m with Sven here, you should use sequences (unless there is something that prevents you to do it… ?).

I know about sequences and use them for other purposes.

But in this case the sequence is not affected by the transaction, so
if for any reason I have to rollback the creation of a new document,
the number of the sequence will remain ahead.

Marten's proposal, on the other hand, is closer, if not exactly to
what I need. Because it moves the control back to the image, where I
like to be. :)

Think of invoices, I must have a consecutive set of invoices, like
0001, 0002, 0003, there can't be gaps in between.

In the past I did this without considering concurrency, and had only a
few issues in thousands of transactions, but the app wasn't web, nor
receiving input from several clients at the same tame.

Thanks!

Stephan Eggermont-3

Re: RDBMS Atomic Counter?

In reply to this post by Esteban A. Maringolo

>Think of invoices, I must have a consecutive set of invoices, like
>0001, 0002, 0003, there can't be gaps in between.

That is not the rule in most countries, AFAIK.

Stephan

NorbertHartl

Re: RDBMS Atomic Counter?

Am 18.04.2014 um 17:11 schrieb Stephan Eggermont <[hidden email]>:

>> Think of invoices, I must have a consecutive set of invoices, like
>> 0001, 0002, 0003, there can't be gaps in between.
>
> That is not the rule in most countries, AFAIK.
>
In germany it is

Norbert

Sven Van Caekenberghe-2

Re: RDBMS Atomic Counter?

On 18 Apr 2014, at 17:48, Norbert Hartl <[hidden email]> wrote:

>
> Am 18.04.2014 um 17:11 schrieb Stephan Eggermont <[hidden email]>:
>
>>> Think of invoices, I must have a consecutive set of invoices, like
>>> 0001, 0002, 0003, there can't be gaps in between.
>>
>> That is not the rule in most countries, AFAIK.
>>
> In germany it is

In Belgium as well, and I seriously doubt you can do that in the Netherlands, but I could be wrong.