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

[tammy_dezilva]

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

[RahulG]

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. 

[tammy_dezilva]

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.

[RahulG]

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

[tammy_dezilva]

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)

[Kurt_Bremser]

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.

[SASKiwi]

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.  

[tammy_dezilva]

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!!  🙂

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.

[Kurt_Bremser]

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.

[LinusH]

RO won't stop users to lock tables.

[Kurt_Bremser]

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.

[LinusH]

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.

[tammy_dezilva]

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.

[Kurt_Bremser]

---

@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.

[SASKiwi]

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.