topic Re: Issue with archive file locking in SAS Programming

Issue with archive file locking

imead — Wed, 04 Aug 2021 14:19:14 GMT

I have a report that is running on our SAS server as a kind of an alarm. Every 30 minutes it pulls data in from a database and then compares to an archive file (sas7bdat), emailing if there are new entries that were not in the archive file, then it writes over the archive file with the new entries for the next time the program runs. For some reason at random intervals archive file will lock (sas7.dbat.lck) and then I have to come in and reconstruct the file before it will run again. The code only takes about 45 secs to run on the server, so I don't think it is running over itself.

Why is this happening? Why at random intervals? How can I prevent it?

Re: Issue with archive file locking

ballardw — Wed, 04 Aug 2021 14:27:06 GMT

What does the LOG show related to that data set when it is locked?

Do any other processes use the set?

Re: Issue with archive file locking

imead — Wed, 04 Aug 2021 14:40:05 GMT

Once the file changes from a file format of sas7bdat to sas7bdat.lck, the log shows the following error:

ERROR: File LIBNAME.FILENAME.DATA does not exist.

Nothing else is pointed towards this folder and I just made the report, so nothing else I know would be using the file but this report. It is stored on our shared UNIX network drive, I have read on these forums that sometimes IT virus software can cause issues if they are scanning a file while the server is trying to run it. Is there a workaround for trying to prevent this?

Re: Issue with archive file locking

jimbarbour — Wed, 04 Aug 2021 21:10:29 GMT

@imead wrote:

Once the file changes from a file format of sas7bdat to sas7bdat.lck, the log shows the following error:

ERROR: File LIBNAME.FILENAME.DATA does not exist.

That seems like unusual behavior to me. At least in my installation, the .sas7bdat is not removed until SAS is completely done writing to the.sas7bdat.lck. When completely done, then the .sas7bdat is deleted and the sas7bdat.lck is (presumably) renamed to a .sas7bdat file.

One possibility for remediation might be to use the generations feature of SAS. Depending on how big the file is, you could save multiple generations of the file. When writing to a new sas7bdat.lck, the existing file is not deleted but is renamed to #nnnn where nnnn is a sequential number that iterates by +1 every time a new version is created. I don't think this will solve a (potential) virus scanning software issue, but it's worth a shot. It might get rid of the "does not exist" errors.

Jim

Re: Issue with archive file locking

SASKiwi — Wed, 04 Aug 2021 23:18:25 GMT

@Kurt_Bremser has a utility program that deals with this issue and it may be useful to you so l'm linking him in.

Re: Issue with archive file locking

jimbarbour — Wed, 04 Aug 2021 23:38:52 GMT

No doubt @Kurt_Bremser will have more to say about this, but since it currently is about 0130 in the morning in Germany (I think he lives in Germany), I will post the link to his post here: https://communities.sas.com/t5/SAS-Communities-Library/Avoiding-the-dreaded-quot-Dataset-is-locked-quot-ERROR/ta-p/653210

Jim

Re: Issue with archive file locking

SASKiwi — Thu, 05 Aug 2021 00:07:07 GMT

@jimbarbour - Austria actually but probably same time zone 🙂

Re: Issue with archive file locking

ChrisNZ — Thu, 05 Aug 2021 02:34:06 GMT

> it writes over the archive file with the new entries

It would be faster (unsure about safer, anyone cares to comment?) to append.

Re: Issue with archive file locking

SASKiwi — Thu, 05 Aug 2021 02:40:23 GMT

I think it would help if you posted the code that is intermittently causing this problem.

Re: Issue with archive file locking

jimbarbour — Thu, 05 Aug 2021 02:56:53 GMT

@ChrisNZ,

Proc Append has been super reliable in my experience. It's Update that is really dangerous and can corrupt a dataset.

Jim

Re: Issue with archive file locking

Kurt_Bremser — Thu, 05 Aug 2021 05:51:30 GMT

But APPEND needs write access for the dataset, and if any process has the file open for any reason, you can't get that.

That's why all my production "update" jobs do the concatenation in work, remove the target file with my macro, and then write it.

Care must be taken if more than one job needs to update the same dataset, then you shoul probably look into using a semaphore. I use that for writing to our main jobcontrol dataset that keeps track of all batch jobs.

Re: Issue with archive file locking

Kurt_Bremser — Thu, 05 Aug 2021 05:57:29 GMT

Hi!

Please show us the complete log of the failing step.

Open dataset files usually result in a message that says that write lock is not possible, and contain the number of the/a process getting in your way.

On UNIX, it is possible to remove a directory entry (link) for a file that is in use. My paper from #SASGF 2021 shows a macro to do this. A similar macro is in use in all our production batch jobs.