Unlocking a locked a Locked dataset

Reply
Occasional Contributor
Posts: 18

Unlocking a locked a Locked dataset

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?

Respected Advisor
Posts: 3,834

Re: Unlocking a locked a Locked dataset

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

New Contributor
Posts: 3

Re: Unlocking a locked a Locked dataset

[ Edited ]

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

Senior User
Posts: 1

Re: Unlocking a locked a Locked dataset

Can you share your macro with me ?

 

Thanks,

New Contributor
Posts: 3

Re: Unlocking a locked a Locked dataset

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. -##
New Contributor
Posts: 3

Re: Unlocking a locked a Locked dataset

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.

*/

 

 

 

 

Ask a Question
Discussion stats
  • 5 replies
  • 4667 views
  • 0 likes
  • 4 in conversation