Azure SQL Blocking from Sleeping Session with Open Transaction
Short answer
C An explicit transaction was started but not committed or rolled back.
Question
During a 10-minute incident window, you run a dynamic management view query and discover the following:
Session 72 is sleeping with open_transaction_count = 1.
Multiple other sessions show blocking_session_id = 72 in sys.dm_exec_requests.
sys.dm_exec_input_buffer(72, NULL) returns only BEGIN TRANSACTION UPDATE Sales.Orders.
Users report that updates to Sales.Orders intermittently time out during the incident window. The timeouts stop only after you manually terminate session 72.
What is a possible cause of the blocking?
Key Takeaways
This question tests your ability to interpret sys.dm_exec_requests and input buffers for blocking sessions, specifically recognizing that 'sleeping' with 'open_transaction_count > 0' indicates an orphaned transaction.
Identifies the root cause of blocking in Azure SQL when a sleeping session holds an open transaction. The correct answer is C because the DMV evidence points to an uncommitted explicit transaction.
Common mistake: Learners often select B (deadlock) because they see blocking, but deadlocks result in immediate termination of one session, not long-term sleeping blocks.
Expert Analysis
Why the Answer Is Correct
The key indicators are blocking_session_id = 72, session_state = sleeping, and open_transaction_count = 1. In SQL Server and Azure SQL, a session in a 'sleeping' state with an open transaction count greater than zero is holding locks acquired during that transaction. Since the input buffer shows only BEGIN TRANSACTION UPDATE, it confirms an explicit transaction was started but never committed or rolled back. This causes other sessions trying to modify Sales.Orders to wait indefinitely until the lock is released.
Why the Other Options Are Wrong
Option A is incorrect because a long-running SELECT would typically show as 'running' if active, or 'sleeping' with open_transaction_count = 0 if it finished. Option B is incorrect because deadlocks are detected immediately by the engine, which kills one session (the victim), rather than leaving it sleeping. Option D is incorrect because lock escalation changes lock granularity but does not inherently cause a session to sleep with an open transaction count unless the transaction itself is uncommitted.
Community Comment Notes
As gangreja noted, the combination of 'sleeping + open_transaction_count > 0 + blocking others' is the definitive signature of an orphaned open transaction that blocks until someone commits, rolls back or kills the session.
Exam Strategy
When analyzing blocking issues, always check the session state and transaction count first. If a session is sleeping but has an open transaction, it is likely the blocker holding locks unexpectedly.
Frequently Asked Questions
Why isn't this a deadlock?
What does open_transaction_count=1 mean?
Discussion 1 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 1
-
gangreja 👍 1 Answered Csleeping + open_transaction_count > 0 + blocking others means an orphaned open transaction and it blocks until someone commits, rolls back or kills the session
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.