topic Re: Determine who has a lock on metadata and how to unlock it in Administration and Deployment

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

tammy_dezilva — Fri, 30 Sep 2016 01:18:24 GMT

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:

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

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

RahulG — Fri, 30 Sep 2016 04:02:46 GMT

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.

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

tammy_dezilva — Fri, 30 Sep 2016 04:09:03 GMT

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.

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

RahulG — Fri, 30 Sep 2016 04:23:47 GMT

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

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

tammy_dezilva — Fri, 30 Sep 2016 04:27:34 GMT

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)

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

Kurt_Bremser — Fri, 30 Sep 2016 06:49:43 GMT

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.

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

SASKiwi — Fri, 30 Sep 2016 06:52:55 GMT

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.

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

tammy_dezilva — Wed, 05 Oct 2016 22:03:45 GMT

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.

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

Kurt_Bremser — Thu, 06 Oct 2016 05:42:51 GMT

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.

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

LinusH — Thu, 06 Oct 2016 06:28:06 GMT

RO won't stop users to lock tables.

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

Kurt_Bremser — Thu, 06 Oct 2016 07:39:19 GMT

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.

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

LinusH — Thu, 06 Oct 2016 17:37:39 GMT

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.

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

tammy_dezilva — Fri, 07 Oct 2016 00:58:18 GMT

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.

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

Kurt_Bremser — Fri, 07 Oct 2016 06:06:25 GMT

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

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

SASKiwi — Sun, 09 Oct 2016 18:52:09 GMT

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.

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

PaulHomes — Mon, 10 Oct 2016 08:23:17 GMT

Hi Tammy,

In the past, when trying to determine who had a handle open on a (SAS dataset) file, I would use Process Explorer. It is available for download from Microsoft Sysinternals at https://technet.microsoft.com/en-us/sysinternals/processexplorer.aspx

Whilst Process Explorer provides a point and click method for this, if you are after a scripting method then there's also a command line utility handle available at https://technet.microsoft.com/en-us/sysinternals/handle.aspx

Cheers

Paul

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

tammy_dezilva — Thu, 20 Oct 2016 02:07:58 GMT

Thanks Paul. This sounds like a great tool to suit our needs.

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

tammy_dezilva — Thu, 03 Nov 2016 23:34:46 GMT

Hi Paul

Our IT are saying that we need administrator rights in order for this to work (to see processes that run by other people) and won't give us this. Do you know of any way around this?

thanks

Tammy

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

PaulHomes — Fri, 04 Nov 2016 03:20:53 GMT

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 😉

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