Solving “Transaction (Process ID) was deadlocked on lock resources.”

The error “Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction” (SQL Server Error 1205) occurs when two competing transactions hold locks on separate resources and each attempts to acquire a lock on the resource held by the other. Because neither process can proceed without the other releasing its lock, Microsoft SQL Server detects a circular dependency, terminates the query with the lowest rollback cost (the “deadlock victim”), and immediately rolls it back to free system resources.

Quick Answer

To solve deadlock errors when refreshing Excel reports, add the WITH (NOLOCK) table hint or execute SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; at the beginning of your SQL query so read operations bypass shared locks. For a permanent server-level fix, have your database administrator enable Read Committed Snapshot Isolation (RCSI), which prevents readers from conflicting with active write transactions.

---

Why Deadlocks Strike Excel Queries

A deadlock is not a simple timeout or slow query. It is a mathematical standstill.

In typical corporate reporting environments, deadlocks happen when:

1. Reporting Reads vs. Batch Writes: An Excel user refreshes an operational report (which requests Shared Locks across several tables) at the exact moment an ERP, POS system, or automated script performs updates (which requests Exclusive Locks on those same tables).
2. Opposite-Order Table Access: Transaction A updates Table 1 and requests access to Table 2. At the exact same microsecond, Transaction B (or an Excel data model refresh) updates Table 2 and requests access to Table 1.
3. Index Lock Escalation: When an Excel query scans a large, unindexed table, SQL Server escalates fine-grained row-level locks into coarse page or table-level locks, colliding directly with transactional system writes.

To evaluate overall server contention and query stability, see Managing SQL Query Stability: Fixing Timeouts and Resource Exhaustion..

---

Practical Diagnostic and Repair Steps

Method 1: Apply READ UNCOMMITTED (Client-Side Fix)

If your Excel workbook connects to SQL Server via a custom SQL statement in Power Query, you can avoid acquiring blocking Shared Locks by switching your session isolation level.

1. In Excel, go to Data > Queries & Connections.
2. Right-click the affected query and select Edit to open the Power Query Editor.
3. Open the Advanced Editor from the Home tab.
4. Modify your SQL statement to include SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; before your query: Source = Sql.Database("db-server.corp.local", "OperationsDB", [ Query = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT OrderID, CustomerID, TotalAmount FROM dbo.Orders WHERE OrderDate >= '2025-01-01';" ])
5. Click Done and refresh your query.

Alternatively, if you query specific tables directly in SQL, apply the WITH (NOLOCK) hint:

SELECT
    o.OrderID,
    c.CustomerName,
    o.TotalAmount
FROM dbo.Orders AS o WITH (NOLOCK)
INNER JOIN dbo.Customers AS c WITH (NOLOCK)
    ON o.CustomerID = c.CustomerID;

Note: Use this method only for analytical reports where reading rows currently being updated (“dirty reads”) will not compromise financial accuracy.

Method 2: Enable Read Committed Snapshot Isolation (Server-Side Fix)

The gold standard for preventing readers from deadlocking with writers is enabling Read Committed Snapshot Isolation (RCSI) on the database. Under RCSI, read queries pull previous row versions from the tempdb system database rather than placing locks on live data pages.

Have a Database Administrator execute the following command during a maintenance window:

-- Enables row-versioning for read queries
ALTER DATABASE OperationsDB
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

Once RCSI is active:

• Readers never block writers.
• Writers never block readers.
• Deadlocks caused by read-heavy Excel refreshes drop to near zero.

If enabling RCSI causes server disk bottlenecks due to high version-store volume, review Troubleshooting “TempDB” exhaustion on the server triggered by Excel refreshes..

Method 3: Standardize Access Order in Multi-Table Macros

If you run VBA macros that execute batch INSERT, UPDATE, or DELETE statements, deadlocks frequently arise because different scripts update database tables in arbitrary sequences.

• Standardize your update scripts so every procedure modifies tables in the exact same logical order (e.g., always update dbo.Customers first, then dbo.Orders, then dbo.OrderDetails).
• Keep macro transactions as short as possible. Commit your data changes immediately rather than holding open connections while running local VBA loops.

For deeper analysis of connection drops occurring during write competition, refer to Troubleshooting “Deadlocked” connections when Excel and SQL compete for resources..

---

Diagnostic Next Steps

If deadlocks continue after implementing non-locking reads:

• Check Execution Timing: If deadlocks occur during specific daily windows, review server scheduled maintenance or ETL batch jobs. If an unoptimized query stalls while waiting for locks, it may trigger timeouts. See Solving “Query Timeout” by adjusting the CommandTimeout parameter in M-code..
• Inspect Deadlock Graphs in SSMS: Ask your database administrator to configure an Extended Events session capturing the xml_deadlock_report. The resulting deadlock graph will pinpoint the exact SQL statement, execution thread, and resource lock that killed your Excel query.