Help using Base SAS procedures

Error: A lock is not available for a table.

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Error: A lock is not available for a table.

I have a code that sometimes works and sometimes doesn't work, which bothers me.

Here is the code:

CREATE TABLE sourcing_chart_name10_stad (chart_name char(30)) ;

INSERT INTO sourcing_chart_name10_stad VALUES('ABC_Plant') ;

Here is the error:

ERROR: A lock is not available for WORK.SOURCING_CHART_NAME10_STAD.DATA.

The table is dropped after each run.

The code sometimes works, but doesn't work in other times.  I don't understand why the code does not always work.  What could be the reason?  Thank you for the help.




Accepted Solutions
Solution
‎09-29-2014 02:10 PM
Valued Guide
Posts: 3,208

Re: Error: A lock is not available for a table.

You are using

-  SQL, The language is designed for record retrieval and modification in an OLTP approach

-  proc sql, that procedure is multi-threading, several processes may run in parallel.

   You re not running one process when possible it will start several threads for you.

Updating the same table that is also being retrieved was once possible (8.2) after that it got into an error (9 multi-threading).


As you create the table and immediately wanting an update(lock required) you can have run into timing issues. 

That can be SAS internal or on the OS system whoever verifies the lock is still there.

The syntax is correct an I am convinced it should be handled correctly the timing should be solved internal.

---->-- ja karman --<-----

View solution in original post


All Replies
PROC Star
Posts: 1,231

Re: Error: A lock is not available for a table.

The reason is probably what is described in the error message: some other process has a lock on the dataset.

It's rare to get this error with a  work dataset, however, as typically only one SAS session would write to a work library at a time.

I was able to replicate your error message when I start 2 SAS sessions running on my PC, and have both sessions trying to write to the dataset at the same time.

Perhaps virus scanning or similar could be accidentally locking a file.

HTH,

--Q.

Contributor
Posts: 52

Re: Error: A lock is not available for a table.

The issue is that:  There is only one process in running.  I am sure there is no other people running the code at the same time.

The weird thing is that:  at one time, I run the code, it works.  Then, run another time, it doesn't work, then run another time, it works again.  It seems that the code works 80% times when I run, but fails 20% times when I run it.  Either work or fail does happen randomly.

Is it possible the code "INSERT INTO sourcing_chart_name10_stad VALUES('ABC_Plant') ;" sometimes executes even before when the code "CREATE TABLE sourcing_chart_name10_stad (chart_name char(30)) ;" is completely done?

Super User
Posts: 5,256

Re: Error: A lock is not available for a table.

Do you get the error after the create table or the insert into statement?

Data never sleeps
Contributor
Posts: 52

Re: Error: A lock is not available for a table.

I got the error after the insert into statement.

Solution
‎09-29-2014 02:10 PM
Valued Guide
Posts: 3,208

Re: Error: A lock is not available for a table.

You are using

-  SQL, The language is designed for record retrieval and modification in an OLTP approach

-  proc sql, that procedure is multi-threading, several processes may run in parallel.

   You re not running one process when possible it will start several threads for you.

Updating the same table that is also being retrieved was once possible (8.2) after that it got into an error (9 multi-threading).


As you create the table and immediately wanting an update(lock required) you can have run into timing issues. 

That can be SAS internal or on the OS system whoever verifies the lock is still there.

The syntax is correct an I am convinced it should be handled correctly the timing should be solved internal.

---->-- ja karman --<-----
Contributor
Posts: 52

Re: Error: A lock is not available for a table.

I think you are right.  The code works after I move

CREATE TABLE sourcing_chart_name10_stad (chart_name char(30)) ;

onto the top of all codes.

and later run

INSERT INTO sourcing_chart_name10_stad VALUES('ABC_Plant') ;

It may end up some timing issue if putting these two executions close to each other.

Thank you for the help.

Super User
Posts: 3,102

Re: Error: A lock is not available for a table.

One way to avoid locking would be to use a DATA step instead:

data sourcing_chart_name10_stad;

  attrib chart_name length = $30;

  chart_name = 'ABC_Plant';

  output;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 6864 views
  • 8 likes
  • 5 in conversation