topic Re: Stored Process Error - Lock Not Available in Developers

Stored Process Error - Lock Not Available

cbrotz — Wed, 27 Jun 2018 19:40:17 GMT

I am getting the following error in SAS EG when I run a stored process from a VB Script. If I run it manually, it runs fine. Any ideas on how set this up properly would be appreciated.

ERROR: A lock is not available for TEMP.V_PRM_DATA

Thank you

Re: Stored Process Error - Lock Not Available

SASKiwi — Wed, 27 Jun 2018 21:20:17 GMT

I would compare the definition of the TEMP library between your two modes of operation. Storing this table in your WORK library will prevent this error as each SAS session is allocated a unique WORK library.

Re: Stored Process Error - Lock Not Available

cbrotz — Wed, 27 Jun 2018 21:34:13 GMT

I was originally running this to store in the WORK. Library and could not use the results the next step in the EG session.

The work library does not save evidently:

No, because the work session disappears when the stored process is done. You can write the file to a temp or perhaps rather than a stored process use a %INCLUDE to call the program instead.

The TEMP library works.....sometimes.

Re: Stored Process Error - Lock Not Available

SASKiwi — Wed, 27 Jun 2018 22:17:46 GMT

Try clearing the TEMP libname in your EG session after using it, then reassign it in your Stored Process session. EG sessions run under your user account, Stored Process sessions run under a shared service user account.

You will still get the lock problem if you run two Stored Process sessions at the same time.

Re: Stored Process Error - Lock Not Available

cbrotz — Wed, 27 Jun 2018 23:47:03 GMT

I see code for "libname_all_clear;" Is that what you are referring to?

How do I assign a library? I know we used to use libname statements. Is it something like that?

Thanks,

Re: Stored Process Error - Lock Not Available

FredrikE — Thu, 28 Jun 2018 06:27:08 GMT

The problem is that sas data set can only be written or updated by one user at a time, otherwise you get the lock error message.

One way to solve this is to name the dataset with a time stamp or user name so every dataset is unique.

The easiest is to add the user name as a suffix. I don' remember which macro variable that are automatic in the stored process but one of _METAUSER, SYSUSERID SASUSER should work:

data test&_METAUSER;
a = 10;
run;

//Fredrik

Re: Stored Process Error - Lock Not Available

cbrotz — Thu, 28 Jun 2018 12:56:00 GMT

I am pretty sure I am the only one creating stored processes in our system...I am definitely not running the same stored process from two different SAS EG sessions at the same time. Something else seems like it is not working right. It seems more like once the stored process runs, it does not free up the connection to that file even though it is a temp file. Hours later, it is still locked. The naming idea might work. I will give that a try.

Re: Stored Process Error - Lock Not Available

cbrotz — Fri, 29 Jun 2018 18:31:41 GMT

What was happening is that my stored process to create a file in a SAS EG session was running exactly the same time as the code. I thought the code would "wait" but I guess it does not work that way. I used the following code solution to "sleep" the program. It is not exactly what I was looking for but it will work.

Re: Sleep Question

KevinC

‎02-07-2013 06:27 PM

Look for the CALL SLEEP function instead of trying to guess how long a loop takes.

Data _null_;

call sleep (600,1); /* 600 seconds*/

run;