Right approach when using transactions and strong scope to conditional update

[juninhopolo]

Imagine the following scenario:
I have a client who only needs to be updated if they meet certain criteria based on their own data. I can only update the record if it meets the criteria (so I only lock the record after the IF statements, when necessary) and the data I was based on cannot have been changed (this is the part I got stuck on).

DEF BUFFER find-customer FOR customer.

DO FOR find-customer.

    FIND FIRST find-customer WHERE find-customer.cust-num = 1 NO-ERROR.

    IF AVAIL find-customer THEN DO.

        //if statement based on the customer data.

        //if statement based on the customer data.

        //if statement based on the customer data.



        //that's why I don't lock the record right up there.



        DEF BUFFER upd-customer FOR customer.

        DO FOR upd-customer TRANS.

            FIND CURRENT find-customer NO-LOCK.

            //I imagine that in here a lot of things can happen (in others sessions), right?

            FIND FIRST upd-customer EXCLUSIVE-LOCK WHERE ROWID(upd-customer) = ROWID(find-customer) NO-ERROR NO-WAIT.

            IF AVAIL find-customer AND NOT CURRENT-CHANGED find-customer AND AVAIL upd-customer THEN DO.

                  //update customer.     

           END.

        END.

    END.

END.

What can I do to ensure that the registry has not been updated and still use separate buffers / strong scope?

 

[Cringer]

Just to make sure I understand - you want to ensure at the point of locking the record in the new buffer that it hasn't been changed by another process?

If so, then you can use the BUFFER-COMPARE keyword to compare the find-customer to the upd-customer and at that point make a decision as to how to proceed.

 

[Patrice Perrot]

Hi
I think there is no update between ( i assume this WHERE find-customer.cust-num instead of WHERE customer.cust-num)

FIND FIRST find-customer WHERE find-customer.cust-num = 1 NO-ERROR.

and

FIND CURRENT find-customer NO-LOCK.

because you use a Shared-lock (no lock option) on find-customer which avoid the Exlusive-lock for other sessions on this record
If the other session could not set an Exclusive-lock on the record, they could not do an Update. on it ...

So update could happen between (in the "DO FOR upd-customer TRANS.")

 FIND CURRENT find-customer NO-LOCK.

and

 FIND FIRST upd-customer EXCLUSIVE-LOCK

There is no treatment between those 2 lines so i will be supprised if another session could lock the record, update it and end its transaction in this time .


Why are you doing the "FIND CURRENT find-customer NO-LOCK." ?

Patrice

 

[juninhopolo]

Just to make sure I understand - you want to ensure at the point of locking the record in the new buffer that it hasn't been changed by another process?

If so, then you can use the BUFFER-COMPARE keyword to compare the find-customer to the upd-customer and at that point make a decision as to how to proceed.

Exactly what I would like. Before posting this question I searched and tested it on "BUFFER-COMPARE source TO target", but I couldn't figure out how to create an example that would work.

From what I've read this function only compares the field types and not the field values. Here's what I have:

DEF TEMP-TABLE ttCustomer LIKE customer.
DEF VAR changed AS LOG NO-UNDO.

//customer.name = 'Josh'.
FIND FIRST customer EXCLUSIVE-LOCK WHERE customer.cust-num = 1.

CREATE ttCustomer.
BUFFER-COPY customer TO ttCustomer.

ASSIGN customer.name = 'James'.

//Dont raises any error.
BUFFER-COMPARE customer TO ttCustomer SAVE RESULT IN changed.

DISPLAY changed. //Display false.

 

[Cringer]

If you define "changed" as a character you should get a list of the changes. I think.

 

[Cringer]

DEFINE VARIABLE cChanges AS CHARACTER NO-UNDO.

DEFINE TEMP-TABLE ttRechnung LIKE Rechnung.

FIND FIRST Rechnung NO-LOCK.

CREATE ttRechnung.
BUFFER-COPY Rechnung EXCEPT Rechnung.RG-DAT TO ttRechnung.

BUFFER-COMPARE Rechnung TO ttRechnung SAVE RESULT IN cChanges.

MESSAGE cChanges
    VIEW-AS ALERT-BOX.

That tells me the field RG-DAT is different.

 

[juninhopolo]

If you define "changed" as a character you should get a list of the changes. I think.

It worked! Rereading the documentation, using a character variable it showed me the changed field. Using a logical variable it returns TRUE when nothing has changed and FALSE when any field has changed (for me this is kind of counterintuitive, but now I got it).

Thx a lot for the help and examples. I'll add the BUFFER-COMPARE in between.

FIND CURRENT find-customer NO-LOCK.
FIND FIRST upd-customer EXCLUSIVE-LOCK WHERE ROWID(upd-customer) = ROWID(find-customer) NO-ERROR NO-WAIT.
IF AVAIL find-customer AND NOT CURRENT-CHANGED find-customer AND AVAIL upd-customer THEN DO
      //BUFFER-COMPARE goes here, checking the variable in SAVE IN and then
     //update customer.     
 END

What do you think?
For the rest of the structure, am i going in the right direction?

 

[Cringer]

Definitely heading the right way. As Patrice said, the chances of there being an update elsewhere are very slim but if you have to account for it then you're ok.

One note though - FIND FIRST should almost never be used. If there really are multiple records that satisfy the FIND then you want to know about it. So you should just FIND, without the FIRST. You can check the AMBIGUOUS flag to see if there are multiple records satisfying the find and error gracefully.

 

[juninhopolo]

Cool, thanks very much to the both of you for the support and tips! It's hard to the find specifics online, but this was very helpful!

 

[juninhopolo]

Hi
I think there is no update between ( i assume this WHERE find-customer.cust-num instead of WHERE customer.cust-num)

FIND FIRST find-customer WHERE find-customer.cust-num = 1 NO-ERROR.

and

FIND CURRENT find-customer NO-LOCK.

because you use a Shared-lock (no lock option) on find-customer which avoid the Exlusive-lock for other sessions on this record
If the other session could not set an Exclusive-lock on the record, they could not do an Update. on it ...

So update could happen between (in the "DO FOR upd-customer TRANS.")

 FIND CURRENT find-customer NO-LOCK.

and

 FIND FIRST upd-customer EXCLUSIVE-LOCK

There is no treatment between those 2 lines so i will be supprised if another session could lock the record, update it and end its transaction in this time .


Why are you doing the "FIND CURRENT find-customer NO-LOCK." ?

Patrice

Sorry for the type.



Since I am doing a NO-LOCK find, other session could still do a EXCLUSIVE-LOCK find and get the record LOCK and then update the record.



There's a very little chance indeed of the record change but I wanted to be sure and the FIND CURRENT is to see if the record data that I am basing on the not change in between.

Thx again to all!