Record Locking Postgres

dlr at futurechalk.com dlr at futurechalk.com
Sun Jul 3 16:51:44 UTC 2022


Hello Das,

Postgresql manages the locking of records automatically. The key to successful management in your program is to do all your work in a transaction that is setup with auto commit. ( this can be done in Omnis on the connection to the database). This ensures minimum locking of records but still be able to read the current data. When the update button is clicked switch the transaction to  manual. This then give you control of when to commit or rollback the transaction based on the success or failure the operation. This also takes out the locks needed for the updated data. Upon success or failure switch your transaction back to auto commit mode to maximize concurrency of users. This technique has worked for me for many years. I use a standard set of routines to manage transactions in Omnis

Look up concurrency control in the postgres online manual it explains a lot about the transaction functioning in postgresql.

You do not need “FOR UPDATE”. 

Understanding transactions is the key to not having locking issue surprises.

Sincerely,


Dan Ridinger
Managing Director




FutureChalk Software Inc.			
20521 92A Avenue						
Langley, BC  V1M 1B7
					
Phone No: 604.723.6837
EMail: dlr at futurechalk.com
www: www.futurechalk.com

> On Jul 3, 2022, at 8:12 AM, Das Goravani <goravanis at gmail.com> wrote:
> 
> (Multi user Javascript Client context)
> 
> With Postgres, I thought to lock records you have to do a SELECT ….FOR UPDATE
> 
> I thought that was the only way to place a lock on the record you selected
> 
> Now another experienced developer has told me that with Postgres you don’t have to lock records, that it handles multi user situations automatically. 
> 
> So which is it, do you need to do "FOR UPDATE" or not?
> 
> And further, if you are editing a record and then you CANCEL, how do you REMOVE the lock you previously placed with FOR UPDATE?
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 



More information about the omnisdev-en mailing list