Resolved Question on transaction

Hi everyone,

I, on the usefull advice of @TomBascom , use the "DO FOR myTable TRANSACTION" every time I need to create, update or delete a row in tables.

But in one of my app I have an issue.
My code is write I follow:

MyTable1 will be the head of a buying request
MyTable2 will be the line of the buying request

Code:
 My button choose trigger for create rows in many tables 

Do some verification before starting the create procedure

RUN Procedure for create myTable1 row(input some data, output ROWID(myTAble1) ). 

RUN Procedure for create myTable2 row (input some data, ROWID(myTAble1)).

MESSAGE "CREATE OK"
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.

I'm agree that the message box a the end was foolish of me.

My question is, inside of each procedure I wrote every creation transaction with "DO FOR .... TRANSACTION" but when I'm coming back to my triggers I still have locks on the different tables ? Did I miss something like a release statement or something ?


Thanks you in advance as always,

- BobyIsProgress -
 

TomBascom

Curmudgeon
You are not showing the code that actually does the work. But the fact that you claim to still have locks tells me that you have not correctly implemented my advice. Show the real code and we can probably tell you where you have left the righteous path.

And, no, RELEASE is not the solution.
 

TomBascom

Curmudgeon
Your code is infested with app builder pre-processor cruft, include files, upper case keywords and FIND FIRST. My eyes are bleeding!

What does "when I'm coming back to my triggers" mean?

What is the evidence that you still have active locks?

Have you compiled this code with the XREF option and checked that buffer and transaction scopes are correct?

I see that "PROCEDURE 25-prc-createDAWFLW" is not following the "DO FOR buffer TRANSACTION" rule. I do not know your db schema but I do not see a temp-table definition for that table so it seems likely that it is a real table. And, as written, it looks to me like it is a pretty good candidate to be causing problems. Especially if any of those include files reference it.
 
Hi @TomBascom ,

All the include part is from our ERP editor. I use a Window template file from them. This template give directly the variable c-stecod : code of the plants running the ERP (he can be multi-plant) ; c-uticod : that is the current user logged in this session.

I do not have evidence right in my code. Maybe the fact that I create DAWFLW line without the "DO FOR buffer TRANSACTION" can be a caused of my lock issue.

In my last version of this app. At the end of my big "CREATE" button triggers I had a message function to put on the user screen that the buying request was successfully created and the number of is request. One of my user after hitting the button was going away and because he let the popup alone all the table that I was working with was locked SACDPE, SACDPR, VAFDAI, DAWFLW....

I was thinking that because I use the "DO FOR buffer TRANSACTION", after the end of this transaction my record was on a no-lock state ? Maybe that is my main issue ??

I did not compiled this one with Xref I will try it. But for now I'm only able to find if My query is working with the right index .... no more . I still lack of knowledge on this. And so it can be an issue of transaction scope.

What does "when I'm coming back to my triggers" mean?
It means that after going through each step my code end with the "CHOOSE" triggers of the button and at this point I still had locks like said before.
To minized the impact of my issue (because of lack of knowledge obviously) I delete the message that I was showing the user and put an information state into a FILL-IN widget instead. Like that my code do not need a user action to finish.

I know that I write many mistaces in my code but I'm willing to do the right things for me and my users . Thank you again for your time and advice.

I updated a little bit my code with the "DO FOR buffer TRANSACTION" for my creation of DAWFLW line. I will keep you updated with the XREF output.
 

Attachments

  • wxCode.w
    79.5 KB · Views: 1

TomBascom

Curmudgeon
I misspoke about XREF - for transaction and buffer scope you just need a plain old LIST listing. That will show you the code with line numbers and it includes a section at the end showing what block (and line#) each transaction and buffer is scoped to. You want transactions and buffers to be scoped to the same blocks and you want those to be the DO FOR ... TRANSACTION blocks. If you see a transaction scoped to the procedure block that is especially bad.

Bad example:
Code:
./trx1.p                              12/18/2018 11:49:14   PROGRESS(R) Page 1

{} Line Blk
-- ---- ---
      1     find customer exclusive-lock where custNum = 1 no-error.
      2     custNum = 12345.
      3     discount = 9.
      
./trx1.p                              12/18/2018 11:49:14   PROGRESS(R) Page 2
          
     File Name       Line Blk. Type   Tran            Blk. Label
-------------------- ---- ----------- ---- --------------------------------
./trx1.p                0 Procedure   Yes
    Buffers: s2k.Customer

Any time that you see "0 Procedure Yes" in the listing it is a bad, bad state of affairs. (The "s2k.Customer" buffer is also scoped to the procedure block.)

Good example:
Code:
./trx3.p                              12/18/2018 11:49:27   PROGRESS(R) Page 1

{} Line Blk
-- ---- ---
      1     define buffer updCustomer for customer.
      2   
      3   1 do for updcustomer transaction:
      4   1   find updcustomer exclusive-lock where custNum = 1 no-error.
      5   1   custNum = 12345.
      6   1   discount = 9.
      7     end.

./trx3.p                              12/18/2018 11:49:27   PROGRESS(R) Page 2

     File Name       Line Blk. Type   Tran            Blk. Label
-------------------- ---- ----------- ---- --------------------------------
./trx3.p                0 Procedure   No
./trx3.p                3 Do          Yes
    Buffers: s2k.updCustomer

This says that the line 0 procedure block is clean of undesirable transactions and that the DO block on line 3 has a transaction scoped to it along with the buffer "s2k.updCustomer". Which is exactly what you want to see.
 
Last edited:

TomBascom

Curmudgeon
On line 506 you have a FIND statement without a lock specified. This makes it a SHARE-LOCK. There is no TRANSACTION block, no FOR and no "update buffer".

Code:
    /************* BUDGETS DAI : VFGDAI ***************/
    IF cDAtype <> "0":U THEN DO:
        FIND FIRST VFGDAI WHERE VFGDAI.dainum = cDainum .
        IF cDAtype = "1":U      AND cTypCde = "FGS":U THEN ASSIGN
            VFGDAI.cfgc  = VFGDAI.cfgc  + dTTc * dTaux.
        ELSE IF cDAtype = "1":U AND CB-depense:SCREEN-VALUE = "2":U THEN ASSIGN     /* Vignal Composant */
            VFGDAI.cimvc = VFGDAI.cimvc + dTTc * dTaux.
        ELSE IF cDAtype = "1":U AND CB-depense:SCREEN-VALUE = "3":U THEN ASSIGN     /* Vignal Process */
            VFGDAI.cimvp = VFGDAI.cimvp + dTTc * dTaux.
        ELSE IF cDAtype = "1":U AND CB-depense:SCREEN-VALUE = "4":U THEN ASSIGN     /* Client Composant */
            VFGDAI.cimcc = VFGDAI.cimcc + dTTc * dTaux.
        ELSE IF cDAtype = "1":U AND CB-depense:SCREEN-VALUE = "5":U THEN ASSIGN     /* Client Process */
            VFGDAI.cimcp = VFGDAI.cimcp + dTTc * dTaux.
        ELSE IF cDAtype = "2":U AND cTypCde = "FGS":U THEN ASSIGN
            VFGDAI.cfgv  = VFGDAI.cfgv  + dTTc * dTaux.
        ELSE
            VFGDAI.cimvc = VFGDAI.cimvc + dTTc * dTaux.
        FIND FIRST VFGDAI NO-LOCK NO-ERROR .
    END.

The "FIND FIRST VFGDAI NO-LOCK NO-ERROR." at the end of the block does not change the lock status. Your transaction has not completed when you run 25-prc-createDAWFLW so all of the DO FOR ... TRANSACTION stuff within that is running in a sub-transaction. Thus when you return from that procedure all of the locks are still held.

You also reference VFGDAI on line 1632 (within procedure 25-prc-createDAWFLW). (Which is called from the trigger containing the snippet above while there is an active transaction.)

I don't have your database so I cannot get a COMPILE LIST (and I am much too lazy to fake it right now) but I wouldn't be surprised if the "free reference" to VFGDAI is causing the whole thing to be scoped to the procedure block.

There may be other issues -- that's just what I see at the moment.
 
Hi @TomBascom ,

I can't thank you enough for your help. Maybe it was the modification of the VFGDAI table and also the DAWFLW without the "DO FOR buffer TRANSACTION" statement, that was causing my lock issue. With the good correction it's all ok :)

I will continue to work with the XREF and listing file to enhance my work.

Really thank you :)
 
Top