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

[sathya66]

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
);

[Kurt_Bremser]

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.

[sathya66]

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.

[Quentin]

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.

[Tom]

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.

[sathya66]

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

[yabwon · Accepted solution]

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
)

[yabwon]

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

Interesting approach to locking.

Bart

[Patrick]

@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.