How to find locks preventing a specific version from being deleted within SDE tables?

2749
5
07-28-2011 05:16 PM
AndrewRudin
Occasional Contributor III
If a specific version cannot be deleted because it has locks on it, Is there a way to identify the sde process that is causing the lock using PL/SQL to inspect the table_locks, object_locks, and process_information tables within SDE?  I can't seem to find the right joins to tie a specific version to a lock.

I often ask this question of our DB admin's and their response is usually to just to kill all the connections or restart SDE.  I'd rather prevent kicking everyone off if I can.

I think the sde demon tools have something like this, but I am not the server administrator and don't have access to the box.
0 Kudos
5 Replies
AndrewRudin
Occasional Contributor III
forgot to mention, our SDE is 9.3.1 SP2, Oracle11g
0 Kudos
JakeSkinner
Esri Esteemed Contributor
If a user is accessing a version there should be a state lock.  You can query the version table for the version that is locked:

SQL> select state_id from sde.versions where name = 'Child_Version'

1864


Then you can query the state locks table to get to the sde_id of the user:

SQL> select sde_id from sde.state_locks where state_id = '1864'

4371


Next, you can query the process information table to get the user's name:

SQL> select owner from sde.process_information where sde_id = '4371'

VECTOR


You can request this user to disconnect, or you can use the 'sdemon -o kill' command to kill this specific user:

C:\> sdemon -o kill -t 4371 -i sde:oracle11g
0 Kudos
AndrewRudin
Occasional Contributor III
Thanks.  I guess there isn't a special lock for when someone is editing versus just viewing a version.  So if anyone is viewing data in a version, it can't be deleted.

I also run a python script every night to reconcile all our editing versions with DEFAULT.  Sometimes people leave their edit sessions open and the reconcile tool returns an error about the edit session being open.  This prevents us from getting a good compress, so I need to figure out how to identify these sessions and kill them before I run my script.
0 Kudos
JakeSkinner
Esri Esteemed Contributor
If you are running a python script to perform the reconcile/post/compress you will want to make sure no users are connected to the database.  A connection acquires a state lock and a locked state cannot be compressed.  You may want to consider using the 'sdemon -o kill -t all' command to kill all connected users so you can perform the compress successfully.  Also, there is a great script here for reconciling/posting versions.
0 Kudos
MichaelTrust
New Contributor III

Hi Jake, I have a Python script that for years worked fine in SDE 10.0. It deletes a version owned by SDE if it exists and then re-creates it (it's a child of another user's version). It then posts it to SDE.DEFAULT. Now we are at 10.8.1 for the enterprise GDB (Oracle 19c). The Python script now fails on the delete version step, saying there is a lock by user SDE. How do I get past this? Interestingly, I CAN delete the SDE-owned version from the Administer GDB GUI, but not using ArcPy either in a script or in the Python window in ArcCatalog. I clicked on your link above for the script for reconciling/posting versions but it just goes to https://community.esri.com/. Is the one at https://community.esri.com/t5/python-questions/using-a-python-script-to-reconcile-and-post/td-p/3527... applicable? Any help would be appreciated. Thanks.

0 Kudos