Question Implementing Transaction Management - Best Way ?

altair

Member
Hi,

I am looking for a method to implements transaction management (as in the DO TRANSACTION block keyword) in many programs.
I need to do this because there some table lock wait issue on some programs.
I have already found some technics to implements the locking, for example this kind of code block :


Code:
/*lock part : */
    r-rowid = ROWID(Table).
    DEFINE BUFFER Table FOR Table.                                                                                                               
    TRANS_LOCK:
    DO TRANSACTION ON ERROR UNDO, RETRY :
         FIND CURRENT Table EXCLUSIVE-LOCK NO-WAIT.
         FIND Table EXCLUSIVE-LOCK WHERE ROWID(Table) = r-rowid NO-WAIT.
         IF NOT AVAILABLE(Table)
         THEN DO:
             IF LOCKED(Table)
             THEN DO:
                 IF ( i-Retries >= i-Max-Nb-Retries-Lock )
                 THEN DO:
                     c-message = c-message + '[ERROR] Attempt To Retry Block [ TRANS_LOCK ] - ' + STRING(i-Max-Nb-Retries-Lock) + ' times - Leave [ TRANS_LOCK ] ' + CHR(13) + CHR(10).
                     UNDO TRANS_LOCK, LEAVE TRANS_LOCK.
                 END.

                 ASSIGN
                     c-message = c-message + '[ERROR] Table Record already locked !' + CHR(13) + CHR(10)
                     i-Retries = i-Retries + 1
                     c-message = c-message + '[WARNING] Table Record Locked - Retry Block [ TRANS_LOCK ] - Wait ' + STRING(de-Max-nb-seconds-waiting-Lock) + ' Sec - Nb Retry : ' + STRING(i-Max-Nb-Retries-Lock) + CHR(13) + CHR(10)
                 .
                 ETIME(YES).
                 DO WHILE(ETIME) < de-Max-nb-seconds-waiting-Lock :
                    READKEY PAUSE(0).
                 END.
                 ETIME(NO).
                 UNDO TRANS_LOCK, RETRY TRANS_LOCK.
             END.
             ELSE DO:
                 c-message = c-message + '[ERROR] Table Record not found !' + CHR(13) + CHR(10).
                 UNDO TRANS_LOCK, RETRY TRANS_LOCK.
             END.
         END. 
/*end lock part.*/ 
  /*DO SOME Table UPDATES*/
/*unlock part : */
         FIND CURRENT Table NO-LOCK NO-WAIT.
    END. /*TRANS_LOCK: DO TRANSACTION*/ 
/*end unlock part.*/

What I want to do here is to put this kind of code (or better, if you have any suggestion :) ) around the code where there are the Table update (typically : ASSIGN Table.field = value).
Basically I have 2 parts : lock and unlock
The thing is that I have many pieces of code where I should add TRANSACTION management.
I don't want to repeat the code itself everywhere (it will complexify the code reading), but want to put some kind of "generic" code call before and after Table update (multiple different tables are concerned by this).

What I have thought about :

- Procedures to achieve this :

Code:
    RUN lock(BUFFER Table)
    ASSIGN Table.field = value
    RUN unlock(BUFFER Table).
=> But it implies to split the DO TRANSACTION block in 2 parts, which is not possible (???)


- Put some preprocessor constant to hold the lock and unlock code :
&GLOBAL-DEFINE lock-{&table} r-rowid = ROWID(Table). ~
DEFINE BUFFER Table FOR Table. ~
TRANS_LOCK: ~
...
In calling code :
Code:
    {&lock-Table}
    ASSIGN Table.field = value
    {&unlock-Table}
But it seems that this is not possible to have preprocessor values on multiples lines and with spaces... each time when my preprocessor definition is executed before it is referenced in the calling, whcih result in errors.



Any suggestions is welcome. :)
For information : Progress 11.5.1, OS : Windows 7 .

Thanks in advance.
 

Cringer

ProgressTalk.com Moderator
Staff member
You should always use a named buffer in your transactions, so

do for buffername transaction:
find buffername exclusive-lock etc...
end.

That way you know exactly what the scope of your buffers is, and it's a lot harder to leak scope. You also want to make your transactions as small as possible.
 
Hi,

I am looking for a method to implements transaction management (as in the DO TRANSACTION block keyword) in many programs.
I need to do this because there some table lock wait issue on some programs.
I have already found some technics to implements the locking, for example this kind of code block :


Code:
/*lock part : */
    r-rowid = ROWID(Table).
    DEFINE BUFFER Table FOR Table.                                                                                                          
    TRANS_LOCK:
    DO TRANSACTION ON ERROR UNDO, RETRY :
         FIND CURRENT Table EXCLUSIVE-LOCK NO-WAIT.
         FIND Table EXCLUSIVE-LOCK WHERE ROWID(Table) = r-rowid NO-WAIT.
         IF NOT AVAILABLE(Table)
         THEN DO:
             IF LOCKED(Table)
             THEN DO:
                 IF ( i-Retries >= i-Max-Nb-Retries-Lock )
                 THEN DO:
                     c-message = c-message + '[ERROR] Attempt To Retry Block [ TRANS_LOCK ] - ' + STRING(i-Max-Nb-Retries-Lock) + ' times - Leave [ TRANS_LOCK ] ' + CHR(13) + CHR(10).
                     UNDO TRANS_LOCK, LEAVE TRANS_LOCK.
                 END.

                 ASSIGN
                     c-message = c-message + '[ERROR] Table Record already locked !' + CHR(13) + CHR(10)
                     i-Retries = i-Retries + 1
                     c-message = c-message + '[WARNING] Table Record Locked - Retry Block [ TRANS_LOCK ] - Wait ' + STRING(de-Max-nb-seconds-waiting-Lock) + ' Sec - Nb Retry : ' + STRING(i-Max-Nb-Retries-Lock) + CHR(13) + CHR(10)
                 .
                 ETIME(YES).
                 DO WHILE(ETIME) < de-Max-nb-seconds-waiting-Lock :
                    READKEY PAUSE(0).
                 END.
                 ETIME(NO).
                 UNDO TRANS_LOCK, RETRY TRANS_LOCK.
             END.
             ELSE DO:
                 c-message = c-message + '[ERROR] Table Record not found !' + CHR(13) + CHR(10).
                 UNDO TRANS_LOCK, RETRY TRANS_LOCK.
             END.
         END.
/*end lock part.*/
  /*DO SOME Table UPDATES*/
/*unlock part : */
         FIND CURRENT Table NO-LOCK NO-WAIT.
    END. /*TRANS_LOCK: DO TRANSACTION*/
/*end unlock part.*/

What I want to do here is to put this kind of code (or better, if you have any suggestion :) ) around the code where there are the Table update (typically : ASSIGN Table.field = value).
Basically I have 2 parts : lock and unlock
The thing is that I have many pieces of code where I should add TRANSACTION management.
I don't want to repeat the code itself everywhere (it will complexify the code reading), but want to put some kind of "generic" code call before and after Table update (multiple different tables are concerned by this).

What I have thought about :

- Procedures to achieve this :

Code:
    RUN lock(BUFFER Table)
    ASSIGN Table.field = value
    RUN unlock(BUFFER Table).
=> But it implies to split the DO TRANSACTION block in 2 parts, which is not possible (???)


- Put some preprocessor constant to hold the lock and unlock code :
&GLOBAL-DEFINE lock-{&table} r-rowid = ROWID(Table). ~
DEFINE BUFFER Table FOR Table. ~
TRANS_LOCK: ~
...
In calling code :
Code:
    {&lock-Table}
    ASSIGN Table.field = value
    {&unlock-Table}
But it seems that this is not possible to have preprocessor values on multiples lines and with spaces... each time when my preprocessor definition is executed before it is referenced in the calling, whcih result in errors.



Any suggestions is welcome. :)
For information : Progress 11.5.1, OS : Windows 7 .

Thanks in advance.

Something like this should work:
{mylockcode.i &table=yourtable
&translocklabel=TRANS_LOCK
&sometableupdates="yourtable.field=value"}
Don't forget to init your vars i-Retries=0, c-message="" etc
 
Last edited:

TomBascom

Curmudgeon
That's fairly elaborate and it lacks the most important element -- explicit strong scope as Cringer suggests and demonstrates.

Pre-processors are a bad idea. You shouldn't use them. They are marginally excusable to manage conditional compilation if you need to support multiple versions of Progress in the same code base but the are a horrible way to achieve code re-use. If you need an endless supply of bad examples and illustrations of doing it wrong you need look no further than the ADM code.

The key to successfully managing locks and transactions is STRONG SCOPE. IOW using "DO FOR buffer TRANSACTION" blocks. It is a simple thing to do and it cures a whole host of problems.
 

altair

Member
Hi,
Thanks for your replies :)
I did not know the "DO FOR buffer TRANSACTION" construct, thanks for that :)
I know that preprocessor constants are not a good idea.
I have not found other alternative than {&lock} and {&unlock} to implements "DO FOR buffer TRANSACTION... END." block without repeating the same 40 lines of code everywhere in my target code base (function or procedure can not be used because can't split the DO...END block in two parts).

In my case, I want to use your suggestion of "DO FOR buffer TRANSACTION... END." and re-use the code in many places without repeating it.
If you have any suggestion to implements it the best way...
 

altair

Member
Finally, I won't use include file to "inject" code block to manage transaction (as per your recommendation, and because it adds noise in code readability ).
Instead of this, I will put everywhere needed the same code lines before any table update which may result in too large transaction scope.

I am implementing this kind of code where needed (I replace <Table> by the actual table name):

Above this, I'll include a code that will manage the locking and the alerting, and which will indicates the record availability.
Code:
mr-rowid = ROWID(<Table>). /*<Table> is in NO-LOCK mode*/
DEFINE BUFFER b_<Table> FOR <Table>.
TRANS_LOCK:
DO FOR b_<Table> TRANSACTION ON ERROR UNDO, LEAVE :
   FIND b_<Table> EXCLUSIVE-LOCK WHERE ROWID(b_<Table>) = mr-rowid NO-WAIT NO-ERROR.
   
   /*update code...*/ 
      ASSIGN  b_<Table>.field   = value .
  /*....update code*/ 

   RELEASE b_<Table>.
END.
/*<Table> is still available*/

I use a different buffer for the Transaction, which is re-positioned on the main buffer table, by using ROWID.
The separation and scoping buffer allow me to not impact the main buffer <Table>, which availability is sometimes required for further reading in NO-LOCK.
 

TomBascom

Curmudgeon
The RELEASE statement is not needed and probably doesn't do what you think it does.

The DEFINE BUFFER statement is probably best positioned at the top of the program block -- it is likely that you would have more than one update block that will use it.

Personally, I like to name the buffer upd_TableName -- this is the proper usage of "Hungarian notation" and is much more meaningful than adding "b_".

There is no harm in adding the TRANS-LOCK: label but you are also never referencing it so it is kind of pointless. And the mental effort of thinking up new names if you have more than one such block could get annoying.

You don't need the mr-rowid variable -- you could just use ROWID( <Table> ) directly.

You have NO-WAIT and NO-ERROR but you are not testing for or handling either condition. This will result in errors potentially being propagated to users.

So in the end my version of your standard update block would look like this:

Code:
define buffer upd_myTable for myTable.

/* ... stuff happens ... */

do for upd_myTable transaction on error undo, leave:

  find upd_myTable exclusive-lock where rowid( upd_myTable ) = rowid( myTable ) no-wait no-error.

  if avaialble( upd_myTable ) then
    assign
      upd_myTable.xxx = zzz
    .
   else if locked( upd_myTable ) then
    . /* do whatever you want to do about locked records -- probably a message to the user or log file... */
   else
    . /* doesn't exist anymore? */

end.
 
Last edited:
Top