Is there an easy way (search/query) to figure out which service(s) are acquiring a schema lock?

AdamCottrell · ‎06-27-2017

I have several services and it would be time consuming to open each on to figure out which service(s) have acquired a schema lock. Can this be searchable through python or is there already a search tool for this and I just do not know that it exists? If not, i think this would be a good "Idea" for ArcGIS Ideas. I will post it there if it doesn't exist.

Thank you.

RebeccaStrauch__GISP · ‎06-27-2017

I have a script that I use to find all the services that are using a FGDB and check for any locks on the FGDB, but not on the service itself. But I run it in a PREVIEW/debug mode so I can see if there ar any locks on the data, before I run it for real and stop those services so I can replace the FGDB and then restart those services. I don't know whether that is what you are trying to get at of not.

Something to try...

Wrapping the "STOP SERVICES" in a try: ..except: block might give you a list of services that can not stop because of locks. (?)

Beyond that, I don't know how to query for the services themselves. but ArcGIS REST API or maybe the API Reference for the ArcGIS API for Python — arcgis 1.0.1 documentation is a good place to start looking.

Just some thoughts to maybe kick start some other ideas.

ModyBuchbinder · ‎06-27-2017

In the FGDB directory there are lock files (size zero) that represent the lock.

The name of the lock is the name of the machine where it comes from. The second number on the lock file name is the PID of the process that create it.

For example I have file _gdb.MODYB-PC.15104.8268.sr.lock in one of my .gdb directory.

The lock comes from machine MODYB-PC and the process ID is 8268 (ArcMap.exe in my task manager).

I have no idea what is the 15104

If it is an ArcSOC.exe process you should check the command line (in task manager) the service name is one of the parameters to the ArcSOC.exe

It is not very simple but it can all be done in Python (without Arcpy).

If you will write it it will be great if you can share it.

Have fun

Mody

AdamCottrell · ‎06-29-2017

Thank you Rebecca and Mody for your responses. I think I need to get with tech support because I ran through all my map services and not a one of them is acquiring schema lock. So I do not know why I am getting a lock on the sde database coming from a server. My only guess is that a portal instance, from another server, is wanting to connect to a map service and this for some reason is locking down the schema. Any ideas of what could possibly be making the ArcGIS Server constantly reconnect and acquire a schema lock on the database even though I setup the services not to lock the schema?

RebeccaStrauch__GISP · ‎06-29-2017

Most of my suggestions and the script I have are for fgdb not, sde. I actually do not go directly to my SDE for services anymore (I do not use ArcGIS Server for editing at all), but instead, I create a copy to a fgdb and run my services off that. Seems to be faster without the overhead of SDE and a RDMS, and prevents any schema locks on our master SDE databases (where we do edit).

Not that these help solve you problem..

FAQ: Why does ArcGIS Server lock the ArcSDE schema?

FAQ: How are the various lock mechanisms implemented in ArcSDE and the geodatabase?

I found those in this old (2012) thread Schema Locks in Multi-User SDE no solutions there either.

Preventing Schema Locks in ArcSDE from ArcGIS Server 10.1. | Idaho Fish and Game Again, older, but might help. But per your OP, you may have already done this.

And fwiw, I found this old archived "ideas" that said it was implemented. Manage Schema Locks | GeoNet

AdamCottrell · ‎06-30-2017

Thanks again Rebecca! Not to get off topic but I think I may reach out to you because I'm wondering about your workflows. I'm trying to implement a mobile strategy using Collector for ArcGIS to update our data. Our asset managers are out in the field and my understanding is that SDE, AGOL or Portal's data store is needed for editing features and fgdb is not capable. I do not see us moving to AGOL or the Portal's Data Store anytime soon, if at all for that matter because we are on a SQL Server platform here at the city and it is open to work with other systems. I will have a look at these links. I cannot say it enough - Thank you for your time and contribution to this question.

RebeccaStrauch__GISP · ‎07-03-2017

Hi Adam,

We are just starting to get more into using Collector (still mainly testing). Our work tends to be just one way (input or animal surveys, etc.) and so far we have been using AGOL for our data collection. This may change, especially once we can get Portal 10.5.1 installed. Our hold up on that is getting newer servers and SQL setup, which had to wait until our regulation year end (June 30), so I'm hoping this will be moving forward soon.

Most of our data collection will also be offline (sync'd on return to internet connection) and we use a different coordinate system and tiling scheme than AGOL, so that is a challenge we are working on right now.

I followed you, so feel free to DM me if you need any specifics.

ModyBuchbinder · ‎07-01-2017

Hi Adam

SDE manage locks in tables (table_locks etc). In SDE esri wrote a full interface to understand the locks. If you right click on the database connection and use "administration" you have a tab "locks" there you can see all locks, get locks for specific table and even disconnect a user if you have privileges.

ESRI - we need similar tool for FGDB!!

AdamCottrell · ‎07-03-2017

Thanks Mody. This is what I use to disconnect the connection. The connections are through a server which leads me to believe that it is through ArcGIS for Server App. The user is the db account that I use to author and publish services. I have a lot of services and I cannot figure out which services that keep locking the schema. On one ArcGIS Server instance, I check all my services and not a one of them had the "Lock Database Schema" enabled. This leaves me to believe that it may be Portal because it is accessing some map service on those servers.

RickGeittmann · ‎07-06-2017

Adam,

Any service will create a lock on data - so by having services running they are going to lock the schema to anything they are connected to.

Locks in workgroup or enterprise geodatabases

Users must own a dataset to make changes to its schema and have the appropriate privileges granted to them to edit other user's data.

Schema locks, both shared and exclusive, are applied to individual datasets and related tables. For example:

If you acquire a lock on a feature class within a feature dataset, the lock applies to the entire feature dataset and its contents.

Locks also apply to both sides of a relationship class. For example, if two stand-alone feature classes are related via a relationship class and you acquire an exclusive or shared lock on one of them, the lock also applies to the other.

Here is some further information on schema locking (rules) - it is older but still gives some good information. In short any connection is going to result in a lock.

http://edndoc.esri.com/arcobjects/9.2/NET_Server_Doc/manager/geodatabase/creating_a_geodatabase/sche...

Hopefully this gives you a bit more info that will help.