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

[KAS25]

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;

I

[DavePrinsloo]

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

[SASKiwi]

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

[Kurt_Bremser]

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?

[KAS25]

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

[Kurt_Bremser]

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. 

[Tom]

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?

[KAS25]

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.

[Tom]

---

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