topic Re: Persistent state locks in Geodatabase Questions

Persistent state locks

JohannesLindner — Sun, 12 Dec 2021 07:15:03 GMT

I have a versioned EGDB with only the DEFAULT version.

When I compress the database, I never get below 8 states. Wondering about this, I investigated a bit and found that I have exclusive state locks from February this year, which stop these states being compressed.

sqlexe = arcpy.ArcSDESQLExecute(r"my\database\connection.sde")

sqlexe.execute("SELECT state_id FROM sde.sde_states")
# 8 states
# [[0.0], [13388.0], [13394.0], [13389.0], [13390.0], [13391.0], [13392.0], [22930.0]]

query = "SELECT s.state_id, s.creation_time, s.closing_time, s.parent_state_id,  l.lock_type, l.lock_time FROM sde.sde_states s JOIN sde.sde_state_locks l ON s.state_id = l.state_id"
for s in sqlexe.execute(query):
    print(s)
# state_id, creation_time, closing_time, parent_state_id, lock_type, lock_time
# [13388.0, '07.02.2020 11:36:32', '07.02.2020 11:40:27',     0.0, 'E', '07.02.2020 11:36:32']
# [13389.0, '07.02.2020 11:40:27', '07.02.2020 11:52:08', 13388.0, 'E', '07.02.2020 11:40:27']
# [13390.0, '07.02.2020 11:52:08', '07.02.2020 11:55:08', 13389.0, 'E', '07.02.2020 11:52:08']
# [13391.0, '07.02.2020 11:55:08', '07.02.2020 12:00:17', 13390.0, 'E', '07.02.2020 11:55:08']
# [13392.0, '07.02.2020 12:00:17', '07.02.2020 12:00:18', 13391.0, 'E', '07.02.2020 12:00:17']
# [13394.0, '07.02.2020 12:04:34', '07.02.2020 12:04:45',     0.0, 'S', '07.02.2020 13:13:50']
# [22930.0, '03.09.2020 10:09:39', '03.09.2020 10:09:41', 13394.0, 'S', '03.09.2020 10:09:41']
# [22930.0, '03.09.2020 10:09:39', '03.09.2020 10:09:41', 13394.0, 'S', '03.09.2020 10:24:06']
# [22930.0, '03.09.2020 10:09:39', '03.09.2020 10:09:41', 13394.0, 'S', '03.09.2020 10:54:31']
# [22930.0, '03.09.2020 10:09:39', '03.09.2020 10:09:41', 13394.0, 'S', '03.09.2020 11:14:42']
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

As I understand it, exclusive locks (lock_type 'E') are created when editing data. The locked states have been closed, so the locks should have been lifted, right? What could have caused them to persist?
Why does the shared lock (lock_type 'S') persist on state 13394? The current state is accessed by multiple services, but AFAIK there weren't any services running back in February. Is it because it's the parent of the current state?
Should I worry about this?
How can I get rid of these locks?

Re: Persistent state locks

George_Thompson — Fri, 04 Sep 2020 11:00:37 GMT

The lock behavior could be related to the location of the feature class, i.e. in a Feature Dataset.

Schema locking—ArcMap | Documentation

How to get rid: Manage geodatabase locks—ArcMap | Documentation

I would not necessarily worry about this unless you are having performance or other issues in the Enterprise Geodatabase times.

Is the end goal to get to state "0" on a compress?

Re: Persistent state locks

JohannesLindner — Fri, 04 Sep 2020 13:33:10 GMT

Thanks for the links!

No, I don't have performance issues and I don't have to get to state 0.

I'm just a little worried that this might signal some inconsistency within the database that'll bite my ass later on.

I can only see the locks through SQL queries, ArcGIS Pro and ArcCatalog don't show any. Seems like disconnecting all users and compressing could do the trick. I'll talk to my server admin next week.

Re: Persistent state locks

George_Thompson — Fri, 04 Sep 2020 14:30:11 GMT

In order to see the locks you have to connect as the Geodatabase administrator and depending on the permissions granted, you can disconnect them.

If you want to get a "full" compress, I would recommend stopping all connections to the GDB and pausing the DB, then compress to see what the end result is.

This may have to be done during a maintenance window.