Excellent progress on the indexing. Now let’s address the remaining deadlocks with a comprehensive solution covering all three focus areas:
Transaction Handling:
Implement batch processing with 500-record chunks. Use explicit transaction scopes with savepoints:
BEGIN TRANSACTION;
SAVE TRANSACTION BatchStart;
-- Process 500 records
COMMIT;
This isolates failures to individual batches. Set transaction timeout to 120 seconds maximum to prevent long-running locks.
Error Handling:
Implement retry logic with exponential backoff specifically for deadlock victims (error 1205). When a deadlock occurs, wait 2^attempt seconds (2s, 4s, 8s) before retry, maximum 3 attempts. Log the deadlock victim process ID and conflicting resources. Use TRY-CATCH blocks around each batch with specific handling for deadlock errors versus data validation errors. Failed batches should be queued to a retry table rather than blocking the entire sync.
Logging Strategy:
Implement multi-level logging: batch-level (start/end timestamps, record counts, success/failure status), transaction-level (savepoint creation, commit/rollback events), and error-level (full stack traces, SQL statements, record IDs involved in deadlocks). Create a sync audit table capturing: SyncJobID, BatchNumber, RecordCount, StartTime, EndTime, Status, ErrorMessage, RetryCount. This gives you full visibility into patterns.
Additional Optimizations:
Process PO headers before invoice matching to reduce table scan duration. Order your batch processing by VendorID to minimize cross-vendor lock contention. Consider running sync during off-peak hours (2-4 AM) when concurrent system activity is minimal. Monitor your tempdb usage - high tempdb contention can exacerbate deadlock situations.
Validation:
After implementing these changes, monitor for one week. Your success rate should exceed 98% with remaining failures isolated to specific vendor records that can be investigated individually. The detailed logging will show you exactly which batches succeed/fail and why.
This draft is based on general Microsoft Dynamics 365 knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.