Delete records - database lock issue

Question

Hello All, We are using ArcGIS GeoEvent manager 10.3.1 which gets input from GeoTab and collects data via feature service and store in SDE feature class. This is not versioned database. DBA folks alerting us particular delete record session takes more resource and results in LOCK. SQL: DELETE FROM DATA.VEHICLES WHERE OBJECTID = 7155 consumes more resource in the database. Please let me know if any one facing similar issue or any clue for this.

ENVSSol · Answer

S R​

Hello,

I am looking for a way to bring in Geotab information as a layer in ArcGIS Online / Portal. Apparently, I do not see any other posts related to Geotab in Geonet. It will be great if you can point me in the right direction. I am thinking, setting up a streaming service to geotab in Geoevent processor is the way to go. However, I would much appreciate any information in regards to this.

RJSunderman · Answer

Hello S R -

Based on my experience working with external RDBMS like Oracle (which is limited), I don't think the issue you are encountering is GeoEvent specific. However, here is some advice I received from one of the database administrators in Esri Professional Services.

1. To specifically address the message you quote below ... connect to the database as a data_editor, then delete the records from DATA.VEHICLES. You may find that you have to have your database administrator release locks on the data (refer to following bullet).

2. Never use a data owner or administrative connection to publish feature services

* Your database administrator should provide you data owner credentials for creating new feature classes (tables) in the database
* You should disconnect and reconnect as a user with a data_editor role before publishing a feature class as a feature service.
* Use or data_viewer role if you will not be adding, editing, or deleting features (not generally the case when using GeoEvent).* A data_editor has select, insert, update, and delete privileges on the table
* A data_viewer has only select privileges

What happens is that clients using the published feature service end up inheriting the publisher's privileges. So publishing as a data owner means that a client (e.g. GeoEvent) which interacts with the feature service to add features, update features, or delete features has the potential to acquire a full-table lock.

When another client tries to access the data, through the same feature service, they also make their attempt as a data_owner, potentially acquiring a full-table lock. This can produce a deadlock situation where two data owners each have locks on the table, and end up locking each other out.

3. You may find that the table's indexes have become fragmented. You may need to have your database administrator gather new statistics, reorganize the tables, and rebuild table indexes.

* Index fragmentation can quickly happen if the table has undergone large bulk inserts, updates, and deletes.
* This affects your DML (data manipulation language) performance ... e.g. insert, update, and delete operations.
* You may need to have your database administrator run a database trace to investigate the DML execution plans in order to identify and address performance issues.

Hope this information helps -

RJ

JillianStanford_0-1739384262422.png

NolanNiedermeyer_0-1739384683988.png

NolanNiedermeyer_1-1739384922169.png