Error: A lock is not available for a table.

[wutao9999]

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.

[Quentin]

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.

[wutao9999]

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?

[LinusH]

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

[wutao9999]

I got the error after the insert into statement.

[jakarman · Accepted solution]

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.

[wutao9999]

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.

[SASKiwi]

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;