BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tammy_dezilva
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaulHomes
Rhodochrosite | Level 12

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

View solution in original post

18 REPLIES 18
RahulG
Barite | Level 11

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
Quartz | Level 8

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
Barite | Level 11

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
Quartz | Level 8

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
Super User

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
PROC Star

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
Quartz | Level 8

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
Super User

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
Tourmaline | Level 20
RO won't stop users to lock tables.
Data never sleeps
Kurt_Bremser
Super User

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
Tourmaline | Level 20
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
tammy_dezilva
Quartz | Level 8

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
Super User

@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
PROC Star

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.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

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