Record Locking Postgres

Mon Jul 4 14:41:18 UTC 2022

to put a name to what Dan and Rudolph are describing to you… there are two basic record locking strategies.

Optimistic record locking. (my preference)
Pessimistic record locking. (fraught with possible deadlock issues and management of locks)

Optimistic record locking.
===================

you pretend that nobody else is using the system and that people can update at any time with no worries

general approach:
1) read the record and note an edit number.    XX_EDITNUM
2) update the record at some time in the future using the edit num

eg

update MyRecord
set values,
XX_EDITNUM = XX_EDITNUM+1,

where Key=Key and XX_EDITNUM=XX_EDITNUM_OLD
returning XX_EDITNUM,

3) then you have to manage the success or conflict

if XX_EDITNUM comes back as a  number, you were successful on the update

as a null, then you had an update conflict and you have to tell the user somebody else beat you to editing the record.  so you reread and let them see the new values and then let them update with the new values on screen.

Advantages:  far less transactional locks placed on a database.    vastly reduces deadlock if one user updates a dependent record in advance of another.

Note: even in omnis DML, you can implement this strategy to avoid the padlocks.

We’ve used optimistic locking in our application for a couple of decades of more — since basically omnis 5 days and I wouldn’t do it any other way.

some people use ’XX_EDIT_TIMESTAMP’ instead of ‘XX_EDITNUM’  and check if the timestamp chnaged.   if thats your preference, use the server time stamp to set XX_EDIT_TIMESTAMP as in

update MyRecord
set values,
set XX_EDIT_TIMESTAMP= now()
where
where Key=Key and XX_EDIT_TIMESTAMP = XX_EDIT_TIMESTAMP_OLD
returning XX_EDIT_TIMESTAMP

pessimistic record locking
===================

lock the record before event thinking of doing an update.  (use the select for update sql option)

causes records to be locked before even reading them and you have to manage that.

generall, creates a poorer interface because you freeze people out of the interface when two people go after the same record.

even in omnis DML, this created the dreaded ‘lock’ icon if two people tried to access the same record .    and it might make one person wait for months if the other person leaves that record open for update.

here’s some reference material on the subject and I’m sure oyu can find more

https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking <https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking>
https://vladmihalcea.com/optimistic-vs-pessimistic-locking/

final notes:

 you can, in postgres use things like advisory locks to notate a record as being desired by one person.     then you can put the other person screen into a read only mode, if you want.

would I do this as a general principle:   no.    but you can if you need to.

and, in postgres, if a batch process is updating some fields that a user cannot see on screen, you can build your application to easily let two people update the same record at the same time with optimistic locking.

on screen, you would always use the XX_EDITNUM appraoch.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Jul 3, 2022, at 1:53 PM, dlr at futurechalk.com wrote:
> 
> Hello Das,
> 
> I use an object to do all my transaction management which does work in both desktop and java script forms. As Rudolf mentioned you have to have a balance. I have chosen that all transactions are done in the lease restrictive way by using auto commit transactions until it is time to update and execute the commands under a manual transaction. In this manual transaction I use either the timestamp or and update count to determine if the data has been changed since I last read the record. If the result is that the timestamp has changed I give the user a message line ‘Data has changed please try again’ and at that point rollback and switch back to auto commit mode again.
> 
> I have had to adjust workflow when the transactions (Unit of Work) has been very complex and the locks were being held to long.
> 
> This topic is very complex and takes awhile to really understand how ACID compliance works in each database. Each database also has different ways of maximizing concurrency.
> 
> Sincerely,
> Dan Ridinger
> 
>> On Jul 3, 2022, at 10:28 AM, Das Goravani <goravanis at gmail.com> wrote:
>> 
>> 
>> 
>>> On Jul 3, 2022, at 12:51 PM, dlr at futurechalk.com wrote:
>>> 
>>> I use a standard set of routines to manage transactions in Omnis
>> 
>> Dear Dan,
>> 
>> When converting my astrology app to SQL, I made a set of routines that mimicked the Omnis data handling commands for the Datafiles. This worked out very well. I put the routines into the Table Superclass. I call an object that has the beginnings of the methods inside it.. here I would define the list from a SQL class.. this connected a Table Class which has the rest of the methods in it. I then would transfer from the Object to the Table class to finish the method. This may have been unnecessarily complex. I might have done just as well doing the whole method in the object. Since the Javascript Client only allows instance variables in fields on the form, or columns from an instance variable row, so your data is really locked in the form.. I’m not sure at this point if I could move the data from the form to say a Task Var Row so that the object containing centralized method would have access to the data being saved. I digress a bit here. Since your data with forms is locked in the form in instance variables, before calling a method in an object which attempts to save that data I envision one would have to copy the data to a Task Variable row that the object then has access to. Does this sound reasonable?
>> 
>> Where do you put your centralized methods? In an object, a Table, or where?
>> 
>> Can you use standard Objects when working the Javascript Client? (Said another way, do Remote Objects mean that standard objects cannot be used with the Javascript Client?) I’m assuming you CAN use normal Objects when working with Remote Forms.
>> 
>> What do your calls to your centralized data handling methods look like?
>> 
>> Is it:
>> 
>> Do MyTaskVarObject.$MethodName(param1, param2…)
>> 
>> Thanks
>> 
>> Das
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com