How do I stop “A lock is not available for 2019-02-15 03:58发布 站内问答 / 前端开发 423 3 2

Question

I consistently get "A lock is not available" errors when running SAS programs. It usually happens if I perform operations on the same dataset multiple times in one program. After researching this error, it's my understanding that this means 2 programs are trying to access the same dataset. In other words, it's similar to trying to open a document that is already in use by someone else or yourself. Here is an example of code that is giving me this error:

data TSTONE.map;
    infile <PATH> delimiter = ',' truncover firstobs=2 dsd TERMSTR=cr LRECL=32760;

    format assessment_edition $45.;
    format ...

    input
    assessment_edition :$45.
    ...
    ;
run;

data tstone.map;
    set tstone.map;
    drop DistrictName ...;
run;

I entered "..." in a few places where I have long lists of fields to import or drop. So, first I'm imported a CSV file, and then performing a data step to overwrite the file dropping some fields I don't need. I should note that sometimes when I run programs like this I do not get any lock errors. No other users are accessing these datasets, they are local to my machine. Also, if I highlight and run the 2 data steps sequentially I get no issues.

Answer 1

EDIT 10/8/2015: The macros in this answer were updated 10/8/2015. Generally better debugging info, added options to terminate SAS nicely regardless of whether running in batch or interactive mode.

EDIT 12/8/2014: The macros in this answer were updated 12/8/2014. If you have a version prior to that you will notice slow lock times if your libname contains hundreds or thousands of datasets. The updated versions below have fixed this issue.

Answer : We have datasets that are updated every so many minutes, and at the same time we need adhoc and scheduled reports to access those datasets. To overcome locking issues we created macros to lock and unlock the tables prior to using them. It's been running without any reported errors for almost a year now (we've ironed out all the bugs we've encountered anyway).

Usage:

Program in session A:

%lock(iDs=sashelp.class);
** READ TABLE;
%unlock(iDs=sashelp.class);

Program in session B:

%lock(iDs=sashelp.class);
** UPDATE TABLE;
%unlock(iDs=sashelp.class);

How it works.... Let's say session B launches first. It will lock the dataset prior to performing the update. Session A wants to use the same table (while B is still updating) it. Before Session A tries to do anything with it, it will check to see if it is locked. If it is locked session A will wait for a certain period of time (default=5mins) before deciding to give up. If session B finishes within the 5 minutes, it will release the lock and session A will lock it and continue none-the-wiser. Session A will unlock it when it is done. There's a bunch of other options you can pass in as necessary to customize how to handle things when a lock can't be attained.

The %lock macro:

/******************************************************************************
** PROGRAM:  MACRO.LOCK.SAS
**             
** DESCRIPTION: LOCKS A SAS TABLE SO THAT YOU CAN DO WHAT IT IS YOU NEED TO DO.
** 
** PARAMETERS: iDS     :             THE TABLE TO TRY AND LOCK.
**             iTIMEOUT:             THE MAXIMUM # OF SECONDS TO WAIT TRYING TO GET THE LOCK.
**             iRETRY  :             HOW OFTEN TO RETRY GETTING THE LOCK (IN SECONDS)
**             iVERBOSE:             WHETHER TO PRINT OUT DEBUGGING INFO
**             iEndQuietlyOnTimeout: EXIT SAS IF THE LOCK FAILS
**             iIgnoreError:         IGNORE ERRORS IF DATASET DOESNT EXIST AND CANT BE LOCKED
**
*******************************************************************************
** VERSION:
** 2.0 ON: 13-NOV-14 BY: RP
**     CHANGED METHOD OF CHECKING WHETHER FILE EXISTS TO USE THE FILEEXIST 
**     FUNCTION.  MUCH BETTERER.
** 2.1 ON: 14-SEP-15 BY: MS
**     ADDED OPTIONAL FLAG TO IGNORE ERRORS WHILE ATTEMPTING A LOCK. MUCH BETTERERER.
** 2.2 ON: 25-SEP-15 BY: RP
**     HANDLED FAILS BETTERERER.  MADE WORDS IN LOG MORE BETTERERER TOO.
******************************************************************************/

%macro lock(iDs=, iTimeOut=600, iRetry=3, iVerbose=1, iEndSasQuietlyOnTimeout=0, iIgnoreError=0);

  %global lock_lock_failed ;
  %local starttime lib mbr physical_filename;

  %let starttime = %sysfunc(datetime());
  %let lock_lock_failed = 1;

  /*
  ** MAKE SURE THE REQUIRED DS IS NOT A VIEW.
  ** TODO. CHANGE THIS TO ACCEPT 1 WORD DATA SET REFERENCES
  */
  %let lib               = %sysfunc(pathname(%sysfunc(scan(&iDs,1))));
  %let mbr               = %sysfunc(scan(&iDs,2));
  %let physical_filename = &lib\&mbr..sas7bdat;  


  %if not %sysfunc(fileexist(&physical_filename)) %then %do;
    %if not &iIgnoreError %then %do;
      %put &err: (MACRO.LOCK.SAS) THE DATASET YOU TRIED TO LOCK DOES NOT EXIST (OR YOU TRIED TO LOCK A VIEW WHICH IS NOT POSSIBLE). ;
      %put &err: (MACRO.LOCK.SAS) THE DATASET NAME WAS &iDs .  EXITING SAS.;
      %stop_sas;
    %end;
    %else %do;
      %put NOTE: (MACRO.LOCK.SAS) THE DATASET YOU TRIED TO LOCK DOES NOT EXIST (OR YOU TRIED TO LOCK A VIEW WHICH IS NOT POSSIBLE). ;
      %put NOTE: (MACRO.LOCK.SAS) CONTINUUING DESPITE FAILURE TO LOCK AS iIgnoreError=&iIgnoreError ;      
    %end;
  %end;
  %else %do;

    %do %until(&lock_lock_failed eq 0 or %sysevalf(%sysfunc(datetime()) gt (&starttime + &iTimeOut)));

      %if &iVerbose %then %do;
        %put trying open ...;
      %end;

      data _null_;
        dsid = 0;
        do until (dsid gt 0 or datetime() gt (&starttime + &iTimeOut));
          dsid = open("&iDs");
          if (dsid eq 0) then do;
            rc = sleep(&iRetry);
          end;
        end;
        if (dsid gt 0) then do;
          rc = close(dsid);
        end;
      run;

      %if &iVerbose %then %do;
        %put trying lock ...;
      %end;

      lock &iDs;

      %if &syslckrc eq 0 %then %do;
        %let lock_lock_failed = 0;
      %end;
      %else %do;
        /*
        ** THIS WILL ONLY HAPPEN WHEN THE DATASET IS BEING VIEWED IN AN INTERACTIVE SESSION.
        ** THE OPEN FUNCTION WILL SAY ITS ABLE TO OPEN THE DATASET BUT THEN THE LOCK FUNCTION
        ** WILL FAIL.  WHEN THIS HAPPENS SLEEP HERE AS WELL SO THAT WE DONT GET THOUSANDS OF LOCK 
        ** ATTEMPTS IN THE LOG FILE.
        */
        %let rc = %sysfunc(sleep(%eval(&iRetry * 5)));
      %end;

      %if &iVerbose %then %do;
        %put syslckrc=&syslckrc;
      %end;

    %end;

    %if &lock_lock_failed %then %do;
      %if &iEndSasQuietlyOnTimeout %then %do;
        %stop_sas;
      %end;
      %put &err: (MACRO.LOCK.SAS) COULD NOT LOCK DATASET BEFORE TIMEOUT (&iTimeOut) OCCURRED.;
    %end;

  %end;

%mend;

The unlock macro:

/******************************************************************************
** PROGRAM:  MACRO.UNLOCK.SAS
**             
** DESCRIPTION: UNLOCKS A SAS TABLE LOCKED WITH MACROS.LOCK.SAS.
** 
** PARAMETERS: iDS     : THE TABLE TO TRY AND UNLOCK.
**
*******************************************************************************
** VERSION:
** 1.0 ON: 07-OCT-11 BY: RP
**     CREATED.
******************************************************************************/

%macro unlock(iDs=);
  /* 
  ** ONLY UNLOCK IF THE LOCK WAS SUCCESSFUL
  */
  %if %symexist(lock_lock_failed) %then %do;
    %if &lock_lock_failed eq 0 %then %do;
      lock &iDs clear;
    %end;
    %else %do;
      %put ATTEMPT TO UNLOCK WAS IGNORED AS ATTEMPT TO LOCK FAILED.;
    %end;
  %end;
  %else %do;
    %put ATTEMPT TO UNLOCK WAS IGNORED AS NO ATTEMPT TO LOCK WAS MADE.;
  %end;
%mend;

These programs also require some other utility macros to exist.

IsDir macro:

/******************************************************************************
** PROGRAM:  CMN_MAC.ISDIR.SAS
**
** DESCRIPTION: DETERMINES IF THE SPECIFIED PATH EXISTS OR NOT.
**              RETURNS: 0 IF THE PATH DOES NOT EXIST OR COULD NOT BE OPENED.
**                       1 IF THE PATH EXISTS AND CAN BE OPENED.
**
** PARAMETERS: iPath: THE FULL PATH TO EXAMINE.  NOTE THAT / AND \ ARE TREATED
**                    THE SAME SO &SASDIR/COMMON/MACROS IS THE SAME AS
**                    &SASDIR\COMMON\MACROS.
**
*******************************************************************************
** VERSION:
** 1.0 ON: 13-JUL-07 BY: RP
**     CREATED.  
** 1.1 ON: 29-APR-10 BY: RP
**     ADDED CLEANUP CODE SO FILES WOULD NOT REMAIN LOCKED.
** 1.2 ON: 15-APR-14 BY: JG
**     ADDED MORE DEBUGGING INFO.
** 1.3 ON: 12-DEC-14 BY: RP
**     CLEANED UP DEBUGGING INFO TO A SINGLE LINE
******************************************************************************/

%macro isDir(iPath=,iQuiet=1);
  %local result dname did rc;

  %let result = 0;
  %let check_file_assign =  %sysfunc(filename(dname,&iPath));

  %put ASSIGNED FILEREF (0=yes, 1=no)? &check_file_assign &iPath;


  %if not &check_file_assign %then %do;

    %let did = %sysfunc(dopen(&dname));

    %if &did %then %do;
      %let result = 1;
    %end;
    %else %if not &iQuiet %then %do;
      %put &err: (ISDIR MACRO).;
      %put %sysfunc(sysmsg());
    %end;

    %let rc = %sysfunc(dclose(&did));

  %end;
  %else %if not &iQuiet %then %do;
    %put &err: (ISDIR MACRO).;
    %put %sysfunc(sysmsg());
  %end;

  &result

%mend;
/*%put %isDir(iPath=&sasdir\commonn\macros);*/
/*%put %isDir(iPath=&sasdir/kxjfdkebnefe);*/
/*%put %isDir(iPath=&sasdir/kxjfdkebnefe, iQuiet=0);*/
/*%put %isDir(iPath=c:\temp);*/

FileList Macro:

/******************************************************************************
** PROGRAM:  MACRO.FILE_LIST.SAS
**
** DESCRIPTION: RETURNS THE LIST OF FILES IN A DIRECTORY SEPERATED BY THE
**              SPECIFIED DELIMITER. RETURNS AN EMPTY STRING IF THE THE 
**              DIRECTORY CAN'T BE READ OR DOES NOT EXIST.
**
** PARAMETERS: iPath: THE FULL PATH TO EXAMINE.  NOTE THAT / AND \ ARE TREATED
**                    THE SAME SO &SASDIR/COMMON/MACROS IS THE SAME AS
**                    &SASDIR\COMMON\MACROS. WORKS WITH BOTH UNIX AND WINDOWS.
**
*******************************************************************************
** VERSION:
** 1.0 ON: 17-JUL-07 BY: RP
**     CREATED.  
** 1.1 ON: 29-APR-10 BY: RP
**     ADDED CLEANUP CODE SO FILES WOULD NOT REMAIN LOCKED.
**     FIXED ERROR OCCURRING WHEN IPATH DID NOT EXIST.
** 1.2 ON: 18-AUG-10 BY: RP
**     CATERED FOR MACRO CHARS IN FILENAMES
** 1.3 ON: 14-APR-14 BY: RP&JG
**     ADDED MORE DEBUGGING INFO TO CHECK PATH
** 1.4 ON: 13-NOV-14 BY: RP
**     CHANGED PROGRAM FLOW TO MAKE IT BOTH EASIER TO READ AND TO 
**     REDUCE UNNECESSARY CHECKS / IMPROVE PERFORMANCE.
******************************************************************************/
/*
** TODO. THERES ABOUT 100 WAYS THIS COULD BE IMPROVED.  DO IT SOMETIME.
** SIMPLY IF STATEMENTS FOR FILTERS.
*/
%macro file_list(iPath=, iFilter=, iFiles_only=0, iDelimiter=|);
  %local result did dname cnt num_members filename rc check_dir_exist check_file_assign;

  %let result=;

  %let check_dir_exist = %isDir(iPath=&iPath);
  %let check_file_assign = %sysfunc(filename(dname,&iPath));

  %put The desired path:  &iPath;
  %if &check_dir_exist and not &check_file_assign %then %do;

    %let did = %sysfunc(dopen(&dname));
    %let num_members = %sysfunc(dnum(&did));

    %do cnt=1 %to &num_members;

      %let filename = %qsysfunc(dread(&did,&cnt));
      %if "&filename" ne "" %then %do;

        %if "&iFilter" ne "" %then %do;
          %if %index(%lowcase(&filename),%lowcase(&iFilter)) eq 0 %then %do;
            %goto next;
          %end;
        %end;

        %if &iFiles_only %then %do;
          %if %isDir(iPath=%nrbquote(&iPath/&filename)) %then %do;
            %goto next;
          %end;
        %end;

        %let result = &result%str(&iDelimiter)&filename;

        %next:

      %end;
      %else %do;
        %put ERROR: (CMN_MAC.FILE_LIST) FILE CANNOT BE READ.;
        %put %sysfunc(sysmsg());
      %end;
    %end;

    %let rc = %sysfunc(dclose(&did));

  %end;
  %else %do;

    %put ERROR: (CMN_MAC.FILE_LIST) PATH DOES NOT EXIST OR CANNOT BE OPENED.;
    %put %sysfunc(sysmsg());

    %put DIRECTORY EXISTS (1-yes, 0-no)?  &check_dir_exist;
    %put ASSIGN FILEREF SUCCESSFUL (0-yes, 1-no)?  &check_file_assign;

  %end;

  /*
  ** RETURN THE RESULT.  TRIM THE LEADING DELIMITER OFF THE FRONT OF THE RESULTS.
  */
  %if "&result" ne "" %then %do;
    %qsubstr(%nrbquote(&result),2)
  %end;

%mend; 
/*%put %file_list(iPath=e:\blah\);*/
/*%put %file_list(iPath=e:\SASDev);*/
/*%put %file_list(iPath=e:\SASDev\,iFiles_only=1);*/
/*%put %file_list(iPath=e:\sasdev\,iFiles_only=1,iFilter=auto);*/

The stop_sas macro:

/******************************************************************************
** PROGRAM:  MACRO.STOP_SAS.SAS
**             
** DESCRIPTION: SIMPLE MACRO TO UNCONDITIONALLY END THE CURRENTLY RUNNING SAS CODE
** 
** PARAMETERS: NONEE
**
*******************************************************************************
** VERSION:
** 1.0 ON: 25-SEP-15 BY: RP
**     CREATED
** 1.1 ON: 05-OCT-15 BY: RP
**     FIXED TYPO TO AVOID WARNING MESSAGE
******************************************************************************/

%macro stop_sas;
  %if "&sysenv" eq "FORE" %then %do;
    %abort cancel;
  %end;
  %else %do;
    endsas;
  %end;
%mend;

Answer 2

I have been having the same problem when I run proc append in a %do loop like so:

%do i=1996 %to 2013;
proc append base=inscat.pc_all data = pc&i. force;
run; quit;
%end;

This was on my local machine, so there was nobody else attempting to access the dataset. What was happening was that the loop was running so fast that the base file hadn't closed before it started writing the next part of the loop. After a lot of hair pulling and trying MANY complicated solutions, it turns out that you can just extend the time SAS waits before declaring a failed lock. You do this when you create the library:

libname inscat 'C:\Users\...\insurercat\data' filelockwait=5;

That just extends the wait time to 5 seconds before SAS decides it's a lock error (from the default of 0, I believe). This simple option fixed all the lock problems I had.

Answer 3

First off, lock errors may simply be issues with timing. The server may not have unlocked the file from the earlier import. Workaround there is to not reuse the dataset name; if you need to do some temporary work, then import the file into a temporary (work.) dataset and then set that dataset into a tstone. dataset. I find lock errors more likely on a server (using NAS in particular) because there are more delays possible there; on a local disk the delays in file locking are usually so short as to not occur (but are not impossible).

Second, DROP (like KEEP and RENAME) can be done in data set options, almost anywhere OUT= or SET or anything similar exists. For example, you could do

proc import file=<whatever> out=tstone.map(drop=... list of drops...);
run;

There's no reason to force SAS to reprocess the entire dataset just to drop a few variables.