For a SAS dataset, what is the best way to prevent locking for multiple user access

Question

Thanks for reading this.

I am using a shared service (server=sharedLib) when setting up my libref, to allow users of my SAS/IntrNet application to modify and update (add new) records of a single dataset. The application will also be used to query my dataset. To minimize locking, I am only using a data step to modify and update rather than Proc SQL (which locks the entire member). However, I wonder if locking is more or less likely if only update/modify access to the data uses the share service but queries do not.

%if &type=QUERY %then %do ;
 LIBNAME lib '/myServer/library' ;
%end ;
%else %do ;
 LIBNAME lib '/myServer/library' server=shareLib ;
%end;

this isn't my actual code, but I do know whether or not the request is going to just send data back or modify an existing record or add a new record (update);

I had originally made this distinction because we were having some failures attaching to the share service (not sure that is the correct terminology), but referencing the lib to query the data did not fail. Since then we have, I think solved this problem, but I wondering if I am setting myself up for problems.

Thanks

Answer 1

Since your question is more like a request for general advice on data access and concurrency in SAS, my answer will be formed as general advice, more than a specific solution.

There are excellent SAS online documentations. Please go visit the index, and find the information relevant for your further reading.

1. Please have a further look at the "ACCESS=READONLY" libname option. It pretty much does what it says, namely restrict access to data members in the libname to be read-only. This holds the benefit that you don't alter your data by accident during a non-altering query. It also enables SAS to leave some room for data altering queries to get higher levels of control over the data.
2. SAS/SHARE enables concurrent data access, so if you need to provide concurrent access (read/write) to the same data, SAS/SHARE is a good choice. It means that you can get away with assigning your libname just once, giving your libname statement the option "SERVER=SHARELIB", and have SAS/SHARE manage concurrent data access. If you assign the libname to your SAS/SHARE server process, all subsequent SAS processes needing access to this libname only have to assign the libname like "LIBNAME LIB SERVER=SHARELIB" (note there is no physical path - the SAS/SHARE server process takes care of that). This setup functions at its best if you have a separate SAS process for your SAS/SHARE server.
3. You can also lock your libname with the LOCK statement or the LOCK command. This means that your SAS program can ensure itself exclusive access rights to a libname, in case that's what you need. Other SAS processes can then use the lock command to query a specific libname and see if it can get (exclusive) access.
4. You can also control access on the data member level. This is done with the CNTLLEV data set option. For example "DATA LIB.MYDATA(CNTLLEV=LIB);" specifies that access control is at the library level, restricting concurrent access to only one update process to the library. CNTLLEV=MEM and CNTLLEV=REC restricts concurrent access at member level and record level respectively.

These options can be combined in many different ways, giving a lot of room for you to make access as fine-grained as you need. I hope these choices will help you complete your task.