Record Locking Methodology

[Phil (OmnisList)]

Hi Das,

Do you know about SQL transactions?

You start a transaction, update/insert whatever you need to do, if 
something fails, rollback everything, or commit everything...

regards
Phil Potter
Based in Chester in the UK.

On 02/07/2022 21:06, Das Goravani wrote:
> Take for example the entering of a payment when there is an invoice to update and a customer record to update.
>
> You have then two tables that need record locking, the invoice and the customer. The payment record is new so no record locking on that one.
>
> I am thinking that the simplest way to handle it is to fetch the customer and invoices up front in the process… so that you know if you got your locks early on, so that you can just quit the payment saving method early before you have to roll back anything. In other words, check your locks early on, by selecting the records you want to lock with FOR UPDATE as part of the select statement. Then if they are locked the user can just not do that thing right now, wait a moment, and try again. Does that sound reasonable?
>