Identify Source of Schema Lock

06-21-2012 06:11 AM
Status: Open
Labels (1)
New Contributor III

Instead of the generic "Schema Lock" message in ArcGIS, how about alerting the user as to which of the possilbe hundreds of MXD, Web, or Mobile services are actually causing the lock, so that everything does not have to be shut down to remove the lock?

In version 10.1 there's a new schema lock manger.  You'll be able to see a list of all schema locks and who the users are.
Extremely painful if you don't know who has the lock.  Shutting down the instance is a workaround (albeit, the best we have to guarantee an update!).

If you have access to the RDBMS (and some people don't, so a tool WOULD be better), here's a sql statement that will show what machine has the lock (useful for direct connect, really not useful for those utilizing an ArcSDE service or virtualized PCs):

select a.lock_type, b.table_name, c.owner, to_char(c.start_time, 'MON-DD-YY HH24:MI:SS') START_TIME, c.nodename
from sde.table_locks a, sde.table_registry b, sde.process_information c
where a.sde_id = c.sde_id
and a.registration_id = b.registration_id;
In a single user environment, when its just another instance of ArcGIS that is causing the lock, how about the option to over-ride the lock??
mping wrote, "In version 10.1 there's a new schema lock manger.  You'll be able to see a list of all schema locks and who the users are."  While this is true, all (or multiple) ArcGIS Server services will have the same name, and that doesn't help.  If I only want to stop the services holding the lock I need more granular identification.  
Please also improve this situation in Arcpy. I wish would also report a schema lock but instead it reports it cannot create the output feature class or table. Even with overwriteoutput = true there are times when a single user environment creates schema locks and arcpy does not report the correct error.
Instead we start chasing down overwriteoutput options when it is not the real issue or cause of the error.  
See also: 
I have so many services that it is very difficult to pin point what service is creating a lock. A tool that shows what service is causing the lock instead of the user would be extremely beneficial and a huge time saver 

Would be niceto be able to list what exactly has it locked, not just that it's locked.


I use the following queries to find the 'culprit':

select registration_id from sde.table_registry where table_name ='<your table in question>'

select b.nodename connect_source, b.start_time from sde.table_registry a, sde.process_information b, sde.table_locks c where a.registration_id = c.registration_id and b.sde_id  = c.sde_id and a.registration_id=<registration_id obtained in the previous query>


That worked for me, thank you.