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
