Unlocking a locked a Locked dataset

[sasthebest]

I am appending  data to a dataset in a Unix environment. However, someone opened the table and forget to close  it there by locking the table,. Any idea how to unlock the dataset?

[Patrick]

The only way to unlock the table is to kill the (SAS-) process locking the table - or to ask the user locking the table to close it.

This is unfortunately a common problem with EG users. Newer versions of EG have under "Tools/options/Data/Performance" a setting "Close data grid after period of inactivity". I believe the default is set to 30 minutes.

If you need to kill the process then Google with keywords like "unix process locking file" and you will find syntax to determine which process locks the table. You then still need the privileges to kill the process. I believe the command is "kill -9 <pid>".

[Paul_Dutton]

Hi,

I have a solution for this.

1) check for the .lck file for the sas dataset, if it exists then it is currently being updated so do not try and unlock it.

2) if no .lck file exists, you can OS move(rename) the dataset file(s) including indexes and then copy them back to their original names, once the copy is complete you can remove the renamed copies.   This method is qiute IO intensive but always releases the lock and retains the data.  If the target table is to be replaced, then you can simply delete the OS dataset files to break the lock.

I have written macros to try locking the table, if that fails then it either OS deletes or OS Copies (based on selected option) to break the lock.

Please conatct me if you would like my macro..

[busrafenerci]

Can you share your macro with me ?

Thanks,

[Paul_Dutton]

You can unlock a locked sas dataset in Unix by either deleting the dataset
files at operating system level, or by renaming the dataset files at OS
level and then copying them back to their original names then delete the
renamed versions.

Be sure not to OS delete or move/copy if a . lck file exists as this means
the dataset is locked for update.

Regards

Paul

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Paul_Dutton]

I'm just typing you a macro into here, no testing has been done on it.

You will need XCMD enabled...

%macro unlock(lib=,dataset=);

/* you may want to check for a lock first by unlocking the dataset and trapping the error, then proceed with the following */

%let path=%sysfunc(pathname(&lib.));

%let dset=%lowcase(&dataset.).sas7bdat;

%let index=%lowcase(&dataset.).sas7bndx;

%let lock=%lowcase(&dataset.).sas7bdat.lck;

%if not %sysfunc(fexist(&path./&lock.)) %then %do;

/* lock file does not exist - proceed */

  %if %sysfunc(fexist(&path./&dset.)) %then %do;

  /* dataset file exists - OS unlock */

    filename cmd pipe "mv -v ""&path./&dset."" ""&path./&dset._mv""; cp -v ""&path./&dset._mv"" -v ""&path./&dset.""; rm ""&path./&dset._mv""";

    data _null_;

      infile cmd;

      input;

      put _infile_;

    run;

  %end;

  %if %sysfunc(fexist(&path./&index.)) %then %do;

  /* index file exists - OS unlock */

    filename cmd pipe "mv -v ""&path./&index."" ""&path./&index._mv""; cp -v ""&path./&index._mv"" -v ""&path./&index.""; rm ""&path./&index._mv""";

    data _null_;

      infile cmd;

      input;

      put _infile_;

    run;

  %end;

%end;

%else %put %upcase(error): &lib..&dataset. Lock file exists - dataset being updated aborting unlock procedure.;

%mend;

/* Usage

%unlock(lib=LIBREF,dataset=SOME_DATASET);

This only works on SAS libraries with a single file path.

*/

[PopCorn14]

@Paul_Dutton ,

Thank you for your code.  

I have a process that copies a dataset ; if the dataset already exists and I can’t acquire a lock, I just simply do a FDELETE  (because it is ok in our case) which allows for me to continue my copy because we use LINUX.  Therefore i am freeing the dataset.

But unfortunately with Windows, if a dataset is opened or locked we can’t use any of the cmd listed in the pipe line that you provided.

I was able to use your code by using the « DOS » commands with the Pipe if the dataset isn’t locked ….I learned something with pipes.  Thank you.
With Linux, if I have a locked dataset, I can easily do a fdelete, and then I can copy the dataset.