SQL Database Blocking and RCSI in Microsoft Fabric
HOTSPOT
You have a SQL database in Microsoft Fabric that uses the default settings for a newly created database and contains a table named Sales.Orders.
You have an application that uses two stored procedures to access Sales.Orders.
While monitoring database activity, you discover the following:
• sys.dm_exec_requests shows multiple sessions in a suspended state with wait_type = LCK_M_X. All the sessions show the same wait_resource, which maps to Sales.Orders, and the same nonzero blocking_session_id.
• sys.dm_exec_input_buffer(blocking_session_id, NULL) returns a last submitted command of BEGIN TRANSACTION UPDATE Sales.Orders.
• sys.dm_exec_sessions for the blocking session shows status = sleeping and open_transaction_count = 1.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Community Insight
The key trap is assuming standard locking behavior; with RCSI enabled by default, readers use row versioning and do not acquire shared locks, preventing read-write blocking.
This scenario analyzes a blocking chain in Microsoft Fabric SQL using DMVs, leveraging Read Committed Snapshot Isolation (RCSI) to determine that SELECT statements are not blocked by UPDATE transactions.
Learners often incorrectly assume that any active transaction holding an exclusive lock will block concurrent SELECT queries, failing to account for the snapshot isolation features inherent in Azure SQL/Fabric.
Community Discussion (4 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Statement 1 is Yes because the blocking session hasopen_transaction_count = 1 and status sleeping, indicating an uncommitted explicit transaction holding locks. Statement 2 is No because Microsoft Fabric (Azure SQL Database) enables Read Committed Snapshot Isolation (RCSI) by default, allowing SELECTs to use row versions instead of acquiring shared locks. Statement 3 is No because sys.dm_exec_requests only tracks sessions with active requests; a sleeping session is not currently executing, so it won't appear there as the blocker.Why Other Candidate Answers Are Wrong
Selecting 'Yes' for Statement 2 ignores the fundamental change in locking behavior introduced by RCSI. Selecting 'Yes' for Statement 3 misunderstands the scope ofsys.dm_exec_requests, which filters out sleeping sessions regardless of their lock-holding status.Community Comment Notes
As BozaXephon noted, the explanation correctly identifies that RCSI prevents read-write blocking. The community consensus aligns with the verdict: Yes, No, No.Exam Strategy
Always check if the environment uses RCSI or SI before assuming blocking occurs between reads and writes. Understand that 'sleeping' sessions hold locks but do not appear in request-based monitoring views.
Frequently Asked Questions
Why does RCSI prevent SELECT from being blocked?
RCSI uses row versioning to provide a consistent snapshot for readers, eliminating the need for shared locks that would conflict with exclusive write locks.
How to identify a sleeping session causing blocking?
Check sys.dm_exec_sessions for open_transaction_count > 0 and status = sleeping, then link via blocking_session_id in sys.dm_exec_requests.