BookmarkSubscribeRSS Feed
Patrick
Opal | Level 21
Hi

I have to implement concurrent write access to a SAS table (control table) without SAS\Share (not licensed) – SAS9.2 under UNIX.

I have in principal a working solution based on the %trylock macro as documented under http://www.lexjansen.com/pharmasug/2005/posters/po33.pdf

My problem is:
In case the table is locked the macro throws an error. Also that execution goes on and everything works I can’t accept Error messages in the log nor a job to end different than with an Error condition of zero.

I can’t figure out how to achieve this suppression of Errors – especially the ones written to the log.
I could save the error status at the beginning of the macro and re-set it at the very end of the macro – but what should I do with the log?

Any suggestions would be really welcome (it starts to drive me nuts)!

The way I test the macro is to open 2 SAS session. In the first one I open the table used for testing (sashelp.class), in the second session I call the macro as below (and then I go back to the first session and close the table).

%macro trylock(member=,timeout=10,retry=1);
%local starttime;
%let starttime = %sysfunc(datetime());
%do %until(&syslckrc LE 0
or %sysevalf(%sysfunc(datetime()) GT (&starttime + &timeout)));
%put trying open ...;
data _null_;
dsid = 0;
do until (dsid GT 0 or datetime() GT (&starttime + &timeout));
dsid = open("&member");
if (dsid = 0) then rc = sleep(&retry);
end;
if (dsid GT 0) then rc = close(dsid);
run;
%put trying lock ...;
lock &member;
%put syslckrc=&syslckrc;
%end;
%mend trylock;

%trylock(member=sashelp.class,timeout=60,retry=5);

lock sashelp.class clear;


The macro as given above is exactly what has been published in the link given.

The error I'm getting if a table is locked:

trying lock ...
ERROR: A lock is not available for SASHELP.CLASS.DATA.
syslckrc=70031

As soon as I unlock the table in my other SAS session the following happens:

trying lock ...
NOTE: SASHELP.CLASS.DATA is now locked for exclusive access by you.
syslckrc=0
36
37 lock sashelp.class clear;
NOTE: SASHELP.CLASS.DATA is no longer locked by you.


So as already described: The macro works as such but I end up with Errors which shouldn't be there.
It's the LOCK statement causing the issues and I still haven't found a way to capture the error conditions.

Thanks in advance
Patrick

Message was edited by: Patrick Message was edited by: Patrick
16 REPLIES 16
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Exactly what SAS error are you getting in your log? Preferably share the SAS code in your SASLOG output, pasted as a post-reply with all code exposed, otherwise we are left guessing about the error you are concerned about.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
if (dsid = 0) then rc = sleep(&retry);
end;
if (dsid GT 0) then rc = close(dsid);
run;
%put trying lock ...;
lock &member;
%put syslckrc=&syslckrc;
%end;
%mend trylock;

Web Design Brisbane
Patrick
Opal | Level 21
Sam

I believe part of your post got "truncated".

From what I see: One of the issues is that OPEN returns a value NE 0 even if the table is opened in a second EG session.

That's why data _null_ suggested to use FOPEN.

Thanks
Patrick
Peter_C
Rhodochrosite | Level 12
Patrick
some of your macro is not appearing on the forum because the syntax uses less-than or greater-than symbols - normally reserved for "forum-markup". If you replace the symbols as suggested in that in a posting I can't find right now, with & LT; or just use the mnemonic LT for < etc. then we would be more able to see what is in your macro
It is hard work deciphering it from the "Quote Original" version that follows .
>
> %macro trylock(member=,timeout=10,retry=1);
> %local starttime;
> %let starttime = %sysfunc(datetime());
> %do %until(&syslckrc <= 0
> or %sysevalf(%sysfunc(datetime()) > (&starttime +
> &timeout)));
> %put trying open ...;
> data _null_;
> dsid = 0;
> do until (dsid > 0 or datetime() > (&starttime +
> &timeout));
> dsid = open("&member");
> if (dsid = 0) then rc = sleep(&retry);
> end;
> if (dsid > 0) then rc = close(dsid);
> run;
> %put trying lock ...;
> lock &member;
> %put syslckrc=&syslckrc;
> %end;
> end trylock;
>
> %trylock(member=sashelp.class,timeout=60,retry=5);
>
> lock sashelp.class clear;
>
>


peterC
Cynthia_sas
SAS Super FREQ
Peter:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

Use the [pre] and [/pre] tags around your code and output in order to maintain indenting and spacing. Use &lt; for < and &gt; for > symbols...

cynthia
Peter_C
Rhodochrosite | Level 12
Thank You Cynthia
that link is now added to the top of my favourites 🙂

peter
data_null__
Jade | Level 19
The program works as I expect it to, which may be a bit odd. If the program times out waiting for a lock then it still executes the LOCK statement, which I don't think you want. If you want a different action just modify it to maybe, print a message and ENDSAS when it times out.
Patrick
Opal | Level 21
Hi data _null_

I want the macro to try and lock the table - and if not successful to wait a defined time and then try again to lock the table - until either the total time for trials runs out or a lock is successful.

My "only" problem is that if the lock is not successful a error message is written to the log and I haven't found a way to suppress this.

No error condition (code) is generated in 'my' UNIX environment so the issue is at least only on the level of 'ugly'.

To be more precise: &syscc is not affected and stays 0 but somewhere the error condition must be saved as in the end of the log the errors are mentioned.

What I'm looking for is to suppress the log error message caused by an unsuccessful lock statement and not to write it to the log. It would already be o.k. if I could suppress any log messages while the macro executes and not to mention any errors in the end of the log.

Interesting is also that if I use 'lock list; in a Win32 environment then the log tells me that no one is locking the DS (even though I have it open in a second EG session), but when I submit a 'lock ;' I'm getting the all to well known error message.

Any suggestions?

Thanks
Patrick
data_null__
Jade | Level 19
> Hi data _null_
>
> I want the macro to try and lock the table - and if
> not successful to wait a defined time and then try
> again to lock the table - until either the total time
> for trials runs out or a lock is successful.
>
> My "only" problem is that if the lock is not
> successful a error message is written to the log and
> I haven't found a way to suppress this.

The macro is designed to use OPEN to TEST if a LOCK can be obtained. If a lock can be obtain by sucessfully opening the data set or the loop times out the lock is attempted. This is a flaw in the code in my opinion. I think you should modify the code to NOT attempt the lock if the loop times out. You know you can't get a lock so why try. This will fix the ERROR problem. Then you can take action as needed because you can't get the LOCK within the time limit.
data_null__
Jade | Level 19
Ok, forget that.

I don't think OPEN will work I think you will need FOPEN in Update mode.

My testing shows that if the data set is open in say EG then OPEN will not show that. However if you create a FILEREF that points to the data set and use
[pre]FOPEN(fileref,'U')[/pre]
the function will return "file in use" and you can determine if a LOCK would fail or not.
Patrick
Opal | Level 21
Hi data _null_

I made the same observation with OPEN. Kind of weird.

Thanks for the tip with FOPEN. I'll try this.

Thanks
Patrick
Patrick
Opal | Level 21
FOPEN() did the job.

Thanks again data _null_

If anybody is interested in my version of the %trylock macro please let me know and I post it here (still finalising it).
jeeth79usa
Calcite | Level 5

Hi Patrick,

Can you please post your tested version for %trylock macro.

Thanks in adavnce.

Jeeth

Patrick
Opal | Level 21

Hi Jeeth

That's now almost 3 years ago...

What I remember is that even after implementing all the good stuff data null suggested I still found cases during testing where the macro didn't behave as required. I ended up with running the jobs in sequence and "flagged" %trylock as an approach I'm no more trying to use in the future.

Thanks

Patrick

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 16 replies
  • 6539 views
  • 6 likes
  • 9 in conversation