BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wutao9999
Obsidian | Level 7

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.



1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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

7 REPLIES 7
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
wutao9999
Obsidian | Level 7

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
Tourmaline | Level 20

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

Data never sleeps
wutao9999
Obsidian | Level 7

I got the error after the insert into statement.

jakarman
Barite | Level 11

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 --<-----
wutao9999
Obsidian | Level 7

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
PROC Star

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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