#CT is Negative

Bastiaan Olij bastiaan at basenlily.me
Wed Feb 22 16:28:54 EST 2017


Hey Doug,

Thats what I always thought too but either it was broken in a version of
Postgres at some point or it doesn't exactly work like that. The update
statement behind the scenes is a select and then change and I've
witnessed a few times in our database that even within a transaction,
two session doing simultaneously would select the same value and update
the record to the same new value instead of taking turns.

As far as I am aware this is why in 9.1 they added the serializable
transaction mode that ensures one happens before the other and is the
only mode that guarantees this works properly.

At least that is my experience...

Cheers,

Bas

On 23/02/2017 3:44 AM, Doug Easterbrook wrote:
> hi bas:
>
> as fas as MVCC, ACID and this statement goes
>
> update CC_Table set next_num=next_num + 1 returning next_num
>
> …. there is ONLY one record, and since is its a single statement, it gets the old ACID treatment and runs in an implicit transaction, so NOBODY ELSE can run against the table twice as the record is locked.
>
> guaranteeing the NEXT number to be unique to each person who asks for it.
>
>
> sequences are one way.   this is another.      this methodology has a distinct advantage — you can ask for a range of unique numbers at a time,   or get a bunch of them at a time, or what have you.
>
>
> eg,
>
> update CC_Table set nexttran= nexttran + 1, nextDep=nextDep+1,   range=range+50 returning nexttran, nextDep, range
>
> the examples are really that great, but the idea is.
>
> to use sequences if you want.      but one thing is for sure.    TWO SESSIONS CANNOT RETURN THE SAME NUMBER.
>
> that because implicit transactions and record locking work perfectly.
>
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
>
>
>
>
> National User Conference, May 8-11, 2017
> https://tickets.proctors.org/TheatreManager/95/online&perf=27956
>
>> On Feb 22, 2017, at 12:41 AM, Bastiaan Olij <bastiaan at basenlily.me> wrote:
>>
>> Hey Doug,
>>
>> Better to use a sequence:
>> https://www.postgresql.org/docs/9.5/static/sql-createsequence.html
>>
>> Does the same thing but ensures MVCC doesn't result in two sessions
>> updating CC_Table.next_num to the same value. Unless you use a
>> sequential transaction but that was only introduced in 9.4.
>>
>> Cheers,
>>
>> Bas
>>
>> On 22/02/2017 10:54 AM, Doug Easterbrook wrote:
>>> hi MIchael:
>>>
>>> we have to use a unique number for our credit card providers…and as Bas points out,    #CT is good enough for 24 days, and isn’t really random -0- and you could run into conflict.
>>>
>>>
>>> we use a unioque sequential number….   and I think you use postgres, so our basic way to grab the next number is
>>>
>>>
>>> update CC_Table set next_num=next_num + 1 returning next_num
>>>
>>>
>>> that way we can never have a conflict on the value of ’next_num' and it keeps on growing.        you can make it numeric (17,0)   which is basically a float 0dp number.
>>>
>>> good enough for a very large number of credit card authorizations.
>>>
>>> hope that helps.
>>
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com


-- 
Kindest Regards,

Bastiaan Olij
e-mail: bastiaan at basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij




More information about the omnisdev-en mailing list