BookmarkSubscribeRSS Feed
Opa4ki
Calcite | Level 5
Hi.

I'm running SAS BASe on Solaris. I have one small table which is used for logging. Many processes use it. They use it only once and they just get new ID from this table - increment last record and store new ID in table. That's all what they do with it.

The issue is that processes frequently fail because this table is locked.
What are the general ways to workout this?

We also have an SPD engine, but i didn't have good knowledge about this stuff now. Some people around me say that SPD helps with this issue. But there is and issue with SPD service itself - it fails frequently too.(actually not so frequently but one fail makes big problems). Message was edited by: Opa4ki
3 REPLIES 3
DanielSantos
Barite | Level 11
Several paths are possible.

First, you should be aware that this is a system services concern.
SAS datasets are stored in system files, and system files access is managed by the OS.

Some systems do provide file locking management (z/OS) other don't (UNIX, Windows).

SAS have it's own solution through the SAS/Share Server ( http://www.sas.com/products/share/index.html ).

But, alternatively, you can implement your own file locking management through the explicit LOCK statement.

Overview:
http://support.sas.com/documentation/cdl/en/shrref/59595/HTML/default/a000203947.htm

Online documentation for the LOCK statement here:
http://support.sas.com/documentation/cdl/en/shrref/59595/HTML/default/a000384048.htm

You'll probably need this too (checking for successful LOCK):
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a000543758.htm

I have seen other implementations, such as using an external relational DB (Oracle, DB2, SQL Server) to manage the locking problem (since the implementation of lock management is native nearly in all DB). But surely it is the more complex way to do this.

If you're going for own locking management implementation, be aware of the DEADLOCK situation ( http://en.wikipedia.org/wiki/Deadlock ).

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.
Opa4ki
Calcite | Level 5
Hi again
Thanks for answer.

When i was writing first message i didn't have enough knowledge about how do we use shared table and how we lock it.

We are trying to lock it with Lock statement exactly the way it is written in this paper
http://www.lexjansen.com/pharmasug/2005/posters/po33.pdf
But it doesn't work well. In spite of some trick with SCL open\close strategy which is given in paper.
The problem is, that when one process has locked the table, and another one is trying to acquire the lock with "lock TableName". Lock statement writes ERROR message in log. And it is not appropriate for our code. Because it is made in such a way, that if there is an ERROR message in log, it thinks that the process failed.
And we have a long batch tree of processes, and when one of them fails(even if it worked well, but had just dummy ERROR message in log) others don't start.
DanielSantos
Barite | Level 11
Well in theory, the QUERY or SHOW options of the LOCK statement should allow you to check if a table has been locked by some other process.

Actually there is a bug, and those options might not work as expected:
http://support.sas.com/kb/2/859.html

But, if the log is the only problem, try the following macro:

%macro lock_try(DATA);
filename tempfile temp;
proc printto log=tempfile; run;
lock &DATA;
proc printto; run;
%mend lock_try;

%lock_try(LIB007.TAB00130);

It will attempt to lock the provided table, and redirect the log to a temporary file.
No message will be displayed in the log.

To see if the lock was successful, just check the value of the SYSLCKRC automatic macro variable.

More about redirecting output/log to a file:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001330273.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

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
  • 3 replies
  • 2234 views
  • 0 likes
  • 2 in conversation