Schema Locking in SDE


Are you receiving the following error: “Lock request conflicts with an established lock.” or unable to change any properties of your data via ArcCatalog?

This is a common occurrence that occurs when you are making schema changes such as:

a) Renaming a Feature class / table
b) Deleting a Feature class / table
c) Adding a new Feature class to a Feature dataset
d) Register as Versioned…
e) Deleting / Adding a Field
f) Applying geodatabase behaviour (Eg. Domains, Subtypes, Relationship classes)

When making any of the above changes, you are making a change to the structure of the dataset, in essence, a change to the schema of the database object. The most common cause of a schema lock is displaying or viewing a dataset in ArcMap. A schema lock error or issue occurs when schema changes to a database object (Feature class / Table / Feature Dataset) is attempted to be made via ArcCatalog, *while* another user or application (ArcMap) is accessing the data at the same time.

In ArcSDE geodatabases, more than one user can be viewing and editing the same data at the same time. To be able to work with data in a geodatabase for applications such as ArcMap, the application must work on the principle that the geodatabase schema remains fixed and is not changing at any time that you are working with the geodatabase contents. For example, while adding a feature class from a geodatabase to your map, its schema cannot be altered by you or another user. Once you have removed the feature class from your map and no other users are querying or editing that feature class, its schema can be altered.

Thus, in order to make a schema change (such as adding a new field to a feature class, or even adding a new feature class to a feature dataset), the feature class cannot be accessed by another ArcGIS application such as ArcMap – even for viewing.

A schema lock is applied on the feature class even when the feature class is just opened for viewing in ArcMap. This ensures the integrity of the data.

ArcSDE geodatabases supports multiple users editing and viewing the same dataset. However, when a schema change is being made, the software applies strict rules, in that only *one* user can be accessing the feature class, table or feature dataset (being changed) in order to make that change. Once the schema change is implemented, all users can continue to view and edit the data simultaneously again.

Other applications that also place a schema lock on the data are:
a) ArcIMS map services
b) ArcGIS Server map services
c) Dekho map services

Thus, in order to make any schema changes to the SDE data, these map services will either need to be stopped or the map service schema lock disabled.

To stop the SOC and SOM services, which in turn will stop all map services, you can manually stop each service via ArcCatalog or the ArcGIS Server Manager.

Or secondly, you can disable the map service schema lock, by setting the tag to false. This tag can be added to the services configuration file (.cfg) for the map service found in
C:\Program Files\ArcGIS\server\user\cfg
OR
C:\Program Files (x86)\ArcGIS\Server10.0\server\user\cfg

More information can be found via the following ESRI Australia technical blog.
https://esriaustraliatechblog.wordpress.com/2011/11/30/map-services-sde-and-locks/

Thus, to get around the “Lock request conflicts with an established lock.” error, it is recommended to perform *all* schema changes after hours when there are less chances of other users editing or viewing the SDE data you are attempting to change.

More information regarding the above can be found via the following ArcGIS Desktop topic: “Schema Locking”:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//003n0000002m000000.htm

Tania T.

10 thoughts on “Schema Locking in SDE

  1. Bill

    Tania, This is an excellent explaination of the behavior I’m seeing when trying to add a feature class to a dataset. Do you know how to break the locks in a programatic way? I have a program that creates new feature classes in a dataset. If a lock is encountered, it would be nice if the program could break the locks and keep going.

    Reply
    1. Tania T Post author

      Jay, apologies for the delayed response. Dekho is Esri Australia’s locally developed geographic web application server that integrates seamlessly with other corporate systems, to combine vast amounts of data into a user-friendly, easy to navigate, web-based interface. More information can found via the ESRI Australia – Dekho web page: http://esriaustralia.com.au/products-dekho.

      Reply
  2. chad

    Adding a field through the arcmap attribute viewer doesn’t work as you say, but the error says the only user is me. So why doesn’t it work if I go through the Toolbox > Data Management > Fields > Add Field tool instead, without ever loading the layer (after a reboot, even)? Unfortunately, toolbox tools usually give useless error messages, so who knows why it can’t get the lock.

    Reply
  3. Mosheur Rahman

    Tania, very good document. Now, if I have a situation like I have got a schema lock but I have to do the schema change anyway. If I do not worry about the user connected, is there any way to forcedly disconnect the user and do the schema change? If yes, what are the steps and what impact may happen to that user or database?

    Reply
    1. Tania T Post author

      There are a few ways to disconnect a user/client application that is locking the schema to perform schema changes:
      1. In ArcCatalog 10.1, connect as the sde user. Right-click connection, and select Administration. From the Geodatabase Administration dialog, from the Connections tab, select the connections holding down the SHIFT key, right-click and select “Disconnect”.
      2. If these connections are being consumed by a web service, you can either stop the associated web services via ArcGIS Server Manager; or stop the “ArcGIS Server” service from Windows services; or disable “Lock Database Schema”.

      When either of these steps are performed, it is recommended to perform schema changes when the system is idle, or after hours when there is less likelihood of users performing edits or querying the datasets whose schema is being updated.

      The impact of disabling “Lock Database Schema” is best described via the following ArcGIS Resource web help:

      http://resources.arcgis.com/en/help/main/10.1/index.html#//0154000005z9000000

      Disabling schema locking on a map service
      “…When schema locking is disabled, it is recommended that you only update your source dataset schemas when the service is idle. If you update the dataset schema when the service is under load, users of your map service may experience unexpected behavior such as layers and fields missing, queries failing, and service unavailability. Changes to the schema of the dataset need to be carefully planned; permanently removing datasets may leave your map services in an inconsistent state.”

      Reply
  4. Sanne

    Hi Tania. Thank you for a good explanation. I have a lot of webservices running. I often get lock conflicts even when I have stopped the services. Mostly it is because i have forgotten, that which web services are associated to which featureclasses. Is there an easy way to see that?
    /Sanne

    Reply

Got something to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s