Minimum Version Required: 26.01
Pre-Conditions
Must have administrative or database management access to the system.
Requires active database monitoring tools or access to system error logs to view transaction statuses.
Must be currently experiencing database timeouts or SQL deadlock error messages during operations.
Steps
Check for Concurrent Operations: Review active system processes to determine if multiple transactions are attempting to update the same database rows simultaneously in a different order.
Review Job Scheduling: Look at the schedule for heavy close or synchronization jobs. Identify if high batch concurrency is occurring due to these jobs running at the same time.
Identify Long-Running Transactions: Query the active database processes to find any long-running transactions that might be holding database locks for too long.
Audit Database Indexes: Check the database schema for missing indexes that could be causing wide, inefficient lock scans across tables instead of targeted row locks.
Contact Support: If the issue persists after reviewing the above steps, please contact technical support. To ensure efficient troubleshooting, you must gather and provide the following information:
Exact error messages or codes encountered.
Screenshots or video recordings of the issue.
Log files (specifically database error logs and transaction logs).
The exact steps taken by the customer that led to the issue.
The date and time the issue was first observed and any recurrence.
Any changes made to the system before the issue appeared.
Fields And Definitions
SQL Deadlock: A situation where two or more competing database actions are waiting for each other to finish, resulting in concurrent transactions attempting to update the same rows in a different order.
Concurrency: The execution of multiple system operations or database transactions at the same time. High batch concurrency can occur during heavy close or synchronization jobs.
Database Locks: Mechanisms used by the database to prevent data corruption by restricting access to a row or table while a transaction is occurring.
Lock Scans: The process of a database searching for available records to lock; inefficient lock scans across tables can occur if indexes are missing.
Best Practices
Optimize Job Schedules: Space out heavy close or synchronization jobs to run during off-peak hours to minimize high batch concurrency.
Maintain Database Indexes: Regularly audit and rebuild database indexes to prevent missing indexes from causing wide, inefficient lock scans.
Optimize Queries: Ensure that custom reports and data imports are optimized so they do not result in long-running transactions holding database locks for extended periods.
Standardize Update Logic: When writing custom API scripts or integrations, ensure that transactions update tables and rows in a consistent, standardized order to prevent cross-locking.