topic Re: Unlocking a locked a Locked dataset in SAS Programming

Unlocking a locked a Locked dataset

sasthebest — Wed, 09 Oct 2013 01:08:53 GMT

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?

Re: Unlocking a locked a Locked dataset

Patrick — Wed, 09 Oct 2013 02:42:48 GMT

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

Re: Unlocking a locked a Locked dataset

Paul_Dutton — Fri, 06 May 2016 09:48:38 GMT

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

Re: Unlocking a locked a Locked dataset

busrafenerci — Wed, 19 Apr 2017 12:35:36 GMT

Can you share your macro with me ?

Thanks,

Re: Unlocking a locked a Locked dataset

Paul_Dutton — Wed, 19 Apr 2017 15:38:01 GMT

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

Re: Unlocking a locked a Locked dataset

Paul_Dutton — Wed, 19 Apr 2017 17:44:24 GMT

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;

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

Re: Unlocking a locked a Locked dataset

PopCorn14 — Fri, 24 Jun 2022 00:11:04 GMT

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