topic Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry in Advanced Programming

code that checks if a lock is available on the table, if not, wait for n seconds and retry

sathya66 — Wed, 16 Jul 2025 10:59:08 GMT

Hi All,

Is there any code that checks if a lock is available on the audit table, if not, wait for n seconds and then retries, and so on.

I tried below code but doesn't look working properly.

%macro try_lock(base_table=, audit_table=, sleep_interval=5, max_retries=12);
 %local i lock_status;

 %let i = 1;
 %let lock_status = 1; /* non-zero means lock failed */

 %do %while(&lock_status ne 0 and &i le &max_retries);

   /* Attempt to lock the base audit table */
   lock &base_table;
   %let lock_status = &syslckrc;

   %if &lock_status ne 0 %then %do;
     %put NOTE: Lock attempt &i failed for &base_table. Sleeping for &sleep_interval seconds...;
     data _null_;
       call sleep(&sleep_interval, 1);
     run;
   %end;

   %let i = %eval(&i + 1);
 %end;

 %if &lock_status = 0 %then %do;
   %put NOTE: Successfully locked &base_table after %eval(&i - 1) attempt(s). Appending &audit_table...;

   proc append base=&base_table data=&audit_table force;
   run;

   unlock &base_table;
 %end;
 %else %do;
   %put ERROR: Unable to acquire lock on &base_table after &max_retries attempts.;
 %end;

%mend try_lock;
%try_lock(
 base_table=audit_master,
 audit_table=audit_temp_01,
 sleep_interval=10,
 max_retries=18
);

Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry

Kurt_Bremser — Wed, 16 Jul 2025 11:03:28 GMT

Why do you think the code does not work properly?

By now (480 posts by you) we really should not need to ask for the log and further information.

Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry

yabwon — Wed, 16 Jul 2025 12:15:19 GMT

There is no such stuff like UNLOCK..., there is LOCK data.set CLEAR;

Maxim 1.

Bart


data work.class;
  set sashelp.class;
  stop;
run;

%macro try_lock(base_table=, audit_table=, sleep_interval=5, max_retries=12);
 %local i lock_status;

 %let i = 1;
 %let lock_status = 1; /* non-zero means lock failed */

 %do %while(&lock_status ne 0 and &i le &max_retries);

   /* Attempt to lock the base audit table */
   lock &base_table;
   %let lock_status = &syslckrc;

   %if &lock_status ne 0 %then %do;
     %put NOTE: Lock attempt &i failed for &base_table. Sleeping for &sleep_interval seconds...;
     data _null_;
       call sleep(&sleep_interval, 1);
     run;
   %end;

   %let i = %eval(&i + 1);
 %end;

 %if &lock_status = 0 %then %do;
   %put NOTE: Successfully locked &base_table after %eval(&i - 1) attempt(s). Appending &audit_table...;

   proc append base=&base_table data=&audit_table force;
   run;

   lock &base_table clear;
 %end;
 %else %do;
   %put ERROR: Unable to acquire lock on &base_table after &max_retries attempts.;
 %end;

%mend try_lock;
%try_lock(
 base_table=work.class,
 audit_table=sashelp.class,
 sleep_interval=10,
 max_retries=18
)

Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry

sathya66 — Wed, 16 Jul 2025 12:15:32 GMT

Sorry,

think I need to use lock &base_table clear; instead of unlock &base_table ;
but if I schedule it via LSF. job is exiting due to below lock ERROR , how can we mask these lock ERRORs only . see the log below

1         %macro try_lock(base_table=, audit_table=, sleep_interval=5, max_retries=12);
32          %local i lock_status;
33         
34          %let i = 1;
35          %let lock_status = 1; /* non-zero means lock failed */
36         
37          %do %while(&lock_status ne 0 and &i le &max_retries);
38         
39            /* Attempt to lock the base audit table */
40         
41            lock &base_table;
42         
43         
44            %let lock_status = &syslckrc;
45         
46            %if &lock_status ne 0 %then %do;
47              %put NOTE: Lock attempt &i failed for &base_table. Sleeping for &sleep_interval seconds...;
48              data _null_;
49                call sleep(&sleep_interval, 1);
50              run;
51            %end;
52         
53            %let i = %eval(&i + 1);
54          %end;
2                                                          The SAS System                       Wednesday, July 16, 2025 01:09:00 PM

55         
56          %if &lock_status = 0 %then %do;
57            %put NOTE: Successfully locked &base_table after %eval(&i - 1) attempt(s). Appending &audit_table...;
58         
59            proc append base=&base_table data=&audit_table force;
60            run;
61         
62         
63            lock &base_table clear;
64         
65         
66          %end;
67          %else %do;
68            %put ERROR: Unable to acquire lock on &base_table after &max_retries attempts.;
69          %end;
70         
71         %mend try_lock;
72         %try_lock(
73          base_table=test.audit_master,
74          audit_table=test.audit_temp_01,
75          sleep_interval=10,
76          max_retries=18
77         );
ERROR: A lock is not available for TEST.AUDIT_MASTER.DATA.
ERROR: Lock held by process 130544.
NOTE: Lock attempt 1 failed for test.audit_master Sleeping for 10 seconds...

NOTE: DATA statement used (Total process time):
      real time           10.00 seconds
      cpu time            0.00 seconds
      

ERROR: A lock is not available for TEST.AUDIT_MASTER.DATA.
ERROR: Lock held by process 130544.
NOTE: Lock attempt 2 failed for test.audit_master Sleeping for 10 seconds...

NOTE: DATA statement used (Total process time):
      real time           10.00 seconds
      cpu time            0.00 seconds
      

NOTE: TEST.AUDIT_MASTER.DATA is now locked for exclusive access by you.
NOTE: Successfully locked test.audit_master after 3 attempt(s). Appending test.audit_temp_01..

NOTE: Appending TEST.AUDIT_TEMP_01 to TEST.AUDIT_MASTER.
NOTE: There were 19 observations read from the data set TEST.AUDIT_TEMP_01.
NOTE: 19 observations added.
NOTE: The data set TEST.AUDIT_MASTER has 152 observations and 5 variables.
NOTE: PROCEDURE APPEND used (Total process time):
      real time           0.07 seconds
      cpu time            0.00 seconds
      

NOTE: TEST.AUDIT_MASTER.DATA is no longer locked by you.

Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry

yabwon — Wed, 16 Jul 2025 12:17:02 GMT

BTW. read this article: https://www.lexjansen.com/wuss/2015/60_Final_Paper_PDF.pdf

Interesting approach to locking.

Bart

Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry

Quentin — Wed, 16 Jul 2025 12:36:34 GMT

Definitely read Troy's paper that yabwon linked to.

Looks like there is also a NOMSG option for the lock statement that could be helpful.

Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry

Tom — Wed, 16 Jul 2025 15:24:24 GMT

What is it that LSF is testing that is causing the job to fail?

Is it actually reading the SAS log and seeing the ERROR: lines? If so then perhaps re-route the SASlog using PROC PRINTTO.

Or is it checking the return code from the SAS process? If so what is the return code and can't you just change that with your SAS code.

Or did it perhaps set the system option ERRORABEND and that is causing the SAS job to stop at the first lock failure? If so then just change the system option before trying to get the lock.

Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry

Patrick — Thu, 17 Jul 2025 00:14:23 GMT

@sathya66 I've tried to use use this %try_lock macro in the past but even after tweaking it got never to a fool-proof solution that only relies on SAS code. In a Unix/Linux environment you could use flock for such purposes.

With SAS what always worked for me both under Windows and Unix/Linux is libname option filelockwait. If you don't need to wait for more than 10 minutes then that's what I'd be going for.

FILELOCKWAIT=n

specifies the number of seconds SAS waits for a locked file to become available to another process.

If the locked file is released before the number of seconds specified by n, then SAS locks the file for the current process and continues. If the file is still locked when the number of seconds has been reached, then SAS writes a locked-file error to the log and the DATA step fails.

The valid values range from 0 to 600. The default value is 0.

Re: code that checks if a lock is available on the table, if not, wait for n seconds and retry

sathya66 — Thu, 17 Jul 2025 06:50:56 GMT

I have added NOMSG option to the lock statement and worked.
lock &base_table NOMSG;