SAS Table Locking: How to avoid it?

Reply
Respected Advisor
Posts: 3,886

SAS Table Locking: How to avoid it?

Hi All

I'm trying to find a way around good old table locking. I have a few ideas on how to solve it and would like your opinion on it - or even better another more elegant approach of what I could come up with.

What I have
- SAS9.3, Win Server 64 bit
- No SAS/Share
- No database
- Full admin access to the environment
- Full freedom how I design and implement

What I need to do
- Append new data to an existing master data set

The challenge
- Process creating new data triggered by user via stored process during the day
- Master data set may be opened (locked) by other user via EG
- New data created and appended should become available "instantly"

Already tried/ideas
All ideas I had so far don't append new data to a master table. Instead I keep all data as separate tables and then create a view over these tables.
Users access data then via view. But: I also need to recreate the view and the view can be opened/locked as well.

1) Tried: Data step view with wildcard
- Create tables following a naming pattern (table_01, table_02,...)
- Create a data step view over these tables using a wildcard (data alltables; set table_:; runSmiley Wink
My thinking was that this way the view would never need to be recreated.
BUT: My testing showed that the wildcard gets resolved to the table names available during compilation time of the view. Tables added after view compilation are not shown by the view.
The DESCRIBE statement for the view doesn't show this in the log(still showing "set table_:"). I've raised this with SAS Tech Support.

2) Tried: Concatenated library
- Create tables following a naming pattern (table_01, table_02,...)
- Have a concatenated library like: libname mydata ("C:\mydata\overlay_02","C:\mydata\overlay_01","C:\mydata\main");
- Have the stored process create the new data table into the main area and the new view into the first "overlay" folder not containing a view
- Have an overnight housekeeping job cleaning out the overlay folders and creating the current view in the main folder
This would work. Users shouldn't create a table more than 3 times a day so some 10 overlay folders should be good enough.
I don't like the approach too much. It's very much a work-around and I would prefer a more elegant solution.

3) Idea: Modify metadata
- Create separate physical tables and always a new view
- Assign library via metadata engine and show users only registerd tables
- Have metadata table object for view and change physical table name in object always to current view
- Have housekeeping job cleaning out old views overnight
The issue with this approach is that I've got also DI jobs which are deployed and the SAS code generated will use the physical table name at time of deployment.
That's why this approach is a no go. And I also wouldn't like to programmatically alter metadata.

4) Idea: Test for table locking
- Test if master table is locked
- Write either message to the user that the table is locked or go into wait state and try later (eg. every 30 seconds for an hour)
I've played around with checking for table locking in the past using code variations as described here: http://www.lexjansen.com/pharmasug/2005/posters/po33.pdf
(the code in the paper needs some tweaking to work properly: https://communities.sas.com/message/43021#43021).
Even after tweaking the code using FOPEN() I found in my testing that results were not fully reliable. So I don't want to go down this path again.
I also would like to come up with a solution which actually adds new data and not only tells the user that he has to wait or that "someone else" in the company is locking the table.


That's it!

All feedback very welcome.


Thanks
Patrick

Respected Advisor
Posts: 3,886

Re: SAS Table Locking: How to avoid it?

Nobody? :-(

Does this now mean I wrote too much text, or is the problem just too boring, or I'm just asking for too much?

Respected Advisor
Posts: 3,777

Re: SAS Table Locking: How to avoid it?

Sounds like you tried everthing and nothing worked.

Whats wrong with using the LOCK statement?  I would like the folks at SAS give us LOCK function.

I've been using FOPEN and have not had any problems that I know of, (but then if I knew about them :smileymischief: ) Can you be more specific than not fully reliable.

Super User
Super User
Posts: 6,494

Re: SAS Table Locking: How to avoid it?

To minimize the time window that you need to lock the file you might look to working outside of SAS.

For example you could create the updated file to a temporary name and then use operating system commands to rename (move) the old file out and the new file in.

%let path=...;

libname lib "&path";

data lib.ds_new;

  merge a.ds new ;

...

run;

x "cd &path ; mv ds.sas7bdat ds_old.sas7bdat ; mv ds_new.sas7bdat ds.sas7bdat ; rm -f ds_old.sas7bdat";

You would probably need a lot more testing to insure that it works as expected and all possible issue are accounted for.

You might be much better off just getting SAS/Share.

Ask a Question
Discussion stats
  • 3 replies
  • 2469 views
  • 6 likes
  • 3 in conversation