topic Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA in SAS Programming

SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

KAS25 — Thu, 14 May 2020 18:54:33 GMT

User receiving ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

and ERROR: A lock is not available for XXXX.XXXX_SSN.INDEX

The table is created user says everything is not complete. I Thought the below was implying that some else has a lock on the entity. Just trying to get an idea of what to check or verify...

NOTE: SAS threaded sort was used.

NOTE: Table PXXXX.XXXX_SSN created, with 645293 rows and 2 columns.

98 +

99 +

NOTE: PROCEDURE SQL used (Total process time):

real time 14:05.97

user cpu time 6.25 seconds

system cpu time 4.09 seconds

memory 269208.58k

OS Memory 279004.00k

Timestamp 05/04/2020 04:37:46 PM

Step Count 3 Switch Count 0

100 +Proc SQL;

101 +create Index ssn on pcxxx.xxxt_ssn(

102 + ssn

103 +);

ERROR: A lock is not available for PXXX.XXXX_SSN.DATA.

104 +

105 +quit;

Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

DavePrinsloo — Thu, 14 May 2020 21:14:42 GMT

The most common cause is that you are viewing the table in another window. You may also also be looking at a view that includes the table. Since you are probably replacing the entire table, then the open table would be wrong. The trickiest cause is that you has a SAS session (Enterprise Guide, Studio) that has the table open and then your client loses the connection, so that you have a zombie SAS task running on the server. These inactive sessions would normally get cleaned up automatically, so maybe simply waiting may solve your problem

Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

SASKiwi — Thu, 14 May 2020 21:51:36 GMT

@KAS25 - If I'm following your evidence correctly you are successfully creating a SAS table but then trying to add an index to it in the SQL step that follows. If that is the case, then possibly cached or "lazy" writes to disk might be the problem. How about getting the user to manually run the table creation step, wait for a minute, then manually run the index step. If this works, then my theory about the lazy writes might be true.

Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

Kurt_Bremser — Fri, 15 May 2020 05:19:46 GMT

Wait, you have 10 seconds of CPU time, but 14 minutes real time creating the dataset? Either that SQL is written in a most inefficient way, or there are other factors involved.

Does the target library "XXXX" point to a RDBMS, or storage on a network share?

Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

KAS25 — Fri, 15 May 2020 17:36:48 GMT

--Does the target library "XXXX" point to a RDBMS, or storage on a network share?

Yes pulling information from a RDBMS. I just wondering why all of a sudden this is an issue?

Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

Kurt_Bremser — Fri, 15 May 2020 17:44:35 GMT

Database systems are complex and do a lot of not-so-obvious things in the background like automatic indexing or checking the integrity rules.

So even if your transfer is through, there still might be some "cleanup" going on which prevents another operation on the table. I suggest adding a "wait" step before the sort.

Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

Tom — Fri, 15 May 2020 18:11:59 GMT

Is there a reason the two steps are using similarly named datasets in two different libraries?

NOTE: Table PXXXX.XXXX_SSN created, with 645293 rows and 2 columns.

ERROR: A lock is not available for PXXX.XXXX_SSN.DATA.

Or is that just a result of you editing the log text to obscure the libref and/or member name?

Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

KAS25 — Fri, 15 May 2020 19:04:26 GMT

The table is being created as for as in SAS for that datamart. The .DATA I'm thinking but not sure that this is a file accessed from the RDBMS and at the point in time it's trying to be accessed SAS is trying to take a lock. Although I know access=readonly, but my not be holding through the entire script.

Re: SAS ERROR: A lock is not available for XXXX.XXXX_SSN.DATA

Tom — Fri, 15 May 2020 19:16:04 GMT

@KAS25 wrote:

The table is being created as for as in SAS for that datamart. The .DATA I'm thinking but not sure that this is a file accessed from the RDBMS and at the point in time it's trying to be accessed SAS is trying to take a lock. Although I know access=readonly, but my not be holding through the entire script.

I can't figure out what you are tying to say here. If the libref is pointing to a remote database then you cannot use the PROC SQL index command to create an index in that remote database. Do you even need to create the index?