10-08-2013 09:08 PM
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?
10-08-2013 10:42 PM
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>".
05-06-2016 05:46 AM - edited 05-06-2016 05:48 AM
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..
04-19-2017 11:38 AM
04-19-2017 01:44 PM
I'm just typing you a macro into here, no testing has been done on it.
You will need XCMD enabled...
/* you may want to check for a lock first by unlocking the dataset and trapping the error, then proceed with the following */
%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""";
%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""";
%else %put %upcase(error): &lib..&dataset. Lock file exists - dataset being updated aborting unlock procedure.;
This only works on SAS libraries with a single file path.