Azure SQL Blocking from Sleeping Session with Open Transaction
You have an Azure SQL database that supports the OLTP workload of an order-processing application. 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?
Community Insight
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.
Learners often select B (deadlock) because they see blocking, but deadlocks result in immediate termination of one session, not long-term sleeping blocks.
Community Discussion (1 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
The key indicators areblocking_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' withopen_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?
Deadlocks are resolved immediately by killing one session. This session remains sleeping, indicating it is waiting, not deadlocked.
What does open_transaction_count=1 mean?
It means the session has begun a transaction but hasn't finished it, keeping all acquired locks active until commit or rollback.