DP-800: Developing AI-Enabled Database Solutions

By Microsoft · Question Mode

QuestionQ55

VerifiedSecure, optimize, and deploy database solutions

You have an Azure SQL database that supports the OLTP workload for an order-processing application.

During a 10-minute incident window, you run a dynamic management view query and find the following:

  • Session 72 is sleeping with open_transaction_count = 1.
  • Multiple other sessions display 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 could be causing the blocking?

Explanation

An explicit transaction begun with BEGIN TRANSACTION remains active until COMMIT or ROLLBACK. Locks acquired by the UPDATE Sales.Orders can therefore remain held while the session is sleeping, blocking conflicting updates until the transaction is ended or the session is terminated.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!