Architecting, installing and maintaining your SAS environment

Determine who has a lock on metadata and how to unlock it

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Determine who has a lock on metadata and how to unlock it

Hi

 

 

I have read that you can determine who has a lock on metadata using the Open Metadata Interface and then use the OMI_UNLOCK_FORCE flag to unlock it.  I am firstly trying to see who has the lock but cannot work out how to get this information.

 

I have tried using proc metadata as below

 

proc metadata
in='<GetMetadata>
<Metadata>
<PhysicalTable Id="A57OBXEE.BE000MIP" Name="" Desc="" LockedBy="">
</PhysicalTable>
</Metadata>
<NS>SAS</NS>
<Flags>0</Flags>
<Options/>
</GetMetadata>';
run;

 

which then returns:

 

<GetMetadata><Metadata><PhysicalTable Id="A57OBXEE.BE000MIP" Name="EDWPORT.PORTFOLIO" Desc=""
LockedBy=""/></Metadata><NS>SAS</NS><Flags>0</Flags><Options/></GetMetadata>

 

I have the table open in e-guide using the sasdemo user so the table should definitely be showing as locked.

 

I have also tried using the metadata_getnatr function which also returns blank for LockedBy.

 

data _null_;
  length attr $256 value $256;
  rc=1; 
  n=1;
  do while(rc>0);
     rc=metadata_getnatr("A57OBXEE.BE000MIP", n);
     if (rc>0) then put n= attr= value=;
     n=n+1;
  end;
run;

 

n=1 attr=UsageVersion value=1000000
n=2 attr=TableName value=PORTFOLIO
n=3 attr=SASTableName value=PORTFOLIO
n=4 attr=PublicType value=Table
n=5 attr=NumRows value=-1
n=6 attr=Name value=EDWPORT.PORTFOLIO
n=7 attr=MetadataUpdated value=15Aug2016:23:20:31
n=8 attr=MetadataCreated value=27Jan2016:05:05:46
n=9 attr=MemberType value=VIEW
n=10 attr=LockedBy value=
n=11 attr=IsHidden value=0
n=12 attr=IsEncrypted value=0
n=13 attr=IsDBMSView value=1
n=14 attr=IsCompressed value=0
n=15 attr=Desc value=
n=16 attr=DBMSType value=
n=17 attr=ChangeState value=
n=18 attr=Id value=A57OBXEE.BE000MIP

 

What am I doing wrong?

 

I have then tried using the OMI_UNLOCK_FORCE flag to unlock the table

 

proc metadata
in='<UpdateMetadata>
<Metadata>
<PhysicalTable Id="A57OBXEE.BE000MIP" Name="" Desc="">
</PhysicalTable>
</Metadata>
<NS>SAS</NS>
<Flags>262144</Flags>
<Options/>
</UpdateMetadata>';
run;

 

but get ERROR: The user does not have permission to perform this action.  

 

I have administrator privileges.

 

Any help would be greatly appreciated.

 

thanks

Tammy


Accepted Solutions
Solution
‎08-09-2017 02:03 PM
PROC Star
Posts: 424

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva

Hi Tammy,

 

In that situation I might keep asking IT to tell me who has a file locked in the hope that they might eventually decide to delegate access so I can find out for myself Smiley Wink

 

Something else that comes to mind is to use SAS library access logging to give you an idea of who has been accessing the table most recently. Gerry Nelson wrote a blog post about it: Auditing data access: who did what and when? There's some more info in the Audit Messages for SAS Library Access section of the SAS 9.4 Logging: Configuration and Programming Reference.

 

Once you have the logging enabled you would write a stored process to report on recent table access. 

 

Cheers

Paul

View solution in original post


All Replies
Super Contributor
Posts: 266

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva

This is an interesting question. Let me try if I am able to put my point

 

Through metadata function you can only see whatever gets updated in metadata. When a table is locked by SAS EG, there is no change in metadata as such. The lock is put on the physical table not the metadata. Thats why you can't view using Open metadata Interface or SAS metadata_getattr function.

 

Now question would arise what about the "LockedBy" attribute in metadata. When we check out any table using SAS DI then this attribute is updated. Value of Lockedby would be the URI of the user  who has checked out the object.  You can try this by making a check out of any table then find the Locked by value. 

 

Using metadata function it is not possible to find locked table and to unlock it. 

 

 

Contributor
Posts: 47

Re: Determine who has a lock on metadata and how to unlock it

Ah, thank you Rahul. I have misunderstood the type of lock those methods and functions are for.  

 

We have production jobs trying to update tables but they are falling over as there is a lock.  It turns out that a user has the table in their e-guide project and even though they don't have it open it is still causing issues as the metadata is in use in the active e-guide session.

Super Contributor
Posts: 266

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva

I am not sure on this, but you can explore that if we can apply an option that table lock should be row level.

 

Check the advance properties of Library. But this has to be approved by your DBA

Contributor
Posts: 47

Re: Determine who has a lock on metadata and how to unlock it

I think the lock is actually on the metadata as when we had the job running with the library as pre-assigned we didn't have this problem but now every second day or more we have several users having this core table in their e-guide project and it's causing a lock even though they aren't actually using the table at the time.  (they leave jobs running on their pc's overnight)

Super User
Posts: 7,762

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva

A lock on the table does not happen in metadata, but on the OS level.

So if a table is locked and can't be overwritten, you can only try to find the offending processes and terminate them.

If your SAS system runs on UNIX, I have a (IMO nice) workaround where I simply delete the dataset first with the operating system's rm command, and then create it. On UNIX, this is possible because file and directory information is kept in separate places. The locking process still has its handle on the physical file, but the directory reference is removed, and a new file can be created. The locking process will see the old file until the handle is released (file closed), at which point the OS also deletes the physical data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 3,250

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva

Please post your actual SAS error message. It is easy for users to cause this type of problem by opening a view data window in EG and just leaving it.

 

We deal with this type of problem by making our Production data libraries read-only via OS folder permissions and AD user groups for all SAS users except for SAS administrators allowed to run Production updates.  

Contributor
Posts: 47

Re: Determine who has a lock on metadata and how to unlock it

Our users have told us they haven't left the view data window open.  Obviously someone is telling fibs as I think this is the issue as well!!  Smiley Happy

 

I have read only for the users in metadata and also on the file system (Windows).

 

This is the error message:

 

631 proc datasets lib = EDWPORT nolist nowarn memtype = (data view);
632 delete PORTFOLIO;
633 quit;

NOTE: Deleting EDWPORT.PORTFOLIO (memtype=VIEW).
ERROR: A lock is not available for EDWPORT.PORTFOLIO.VIEW.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.

 

Super User
Posts: 7,762

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva

Well, there might be "dead" processes lying around (workspace servers that have lost their client connection, but don't act properly by shutting themselves down). One can only try to use operating system tools to locate such processes. Sometimes locks might be caused by backup software or malware scanners (which are only needed on the Windows *@x?!#, one of the gazillion reasons Windows should never be used on a server).

Be also aware that a lock on a SAS view might be caused by a lock on one of the datasets referenced in the view.

 

So you might have some detective/computer forensics work coming up.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,424

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva
RO won't stop users to lock tables.
Data never sleeps
Super User
Posts: 7,762

Re: Determine who has a lock on metadata and how to unlock it

[ Edited ]

Actually, the problem is not that the file is locked, but that a lock (which is necessary for writing) can not be obtained, because some process(es) has/have an open handle on that file.

And therefore setting the file, directory or filesystem to RO for the other users (that don't need write access) won't remedy the problem.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,424

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva
There have been several threads on the forum regarding similar issues. Scroll through those might give you some insight.
Bottom line is that can be resolved either on that application level by killing any process that holds a lock. Or move your data to an engine that has better multi user support. In SAS that is SPD Server.
Data never sleeps
Contributor
Posts: 47

Re: Determine who has a lock on metadata and how to unlock it

There's no way for me to determine which process it is that has the lock on Windows however.  I don't want to have to kill all processes.

Super User
Posts: 7,762

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva

tammy_dezilva wrote:

There's no way for me to determine which process it is that has the lock on Windows however.  I don't want to have to kill all processes.


If you want to run SAS as a more-or-less administrator or at least as a power user, access to certain operating system features and knowledge how to use them is essential. Either you run the system, or the system runs you.

 

On top of that, it is my personal belief that Windows is NOT suited as a server platform. Certain multi-user issues are much more easily dealt with in a UNIX system. Like the problem with open SAS datasets.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 3,250

Re: Determine who has a lock on metadata and how to unlock it

Posted in reply to tammy_dezilva

I'm wondering if it is possible to change your application so the troublesome VIEW is created in a user's WORK or SASUSER library. That way each user has their own copy so it can never be locked.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 1363 views
  • 6 likes
  • 6 in conversation