How to fix Error 1205 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 1205 – Transaction Deadlock Victim

Why This Error Happens

This error occurs when two or more transactions in SQL Server 2025 hold locks on resources (rows, pages, or tables) that each other’s transactions need, creating a circular dependency that cannot be resolved. SQL Server’s deadlock monitor detects this cyclical blocking condition and automatically terminates one transaction (the “victim”) to allow the other to proceed. This is typically caused by inefficient query design, missing indexes causing broader lock scopes, long-running transactions, or inconsistent object access order across concurrent sessions.

Step-by-Step Resolution Guide

Step 1: Capture Deadlock Graph Details

  • Enable trace flag 1222 to log deadlock graphs to the SQL Server error log: DBCC TRACEON (1222, -1).
  • Alternatively, configure a Extended Events session using the built-in system_health session or create a custom session targeting the xml_deadlock_report event.
  • Locate the SQL Server error log via SQL Server Management Studio (SSMS) under Management > SQL Server Logs, or check the physical log path at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 2: Analyze the Deadlock Graph

  • Open the captured XML deadlock graph in SSMS by right-clicking the event in Extended Events and selecting View Target Data.
  • Identify the two conflicting processes, the specific resources (table/index names), and the lock types (e.g., KEY, PAGE, RID) involved.
  • Note the exact T-SQL statements and execution plans associated with each session to determine the conflicting access pattern.

Step 3: Optimize Indexing and Query Logic

  • Add or modify indexes on the involved tables to reduce lock footprint, particularly covering indexes that avoid key lookups.
  • Ensure all transactions access tables in the same consistent order (e.g., always Table A before Table B) across all stored procedures and application code.
  • Wrap multi-statement transactions in explicit BEGIN TRANSACTION / COMMIT blocks with the shortest possible duration to minimize lock hold time.

Step 4: Adjust Isolation Level or Locking Hints

  • Consider changing the database isolation level to READ_COMMITTED_SNAPSHOT to reduce blocking: ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON;.
  • For specific high-contention queries, apply locking hints such as WITH (ROWLOCK) or WITH (UPDLOCK, HOLDLOCK) where appropriate to control lock granularity.
  • Review application-level retry logic to ensure deadlock victims are automatically retried using TRY...CATCH blocks with error number 1205 detection.

Step 5: Implement Application-Level Retry Logic

  • Modify the data access layer to catch SQL error 1205 and automatically resubmit the transaction after a short delay (e.g., 100-500ms with exponential backoff).
  • Limit retry attempts to 3-5 to prevent infinite loop scenarios under sustained contention.

Alternative or Verification Steps

  • Re-run the previously failing workload under load testing while monitoring the system_health Extended Events session; confirm no new xml_deadlock_report events are generated over a sustained period.
  • If deadlocks persist despite indexing changes, consider using sp_getapplock to serialize access to critical sections of code, or set a deadlock priority using SET DEADLOCK_PRIORITY to control which session is chosen as the victim.