arcpy.TestSchemaLock ArcGIS Pro 3.3.5

Question

Hi all,

Relatively new to python and Model Builder. ArcGIS Pro 3.3.5 and ArcGIS Enterprise 11.3 and SQL Server 2022.

I'm trying to use arcpy.TestSchemaLock in a python script tool in my SQL geodatabase Model Builder model that I'm scheduling to run from the ArcGIS Pro geoprocessing 'schedule the tool'. This is similar to this 10 year old post: https://community.esri.com/t5/python-questions/arcpy-testschemalock/td-p/300632

It seems like arcpy.TestSchemaLock does not work in this scenario as it is always detecting a schema lock when there isn't one. I stop all of my Map Services first in a python script tool my Model.

Has anyone done this before?

I reached out to Esri tech support and their solution was to manually stop the ArcGIS Server Service on the Services window on the server Every Night at the end of the work day then restart it manually Every Morning. There is No Way this is realistic. I guess they think the reason I can't get a schema lock is because that Service is still running. But if I don't include the arcpy.TestSchemaLock python tool in my model it runs just fine. I want to include it in case someone leaves ArcGIS Pro open over night with a SQL connection.

I thought of posting this in the ModelBuilder question area too https://community.esri.com/t5/modelbuilder-questions/bd-p/modelbuilder-questions but maybe an Admin can link it?

Thank you!

Andrea

AndreaB_ · Answer

Thanks for the reply, @HaydenWelch . I'm afraid that's too advanced for me to understand and use.

Esri tech support was not helpful in this case. So what I ended up doing was splitting my model into 3 models and scheduling them to run about 20 mins apart. I didn't use arcpy.TestSchemaLock at all since it didn't work. My first model runs all of the geodatabase maintenance. The second model will re-create the geodatabase Version (if geodatabase maint model couldn't get a schema lock it will still have deleted my Version, so this is just in case. If geodatabase maint model ran correctly then this second model will fail since Version is already there. Totally fine). The third scheduled model will Start Services (if geodatabase maint model couldn't get a schema lock it will still have Stopped Services, so this is also just in case. It runs regardless of the other models' outcomes and Starting services that are already Started doesn't harm anything).

If this helps anyone in the future, great! It's working so far.

Hayden Welch · Answer

Have you tried using da.Editor instead? Attempting a one row transaction will capture locks.

import arcpy
from arcpy._mp import Layer

def editable(table: Layer | str) -> bool:
    try:
        with arcpy.da.UpdateCursor(table, ['OID@']) as cur:
            for row in cur:
                cur.updateRow(row)
                break
    except Exception: # Catch all possible exceptions, you can get more granular here
        return False
    return True

with arcpy.da.Editor(database, multiuser_mode=True) as editor:
    if not editable(table):
        arcpy.AddError(f"Unable to get lock for {database}")

# Continue transaction 
    ...