topic Re: Lock Dataset but still get lock error in SAS Programming

Lock Dataset but still get lock error

cckesler — Fri, 22 Nov 2019 21:24:33 GMT

I have some code that tries to lock a dataset, and upon successful locking will update said dataset. It generally works, but I just got a message in my production process that it errored because it couldn't get the lock - even when the previous step showed it had a lock. What am I missing? This has been in place for a while and has worked just fine - looping until it could get a lock.

Here is a snippet from that portion of the log:

MLOGIC(UPDATELST): %LET (variable name is J)

SYMBOLGEN: Macro variable J resolves to 0

SYMBOLGEN: Macro variable J resolves to 1

MLOGIC(UPDATELST): %IF condition &j > 1 is FALSE

MLOGIC(UPDATELST): %PUT trying to lock at &time

SYMBOLGEN: Macro variable TIME resolves to 1:13:24

trying to lock at 1:13:24

SYMBOLGEN: Macro variable DSN resolves to sched.status

MPRINT(UPDATELST): lock sched.status;

NOTE: SCHED.STATUS.DATA is now locked for exclusive access by you.

MLOGIC(UPDATELST): %PUT syslckrc = &syslckrc

SYMBOLGEN: Macro variable SYSLCKRC resolves to 0

syslckrc = 0

MLOGIC(UPDATELST): %PUT sysrc=&syscc

SYMBOLGEN: Macro variable SYSCC resolves to 0

sysrc=0

MLOGIC(UPDATELST): %PUT syserr = &syserr

SYMBOLGEN: Macro variable SYSERR resolves to 0

syserr = 0

SYMBOLGEN: Macro variable SYSLCKRC resolves to 0

4 The SAS System 01:13 Wednesday, November 13, 2019

MLOGIC(UPDATELST): %IF condition &syslckrc = 0 is TRUE

SYMBOLGEN: Macro variable DSN resolves to sched.status

MPRINT(UPDATELST): data sched.status;

SYMBOLGEN: Macro variable DSN resolves to sched.status

MPRINT(UPDATELST): set sched.status;

SYMBOLGEN: Macro variable JOBNAME resolves to MARSDS_H_200

MPRINT(UPDATELST): if JobName="MARSDS_H_200" then do;

MPRINT(UPDATELST): LStatus=put(DateTime(),DateTime16.);

MPRINT(UPDATELST): if rnstatus = 'YES' then do;

MPRINT(UPDATELST): Cstatus = 'Pending Approval';

MPRINT(UPDATELST): rnstatus = ' ';

MPRINT(UPDATELST): end;

MPRINT(UPDATELST): else if complstatus='Complete' then CStatus='Complete';

MPRINT(UPDATELST): else CStatus='Pending Approval';

MPRINT(UPDATELST): call symput('status',left(trim(cstatus)));

MPRINT(UPDATELST): call symput('lstatus',left(trim(lstatus)));

MPRINT(UPDATELST): end;

MPRINT(UPDATELST): run;

ERROR: A lock is not available for SCHED.STATUS.DATA.

ERROR: Lock held by process 56791.

NOTE: The SAS System stopped processing this step because of errors.

ERROR: File SCHED.STATUS.DATA is not open.

WARNING: The data set SCHED.STATUS was only partially opened and will not be saved.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

user cpu time 0.01 seconds

system cpu time 0.01 seconds

memory 494.28k

OS Memory 8612.00k

Timestamp 11/13/2019 01:13:23 AM

Step Count 3 Switch Count 0

Page Faults 0

Page Reclaims 235

Page Swaps 0

Voluntary Context Switches 20

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

MLOGIC(UPDATELST): %PUT clearing the lock

clearing the lock

SYMBOLGEN: Macro variable DSN resolves to sched.status

MPRINT(UPDATELST): lock sched.status clear;

NOTE: SCHED.STATUS.DATA is no longer locked by you.

SYMBOLGEN: Macro variable SYSLCKRC resolves to 0

SYMBOLGEN: Macro variable J resolves to 1

SYMBOLGEN: Macro variable MAXTRY resolves to 120

MLOGIC(UPDATELST): %DO %UNTIL() condition is TRUE; loop will not iterate again.

MLOGIC(UPDATELST): Ending execution.

Re: Lock Dataset but still get lock error

ChrisNZ — Sat, 23 Nov 2019 04:03:36 GMT

That's odd indeed. Was the production process running with PID 56791?

Re: Lock Dataset but still get lock error

Patrick — Sat, 23 Nov 2019 04:08:55 GMT

Based on the SAS log it appears that even though you issued a lock out of SAS there is another process still able to also lock the same file.

File locking as such is more complicated than one would think. The article here should give you an idea.

From what I remember how things work conceptually (others might be able to explain this much better and technically correct):

SAS respects locks and behaves accordingly but other non-SAS processes might not. So it looks like you're dealing with such a process. Ideally find out what this process does.

If something worked for long and then suddenly doesn't anymore: The question is always "what changed in the environment?". For what you describe with these "sometimes locking errors" my first suspect would be a virus scanner or an archiving process which locks the file.

Just as a thought and more a workaround than a real solution:

Using option FILELOCKWAIT might get you over this speed-bump as it will prevent SAS to fail if there is a lock. You can eventually even get rid of the LOCK/UNLOCK statements at all when using FILELOCKWAIT.

Re: Lock Dataset but still get lock error

ChrisNZ — Sat, 23 Nov 2019 04:18:32 GMT

See here

https://support.sas.com/resources/papers/proceedings17/1465-2017.pdf

sadly.

Re: Lock Dataset but still get lock error

ChrisNZ — Sat, 23 Nov 2019 10:22:20 GMT

@Patrick

> SAS respects locks and behaves accordingly

SAS does not apparently, see the link.

>Using option FILELOCKWAIT might get you over this speed-bump

Does SAS only try to access the file at the end of the waiting period? Or how often?

Re: Lock Dataset but still get lock error

Patrick — Sat, 23 Nov 2019 23:51:50 GMT

@ChrisNZ

Thanks for this link. That was news to me.

Using Filelockwait SAS certainly retries frequently to access the file. If this trying exceeds the defined wait period then you'll get the locking error.

I've been using Filelockwait regularly since its introduction especially for access to control tables (like job execution logging). I've never experienced unexpected behaviours even for situations with quite a few jobs executing in parallel reading/writing to a single control table.

Re: Lock Dataset but still get lock error

ChrisNZ — Sun, 24 Nov 2019 08:12:38 GMT

@Patrick Using the filelockwait option certainly looks like it might be a better option than using the lock statement.

I'd be curious to stress-test it now in the manner described by Troy Martin Hughes in his paper.

It could very well be that filelockwait use another locking mechanism than lock's.

Another approach to the one he's chosen (using flat files) would be to rename the data set, say from STATUS to STATUS_&PID._&TIMESTAMP.. If the rename is successful, the file is yours to update and then rename back.

If the time stamp is too old, then the original session is deemed failed and other sessions can try to rename it for their own use.

Just typing as it comes to mind...

Re: Lock Dataset but still get lock error

Patrick — Sun, 24 Nov 2019 09:19:59 GMT

@ChrisNZ

Filelockwait gave me back disp=wait from the mainframe days which made me very happy.

If you're doing serious stress testing then I'd be really interested what you find. I've done some stress testing in the past but it could have been more thorough. I never ever saw filelockwait behave in unexpected ways.

Re: Lock Dataset but still get lock error

ChrisNZ — Mon, 25 Nov 2019 01:10:04 GMT

@Patrick

> If you're doing serious stress testing

I can't as I can't generate multiple sessions at the moment, but it's definitely a test I now want to do. We need a reliable and clean way for processes to lock/await tables.

Re: Lock Dataset but still get lock error

SASKiwi — Mon, 25 Nov 2019 01:38:21 GMT

@ChrisNZ - Excellent link. One great advantage of FILELOCKWAIT is its simplicity. The maximum value you can set is 10 minutes although I suspect that would be more than adequate for most purposes.