SQL Server deadlocks and blocking are among the most difficult database performance problems to diagnose in production enterprise applications. A query that is normally fast can appear to hang when another transaction is holding a lock, while a deadlock occurs when concurrent transactions wait on resources held by one another.
These problems matter in systems that combine SQL Server with .NET data-access layers and long-running business workflows. A document management application, for example, may simultaneously read document records, update approval state, create document versions, write audit information and stream large document content. The same tables can therefore be used by several workflows at the same time.
This article uses the Muziho document management DAL as a
practical reference. The supplied code uses Entity Framework 6,
direct ADO.NET and SQL Server, with workflows around
tblDocuments, approval status, document versions,
client accounts, deletion logs and binary document processing.
The examples are generalized and do not expose environment
credentials.
- Distinguish blocking from a true SQL Server deadlock before changing code.
- Keep transactions short and out of long file or network operations.
- Access shared resources in a consistent order across workflows.
- Use execution plans, DMVs and deadlock information to locate contention.
- Reduce unnecessary scans, repeated queries and oversized result sets.
- Design document approval, versioning and audit operations as focused database work.
1. SQL Server Blocking vs Deadlocks: What Is the Difference?
Blocking occurs when one session holds a lock and another session must wait. A deadlock is a circular wait: session A is waiting for a resource held by session B while session B is waiting for a resource held by session A.
Blocking
Session A
│
└── holds a lock
↓
Session B waits
Deadlock
Session A
│
└── holds Resource 1
↓
waits for Resource 2
↑
holds Resource 2
│
Session B
↓
waits for Resource 1
The distinction matters. Blocking may be resolved by shortening a transaction or fixing a long-running query, while a repeatable deadlock requires the circular resource dependency to be addressed.
2. Why Document Management Workflows Can Experience Contention
The Muziho DAL contains several workflows operating on
tblDocuments: checking approval state,
changing approval fields, creating document versions,
retrieving account documents and removing documents.
Document workflow
↓
Read document state
↓
Update approval/version state
↓
SaveChanges()
↓
Write related audit information
None of these operations proves that a deadlock exists in the system. The important point is that concurrent reads and writes against shared rows need an explicit concurrency strategy.
3. Keep SQL Server Transactions Short
Locks can remain in place for the lifetime required by a transaction. The longer the transaction remains active, the greater the chance that another session will wait.
BEGIN TRANSACTION
Update document
↓
Process file
↓
Call external service
↓
Write audit record
COMMIT
A better design is to keep database work inside a focused transaction and perform slow file or network operations outside the critical section whenever business rules allow.
4. Be Careful When One Workflow Uses Multiple DbContexts
The supplied DAL creates
kiasolut_docEntities instances in many helper
methods. The document deletion workflow also calls a
deletion-log method that creates a separate context.
dmsEntities.tblDocuments.Remove(document);
dmsEntities.SaveChanges();
// Related audit work uses another context
SaveDeletionLog(document);
Multiple contexts are not automatically wrong. The concern is transaction ownership: related operations are harder to reason about when they are split across different contexts, especially when several workflows touch the same tables.
5. Access Shared Resources in a Consistent Order
One practical deadlock-reduction technique is to establish a consistent order for accessing related resources.
Workflow A
tblDocuments
↓
tblDocumentDeletionLogs
Workflow B
tblDocumentDeletionLogs
↓
tblDocuments
Under concurrent execution, inconsistent ordering can create a circular wait when the operations participate in compatible transaction scopes. A consistent access order makes the concurrency behavior easier to reason about.
6. Protect Document Approval State Transitions
The DAL includes checker and approver operations that load a document when approval state is still pending and then update approval fields.
var document =
(from d in dmsEntities.tblDocuments
where d.ID == @DocumentId
&& d.IsApprovedByApprover == null
select d)
.SingleOrDefault();
if (document != null)
{
document.IsApprovedByApprover = true;
document.ApproverID = @UserId;
dmsEntities.SaveChanges();
}
The important requirement is that two concurrent approvers should not both believe they own the same pending state. Concurrency protection should therefore apply to the state transition, not only to the user interface.
7. Document Versioning Can Create Race Conditions
The DAL determines the most recent document version and uses that value when creating a new document. This read-then-insert sequence is sensitive to concurrent writes.
Request A
Read latest version = 2.0
↓
Create 3.0
Request B
Read latest version = 2.0
↓
Create 3.0
If two requests run concurrently, both can observe the same latest version unless the database design or transaction strategy protects the operation.
Treat version uniqueness as a database concern as well as an application concern. A suitable uniqueness rule can prevent duplicate versions even when requests arrive at the same time.
8. Repeated Queries Increase Database Work
Several DAL workflows load document collections and then call additional helper methods for individual documents, accounts or signatory records. That can turn one screen operation into many database round trips.
Load checklist
↓
Query signatories
↓
Query latest version
↓
Query approval state
↓
Query rejection reason
↓
Repeat for another document
Even when each query is individually fast, the cumulative workload can increase CPU, I/O and the time needed to complete concurrent transactions. Reducing unnecessary round trips is therefore both a query-performance and a concurrency consideration.
9. Large Scans Can Extend Resource Usage
Some supplied methods retrieve collections and then perform additional filtering or checks in application code. When the same result can be filtered in SQL Server, moving the predicate into the query can reduce unnecessary work.
var documents =
(from d in dmsEntities.tblDocuments
where d.AccountNumber == @AccountNumber
select d)
.ToList();
Smaller result sets can reduce database I/O, application memory use and the time a workflow spends processing the returned records.
10. Index High-Frequency Document Queries
Indexes do not eliminate deadlocks by themselves, but appropriate indexes can reduce the number of rows SQL Server must examine for frequent document-management queries.
SELECT
ID,
MasterDocID,
AccountNumber,
SignatoryID,
Version
FROM dbo.tblDocuments
WHERE AccountNumber = @AccountNumber
AND MasterDocID = @MasterDocID
AND SignatoryID = @SignatoryID;
Review the actual execution plan and existing indexes before adding a new index. Extra indexes also increase write and maintenance cost.
11. Avoid SELECT * for Large Document Rows
Document tables may contain binary content in addition to metadata and workflow state. Selecting every column for a metadata-only operation can increase I/O and data transfer.
SELECT
ID,
UploadedName,
Version,
IsApproved,
IsApprovedByApprover
FROM dbo.tblDocuments
WHERE ID = @DocumentId;
When a screen only needs workflow metadata, do not retrieve the document binary at the same time.
12. Treat Large Document Blobs as a Separate Concern
The DAL contains direct ADO.NET code that reads document
binary data in chunks and writes it to a file stream. The
source allocates a fixed buffer and repeatedly calls
GetBytes to process the document.
var buffer = new byte[bufferSize];
long currentIndex = 0;
long bytesReturned;
while ((bytesReturned =
reader.GetBytes(
blobColumnIndex,
currentIndex,
buffer,
0,
buffer.Length)) > 0)
{
writer.Write(
buffer,
0,
(int)bytesReturned);
currentIndex += bytesReturned;
}
Large document streaming should not unnecessarily extend a transaction holding locks on shared business rows. Keep binary transfer and metadata state changes separated where the workflow permits it.
13. A Large Command Timeout Does Not Fix Blocking
The supplied document-processing code sets a very large command timeout. That controls how long the client waits; it does not make the SQL operation execute faster.
command.CommandTimeout =
1800;
When an operation takes unexpectedly long, determine whether SQL Server is executing slowly or whether the session is waiting for another session. Those are different problems and require different fixes.
14. Diagnose SQL Server Blocking with DMVs
Dynamic management views can help identify sessions that are waiting for another session.
SELECT
r.session_id,
r.blocking_session_id,
r.status,
r.wait_type,
r.wait_time,
r.wait_resource
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id > 0;
Start with the blocking session and determine what it is doing, how long it has been active and which transaction owns the resource being waited on.
15. Inspect SQL Server Locks
Lock information can connect a waiting request to the database resource involved.
SELECT
request_session_id,
resource_type,
resource_database_id,
request_mode,
request_status
FROM sys.dm_tran_locks;
Combine lock information with active requests and the SQL text or execution plan to understand the complete wait chain.
16. Capture the Actual Deadlock Graph
A deadlock graph is far more informative than a generic timeout message. It can show the participating sessions, resources held, resources requested and the transaction selected as the deadlock victim.
Session A
holds Key A
requests Key B
Session B
holds Key B
requests Key A
SQL Server
↓
Detects circular wait
↓
Chooses a deadlock victim
Use SQL Server Extended Events or the diagnostic facilities available in your environment to capture the actual deadlock information rather than guessing from symptoms.
17. Use Query Store to Find Performance Trends
Query Store can help compare query performance and execution plans over time. It is useful when a document-management query becomes slower after data growth, a deployment or an index change.
Compare common workloads such as document search, approval queues, account lookups and version queries to determine whether the problem is isolated to a particular query plan.
18. Understand Entity Framework 6 Transaction Behavior
The supplied Muziho DAL uses Entity Framework 6 and
frequently calls SaveChanges() after modifying
an entity.
using (var db =
new kiasolut_docEntities())
{
document.IsSentForApproval = true;
db.SaveChanges();
}
When diagnosing blocking, understand the SQL generated by the ORM, the transaction boundaries and the rows affected by the change. Entity Framework does not bypass SQL Server's locking rules.
19. Entity Framework 6 and ADO.NET Can Share SQL Server
The DAL uses both Entity Framework and direct
SqlConnection, SqlCommand and
SqlDataReader.
Document state and metadata
↓
Entity Framework 6
Large document retrieval
↓
ADO.NET / SqlDataReader
↓
SQL Server
Mixing data-access techniques is not inherently a problem. The critical consideration is that their transaction boundaries and lock behavior remain understandable when they participate in related workflows.
20. The Same SQL Server Problems Exist in Dapper Applications
The same principles apply to modern ASP.NET Core and Dapper applications. Dapper is lightweight, but SQL Server still controls locking, blocking and transaction behavior.
var document =
connection.QuerySingleOrDefault<DocumentSummary>(
"""
SELECT
ID,
MasterDocID,
AccountNumber,
Version
FROM dbo.tblDocuments
WHERE ID = @DocumentId
""",
new
{
DocumentId = documentId
});
In a Razor .cshtml article source, the SQL
parameter is written as @DocumentId so the
rendered article displays @DocumentId.
21. Retry Deadlocks Only When the Operation Is Safe to Retry
SQL Server can terminate one participant in a deadlock so the other transaction can proceed. For operations that are safe to repeat, controlled retry logic can be part of the resilience strategy.
for (var attempt = 1; attempt <= 3; attempt++)
{
try
{
await ExecuteDatabaseOperationAsync(
cancellationToken);
break;
}
catch (SqlException ex)
when (IsDeadlock(ex) && attempt < 3)
{
await Task.Delay(
TimeSpan.FromMilliseconds(
attempt * 200),
cancellationToken);
}
}
Retry is not a substitute for fixing a repeatable deadlock. It should be used only after considering idempotency and transaction semantics.
22. Keep Approval, Versioning and Audit Changes in a Clear Order
When a document workflow touches multiple records, make the order of database operations explicit.
1. Validate document state
2. Update document state
3. Record audit information
4. Commit
On transient failure:
5. Roll back / retry when safe
Avoid hidden database work inside helper methods when it makes the transaction boundary difficult to understand.
23. Review These Patterns in the Muziho DAL
Several source patterns are especially valuable for a performance and concurrency review:
- Document state checks followed by updates.
- Read-latest-version followed by insert-new-version workflows.
- Repeated helper queries while building document lists.
- Separate contexts for deletion and deletion logging.
- Large document reads using ADO.NET streaming.
- Long command timeouts on file-processing operations.
These patterns do not prove that the application has a particular deadlock. They identify areas where transaction scope, indexing, query shape and concurrency behavior deserve measurement.
SQL Server Deadlock and Blocking Troubleshooting Checklist
- Is the observed issue blocking, deadlocking or slow execution?
- Which session owns the resource being waited on?
- What transaction and SQL statement are involved?
- Are large scans or missing indexes extending query duration?
- Are transactions holding locks during file or network operations?
- Do concurrent workflows access shared tables in different orders?
- Are multiple DbContexts involved in one logical operation?
- Can document version creation race under concurrent requests?
- Can approval state transitions be performed twice?
- Can a safe operation be retried after a deadlock victim error?
Conclusion
SQL Server deadlocks and blocking are concurrency problems, not simply slow-query problems. The most effective diagnosis starts by identifying the waiting sessions, locks, transactions and SQL statements involved.
The Muziho document-management DAL provides useful enterprise examples: document approval, versioning, deletion logging, account lookups and large document processing all interact with SQL Server while the application uses both Entity Framework 6 and direct ADO.NET.
The practical approach is to keep transactions focused, reduce unnecessary database work, align indexes with real queries, establish predictable access ordering and use SQL Server diagnostics to investigate the actual contention. The same principles apply to modern ASP.NET Core, Dapper and other .NET applications using SQL Server.