AW: Record Locking Postgres

[Rudolf Bargholz]

Hi Das,

As with most things in life you have to find a balance between what you would like and what makes sense. On the one hand you have transactions, which are normally used for short lived workflows, like adding an amount to one table and subtracting it from another. The transaction will ensure this will either work, or not. But transactions are normally not used for long lived workflows. You can define a timestamp in a table column, and have a user set the timestamp before starting a process, and if another user tried to access the table with a set timestamp, prevent the other user from accessing the record. You are then responsible for locking the record and have to handle cases where the user leaves their PC for a few hours, or completes the process successfully, or some error occurs in your workflow. If the user leaves their PC on for a few hours, locking a record, you need a process that unlocks the record, and I the user tries to carry on in the workflow, you need to report an error and tell them to start the workflow again. You need to find that golden middle road for your application that prevents deadlocks in the application, when too many transactions are used, or the transactions are long lived, and cases where processes take a long time, and transactions make no sense. At least not long-lived transactions. You can however split up your workflow into steps where specific parts of the workflow are inside a transaction to ensure data integrity.

Triggers on the database can also help keep your data integrity. Triggers run in a transaction, but you need to ensure you do not use them too much and the processes do not take too much time. This can slow down your app in strange ways, but used sensibly can be great.

Sorry, there is no definitive answer here, and really depends on how many users are working in your app, how likely users will step on each other's data/feet, how long your workflows are, how fast your processes are. We lock a customer dossier when the user using a timestamp. If the customer does not do any work in the dossier for a specified time, we unlock the dossier. If the original user wants to access the dossier again, they get an error message, and have to reopen the dossier. For short lived queue processes, we lock only one unlocked job, to ensure that we do not get into a deadlock situation where two users try to lock the same record.

Regards

Rudolf Bargholz

-----Ursprüngliche Nachricht-----
Von: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> Im Auftrag von Das Goravani
Gesendet: Sonntag, 3. Juli 2022 19:12
An: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
Betreff: Re: Record Locking Postgres


Dear Dan,

> On Jul 3, 2022, at 12:51 PM, dlr at futurechalk.com wrote:
> 
> 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.

Are you saying that switching to Manual transactions, and starting a transaction, causes there to be locks on records you select?

I am concerned about customer balance issues. While one workstation is entering new charges say, another may be inputting a payment on the same account. The balance would be recorded wrong at the invoice inputting workstation if the payment entering workstation saved before invoicing was done. Invoicing will update the balance wrongly. I thought therefore that the lock should be on the customer record the whole time invoicing is typing in a new invoice. 

How do I place lock on the customer record while it is being used by a user? 

How do I undo that lock if they Cancel? (I assume that actually updating the record takes off the lock previously placed)

As far as using Transactions set to Manual, I think this is more necessary where you have multiple records being updated and added in one process, such as the saving of a new invoice. You update the customer record with an updated balance, and you save the new invoice, then you save invoice line items as new records to that table. So you have a lot of updating. If the connection went down during this process, you can then rollback all of it. But for single records it doesn’t seem like I need Manual Transactions or is that how you place a lock (as you seemed to imply). 


> 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
> 
> _____________________________________________________________
> 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