Avoid Deadlock when using External Reporting Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago
Hi,
I seek your knowledge and experience so as to avoid Locks on Objects/ Deadlocks when using External Reporting Tools connecting thru JDBC / ODBC.
Appreciate Any sample SQL involving task or cmdb.
Best regards,
Atif
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago
Hi @AtifNaeemS ,
Use Read-Only Replicas: If possible, point your JDBC/ODBC connection to a reporting replica.
ServiceNow provides Read Replicas (Analytics DB) for reporting use cases — much safer than hitting production.
Avoid SELECT * on large tables: Explicitly list only required fields.This reduces I/O and avoids long-running scans.
Limit result sets: Always use WHERE filters and LIMIT. Avoid queries that touch the entire task or cmdb table.
Index Awareness: Filter on indexed columns (sys_id, sys_created_on, sys_updated_on, active, assigned_to, etc.). Non-indexed WHERE clauses → full table scans → contention.
No Updates via ODBC/JDBC: Ensure the reporting account has read-only access. Updates/inserts can lock rows.
External Reporting Tools
Schedule extracts during off-peak hours.
If tool supports incremental refresh, query by sys_updated_on so you only pull deltas.
Add database view wrappers in ServiceNow to simplify queries (e.g., pre-joined task+incident views).
Always test queries in sub-production first.
