Try to delete Reference registered data which published from ArcGIS Pro to Portal

[Fiona]

I have published some feature classes from an SDE Geodatabase to Portal using ArcGIS Pro as reference registered data. These datasets need to be overwritten daily using FME. However, when I try to delete the data through FME, I get an error saying the data is locked.

Has anyone experienced this issue before? How did you resolve it?

[RyanUthoff]

Is the feature service actively running when you're running the FME workspace and receive the lock error? If other people are actively using the feature service, it might not be able to delete the data because it's locked by other people. Even if no one is using the feature service, if you have it as a shared instance or a dedicated instance with minimum number of instances of 1 or more, then the feature service is running and could cause locks.

The first step I would try is look at the other connections to the DB when you receive the error. If there are other connections, try disconnecting them and trying again (keep in mind that the feature service could reconnect after disconnecting it, so it might be best to completely turn the feature service off in Server Manager to ensure it doesn't reconnect and cause locks).

[Fiona]

Thank you for your reply 🙂

FME tries to delete at midnight. I don't think those feature services are being used by others.
Yes, I set the dedicated instance with the minimum number of instances to more than one. Maybe that's the problem.
Actually, when the connection is stopped, deleting works.

I can test how it behaves with only one instance. 

[RyanUthoff · Accepted solution]

Normally there are connections to the DB with one instance running, even if no one is actually using the feature service.

You could set the minimum number of instances to zero. But the downside is that your users will have roughly an extra 30 second delay for that feature service to come online the first time it is used after being at zero instances.

I suppose the more "elegant" way of handling it would be to stop your feature service as a part of your FME process. Then you wouldn't have to worry about the feature service causing any locks. This support article says how you can do it through the Python API, which you could integrate into your FME processs. You could configure it to run the Python to stop the services before the FME process runs, then run the Python again afterwards to start the services.

[Fiona]

Thank you for the helpful tips.

[HannesVogel]

There is also an option to disable the schema lock of the Featureservice in the Publish/overwrite dialoge - it is just super hidden:
Configuration - 2. option - Advanceduncheck "Enable schema locking"

[RyanUthoff]

I thought of that option as well, but there are some additional considerations with that option, mainly if the schema changes as part of the nightly update. Here is the documentation regarding schema locking. I think both options will work in this scenario though.

I should have asked if your overwriting the entire table, or just records inside of it. I might be wrong, but I don't think disabling schema locking would impact any locks at the individual record level (although, I'm not sure if you would get any locks at the record level in the first place). 

I suppose another alternative solution is (if you're overwriting the entire table, as in deleting and recreating it), is modify your FME process to keep the table, and just truncate and append the new records to it. I think that would eliminate the schema locking issue.

[Fiona]

That's right, only records need to be overwritten.

[VinceAngelo]

Rather than rewriting the whole dataset, and having to take the service offline to do so, you could amend your process to only UPDATE the changed records, INSERT the new records, and DELETE the no longer active ones. In one system where I did this, I'm able to complete UPDATE and INSERT operations in ten minutes, processing 100k of change messages (many of which are redundant) while the parent system takes three hours to make its changes using the "reload everything" procedure.

For me, the key is to crunch the rows into a SHA-1 hash with a key (or compound key) associated with each row. Then after the changes in the parent occur, I pull the hash table into a Python dictionary, then scan the parent table, hashing each row. If the key exists in the dictionary, and the hash is equal, no change is necessary (and I delete the matching dictionary item). If the hash is different, I set the query row (and hash) aside into an update queue (and also delete the dictionary key). If the key isn't present, then the row goes into an insert queue (with hash). Once I'm done with input rows, any keys still in the dictionary become my deletion queue. Then the deletes are made from the table by key (and likewise from the hash table), updates are made to the child table (and the child's hash table in parallel), and the inserts are performed (to both table and hash). It depends on the data change frequency and volume whether I vacuum and rebuild indexes daily, weekly, or monthly.

A service that I've deployed processes 100k-500k rows every 12 hours, performing 4k-10k updates and 3-50k inserts in 10-25 minutes (and has been doing so for half a decade now). I'm about to deploy another system like it, and it will process 26-30 million rows each month, with 1k-5k in combined U/I/Ds, in a hour or so (loading the full tables from scratch takes 6-8 hours, which is a lot of downtime for a 24x7 system).

- V