ERROR: A lock is not available for STORE.EMPLOYEE.DATA.

Reply
Occasional Contributor
Posts: 19

ERROR: A lock is not available for STORE.EMPLOYEE.DATA.

Hi All,

It would be really great if you suggest me some ideas on the below issue.

While running the SAS code in batch mode i am getting the below error message. The reason is STORE.EMPLOYEE is already opened by another process (i.e SAS EG).

Is there any way to disconnect that process automatically to update the dataset.

ERROR: A lock is not available for STORE.EMPLOYEE.DATA..

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set STORE.EMPLOYEES was only partially opened and will not be saved.

NOTE: PROCEDURE SORT used (Total process time):

      real time           0.06 seconds

      cpu time            0.00 seconds

thanks

Kannan

Super User
Posts: 7,854

Re: ERROR: A lock is not available for STORE.EMPLOYEE.DATA.

Posted in reply to KannanBaskar

Depends on your platform. If you're running SAS on UNIX you can simply rm the file before writing it.

rm does not affect the process that has the file open, it just removes the directory entry (link).

We had that issue repeatedly until I wrote a macro that deducts the physical file name of the SAS data set and then does a x "rm -f filename";.

Don't know if you can do the same on Windows, though.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 19

Re: ERROR: A lock is not available for STORE.EMPLOYEE.DATA.

Posted in reply to KurtBremser

Thanks for your reply KurtBremser,

Yes, I am running on SAS on Unix, Can you please share the Marco which you wrote to deducts the physical file name of SAS dataset. Can you also confirm "rm -f filename" whether it delete the SAS data set or it will disconnect the link.

Thanks,

Kanann B

Super User
Posts: 7,854

Re: ERROR: A lock is not available for STORE.EMPLOYEE.DATA.

Posted in reply to KannanBaskar

I have to delve a little into the inner working of a UNIX FS.

First, disk space is divided into file space, the inode table and the block map. inodes store the actual position of file blocks in the file space and the attributes of the file (owner, access etc)

Directories are just files in the file space.

Files may have several entries in directories pointing to them, called links.

Opening a file first gets the inode number from the directory entry and then opens the inode, which leads to the actual blocks.

Removing a file removes the directory entry and reduces the link count. If link count = 0, then the inode is removed and the blocks are marked available in the block map. If the file is opened by a process at that point, removal of the inode is postponed until the file is closed. Therefore you may see used disk space that has no links.

Because of all this, UNIX allows removal of a file when opened.

Now, here comes the macro:

%macro del_phys(tabname,tabtype); /* does views or data sets */

%local dp_dsid dp_libref dp_filename dp_pathname dp_type dp_suffix dp_rmcmd;

%if "&sysrc" = "0" and "&syscc" = "0"

/* don't do anything if there was a previous error in the batch job, this protects existing files */

%then %do;

  %if (%substr(sysscp,1,3)=WIN)

  %then %let dp_rmcmd=del; /* never tested that */

  %else %let dp_rmcmd=rm -f;

  %if "&tabtype" = "" %then %let tabtype=data;

  %if %sysfunc(exist(&tabname,&tabtype))

  %then %do;

    %let dp_dsid=%sysfunc(open(&tabname));

    %if (&dp_dsid = 0)

    %then %do;

      %put %sysfunc(sysmsg());

    %end;

    %else %do;

      %let dp_type=%sysfunc(attrc(&dp_dsid,MTYPE));

      %if (&dp_type = VIEW)

      %then %let dp_suffix=sas7bvew;

      %else %let dp_suffix=sas7bdat;

      %let dp_libref=%sysfunc(attrc(&dp_dsid,LIB));

      %let dp_filename=%sysfunc(attrc(&dp_dsid,MEM));

      %let dp_filename=%lowcase(&dp_filename);

      %let dp_dsid=%sysfunc(close(&dp_dsid));

      %let dp_pathname=%sysfunc(pathname(&dp_libref,L));

      %let dp_pathname=&dp_pathname./&dp_filename..&dp_suffix;

      X "&dp_rmcmd &dp_pathname";

      %put &dp_rmcmd &dp_pathname; /* for control in the log */

    %end;

  %end;

%end;

%else %do;

  %put "No removal because of SYSRC=&sysrc SYSCC=&syscc";

%end;

%mend del_phys;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 106

Re: ERROR: A lock is not available for STORE.EMPLOYEE.DATA.

Posted in reply to KannanBaskar

hi,

rm -f  will surely delete your data set.

if you dont want to delete the data set, use processid which you must be getting in the log, by which your table is locked.

kill that process to by which your table is locked to get rid of the lock ,

To find the process in unix you can use >ps -ef|grep processid

then you can kill it using "kill processid"

New Contributor
Posts: 2

Re: ERROR: A lock is not available for STORE.EMPLOYEE.DATA.

Posted in reply to KannanBaskar

Good morning to you all.

A long while back (say 1996), I did encounter such a problem ot locked datasets. Most ot the times (all the time LOL), it was an end user that left the dataset open in browse mode on it's left open SAS Session.

If I recall well, the operating system, at the time was Solaris on a Unix box, added a line in the log stating the Process ID (pid) that was responsable for the lock. I wrote a LOG browsing utility that looked for such a lock and then proceeded with a "kill -9 {pid}" with administrator priviledges to remove the session then releasing the lock.

I did automated the process at the time in order to pre-test the availability of the SAS datasets prior to running an end of month or end of day process.

Will have to replicate that process where I'm workning right now. Will post the resulting code as soon as it is done.

Hope the explanation and the idea will help.

Have a nice one Smiley Happy

New Contributor
Posts: 2

Re: ERROR: A lock is not available for STORE.EMPLOYEE.DATA.

Posted in reply to KannanBaskar

As promised, I'm coming to you with a 99% tested solution for Unix environment.

The only part I can't test so far is the shell to the operating system because of security credentials not yet available for me.

This solution will be used in a production environment with a scheduler. The first task is a program and the following macro that scans all the necessary libraries for production work. Since all users should have closed their SAS session when leaving the office, nobody should retain a LOCK on a SAS datasets because they're on viewtable mode. In Unix, SAS will report the ProcessID "LOCKing" the datasets into the log and we can grad this info and proceed to clear the offending SAS session that locks our needed datasets.

The following code was designed with the concept that all the SAS datasets in this library needs to be checked before our overall end of day/week/month process can start. You could easily adpapt it to check for a single SAS dataset. Don't know if this thing could be done in a Windows/Mac/other operating system environment.

Here goes the code as much documented as I could. I'm sure there could be some enhancement that could be made. Feel free to comment back with improvement suggestion.

The only thing not fully tested here is if two users (therefore two distinct ProcessID) are locking the same dataset. My guess is that SAS will report only the first one. I translated this macro that was written in french initialy.

Have fun.

/*
Pseudocode
----------
macro CheckLockOnLibrary
    Assign library
    Load datasets from dictionary.tables
    Capture active WORK directory into a macro variable
    For all SAS datasets listed, check if we could obtain a lock.
        Create a unique log filename
        Redirerect output of following code execution into that log (proc printto)
        Dynamically create a SAS program stored into the WORK directory that will try to lock and then clear that lock
        Execute temporary SAS program previously created
        Close the log
        Analyze the log (isolate ProcessID number)
            if "LOCK" is present
                issue a kill -9 on the ProcessID captured (UNIX environnement)
            End
    Here we go again (we loop)
%mend;
*/

options msglevel = i
        mprint
        spool;

%macro CheckLockOnLibrary(LibraryName, LibraryPath);
    /****************************************/
    /* Local macro variables for processing */
    /****************************************/
    /* Let's get our WORK directory. Stored logs and temporary
       programs will be deleted after session closing */
    %let WorkDir        = %sysfunc(getoption(WORK));
    /* Determine the ERROR message displayed in the log for later processing */
    %let RemoveString   = "ERROR: Lock held by process ";
    /* Initialize ProcessID list to blank */
    %let ProcessID_List = ;
    /* Assign a temporary SAS program */
    %let SAS_Prg        = &WorkDir./_tt.sas;

    /* Assign a filename for our tremporary SAS program that will hold the LOCK statements */
    filename SAS_Prg "&SAS_Prg.";

    /* Assign the library according to the information received */
    %let &LibraryName. = upcase(&LibraryName.);
    libname &LibraryName. &LibraryPath.;
   
    /* Grab all the SAS datasets inside the Library that we want to process from SAS Dictionary Tables */
    /* Macro variable NbTable will contains the number of SAS datasets present in the _Locking dataset */
    proc sql;
        create table work._Locking as
            select  libname, memname
            from    dictionary.tables
            where   upcase(libname) = upcase("&LibraryName.") and
                    memtype         = "DATA";

        select  nobs
        into    :NbTable
        from    dictionary.tables
        where   libname         = "WORK" and
                upcase(memname) = "_Locking";
    quit;

    /* Will loop as long there are SAS datasets to LOCK */
    %do _Counter = 1 %to &NbTable.;
        %let Ok = 1;
        %do %while (&Ok. = 1);
            /* Create a unique log filaneme */
            data _null_;
                _LogName = compress("&WorkDir./L_" || trim(compress(put(datetime(), datetime19.2), ": ")) || ".tmp_log");
                call symput("LogName", _LogName);
            run;
            /* Assign that unique log name to a filename */
            filename Out_Log "&LogName.";

            /* Create a temporary SAS program that will be called to LOCK a SAS datasets */
            data _xx;
                file SAS_Prg;
                set work._Locking (firstobs = &_Counter.
                                   obs      = &_Counter.);
                format x $200.;
                x = "lock " || trim(libname) || "." || trim(memname) || ";";
                put x; output _xx;
                x = "lock " || trim(libname) || "." || trim(memname) || " clear;";
                put x; output _xx;
                call symput("WhichTable", trim(memname));
            run;

            /* Open an output log for further analysis */
            proc printto log = Out_Log new;
            run;

            /* Execute our dynamically created program **/
            %include SAS_Prg /source2 source;

            /* Close the LOG */
            proc printto;
            run;

            /* Load the LOG */
            data work.log1;
                length      _text $ 250;
                format      _text $char250.;
                informat    _text $char250.;
                infile      Out_Log
                            lrecl    = 250
                            encoding = "LATIN1"
                            truncover;
                input      
                    @1 _text $char250.;
            run;

            /* In this step, we isolate only the lines containg the search string,                */
            /* we clean it and store the resulting Unix ProcessID that is locking the SAS dataset */
            data work.log2;
                set work.log1 (where = (_text contains &RemoveString.));
                format ProcessID best12.;
                ProcessID = input(compress(_text, &RemoveString. || "."),12.0);
            run;

            /* We sort the resulting dataset and make sure there is only one occurence of a ProcessID */
            proc sort data = work.log2 nodupkey;
                by ProcessID;
            run;

            /* We create a macro variable into which the ProcessID is(are) stored */
            /* We calculate, at the same time, how many observations we have      */
            proc sql;
                select  ProcessID,
                        count(*)
                into    Smiley TonguerocessID_List separated by " ",
                        :HowMany
                from    work.log2;
            quit;

            %put HowMany        = &HowMany.;
            %put ProcessID_List = &ProcessID_List.;

            %if &HowMany. = 0 %then %let Ok = 0;
            %else %do;
                %let x = %sysfunc(getoption(XCMD));
                %put We now shell into the operating system to clear the LOCK on &WhichTable.;
                %put x = &x.;
                /* x "kill -9 &ProcessID_List."; */
                %let ok = 0; /* Just for the time of testing otherwise eternal looping */
            %end;
        %end;
    %end;
    filename SAS_Prg clear;
%mend;

** Macro calling exemple **;
* %CheckLockOnLibrary(control, "/Whatever/Somewhere/000_Control");

Ask a Question
Discussion stats
  • 6 replies
  • 5897 views
  • 0 likes
  • 4 in conversation