Blocked on trip to MARS-CodePudding

The database tool I'm writing investigates blocked queries by running a parallel query against sys.dm_exec_requests if the main query got delayed to find the cause of the delay.

That works fine if the investigating connection has the VIEW SERVER STATE permission. If not, however, sys.dm_exec_requests only contains entries for the connection it runs on - which is somewhat pointless for connections where only one query can run at a time.

Enter MARS, the first time I was thinking this arcane feature may be useful for something.

With MARS enabled, I can run the investigating query on the same connection as the delayed query we're investigating.

However, a simple test shows that if the first MARS query is blocked, apparently the second one is also, even if the second has no reason to be.

I'm running this test code in LinqPad (with Dappper for a tighter code sample, but I got the same effect in my app that doesn't use Dapper):

var csb = new SqlConnectionStringBuilder();
csb.TrustServerCertificate = true;
csb.DataSource = @".\";
csb.InitialCatalog = "...";
csb.IntegratedSecurity = true;

using var c0 = new SqlConnection(csb.ConnectionString);

csb.MultipleActiveResultSets = true;

using var c1 = new SqlConnection(csb.ConnectionString);
using var c2 = new SqlConnection(csb.ConnectionString);

// Begin the blocking transaction on connection #0
await c0.QueryAsync(@"
begin transaction
select * from mytable with (tablockx, holdlock)
");

// This query on connection #1 is blocked by connection #0
var blockedTask = c1.QuerySingleAsync<int>("select count(*) from mytable");

// Strangely, this second query is blocked as well
var requests = await c1.QueryAsync(@"
select session_id, cpu_time, reads, logical_reads
from sys.dm_exec_requests r
");

// We don't get here unless you swap `c1` for `c2` in the last query, making
// it run on it's own connection, thus requiring VIEW SERVER STATE to be useful
requests.Dump();

await blockedTask;

You just need a database with any random table to apply this.

CodePudding user response：

MARS allows interleaved execution of multiple requests on the same connection, not concurrent execution.

In the case of a blocked SELECT query, other queries on the same connection cannot execute until the select query completes or yields by returning results.