Avoid Deadlock when using External Reporting Tools

[AtifNaeemS]

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

[pavani_paluri]

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.

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for

Kind Regards,

Pavani P