Record Locking Postgres

[Das Goravani]

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