DP-800: Developing AI-Enabled Database Solutions

Question

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.

An application uses two stored procedures to access Sales.Orders.

While monitoring database activity, you discover:

sys.dm_exec_requests shows multiple sessions in a suspended state with wait_type = LCK_M_X. All sessions have 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 the last submitted command 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.

Yes or No

Statements	Yes	No
The blocking is caused by an uncommitted explicit transaction in the blocking session that is holding locks.
While an UPDATE operation on Sales.Orders is occurring, SELECT statements will be blocked.
Joining sys.dm_tran_locks to sys.dm_exec_requests will show which session holds locks involved in the blocking chain.

Explanation

An open explicit transaction retains the UPDATE’s exclusive locks until it commits or rolls back, even when the session is sleeping. READ_COMMITTED_SNAPSHOT is on by default for SQL database in Microsoft Fabric, so ordinary read-committed SELECT operations use row versions and do not block on the UPDATE’s exclusive locks. A sleeping session has no active request in sys.dm_exec_requests, so that join cannot reveal the sleeping session that retains the locks; session and transaction DMVs must also be used.

Learn more

DP-800: Developing AI-Enabled Database Solutions

AstroTutor

Notes

Readiness

QuestionQ101

Community Discussion