TRANSACTION_MUTEX and Multi-Session Transaction Access

I recently encountered high TRANSACTION_MUTEX accumulated wait time on a client system. I couldn’t recall a case where I saw this wait type as near the top of the “high waits” list and I was curious about what factors could push this type of overall wait time.

The Books Online definition of TRANSACTION_MUTEX is that it "occurs during synchronization of access to a transaction by multiple batches." Not many areas within the SQL Server engine expose this type of functionality, so my investigation was narrowed down to the following technologies:

• The deprecated sp_getbindtoken and sp_bindsession system stored procedures used to handle bound connections
• Distributed transactions
• MARS (Multiple Active Result Sets)

My goal was to test each technology and see if it influenced the TRANSACTION_MUTEX wait type.

The first test I performed used the deprecated sp_getbindtoken and sp_bindsession stored procedures. The sp_getbindtoken returns a transaction identifier which can then be used by sp_bindsession to bind multiple sessions together on the same transaction.

Before each test scenario, I made sure to clear my test SQL Server instance's wait statistics:

DBCC SQLPERF('waitstats', CLEAR);
GO

My test SQL Server instance was running SQL Server 2012 SP1 Developer Edition (11.0.3000). I used the Credit sample database, although you could use any other kind of sample database like AdventureWorks if you wanted to, as the schema and data distribution isn’t directly relevant to the subject of this article and wasn’t necessary in order to drive the TRANSACTION_MUTEX wait time.

sp_getbindtoken / sp_bindsession

In the first session window of SQL Server Management Studio, I executed the following code to begin a transaction and output the bind token for enlistment by the other planned sessions:

USE Credit;
GO

BEGIN TRANSACTION;

DECLARE @out_token varchar(255);

EXECUTE sp_getbindtoken @out_token OUTPUT;

SELECT @out_token AS out_token;
GO

This returned an @out_token of S/Z5_GOHLaGY<^i]S9LXZ-5---.fE---. In two separate SQL Server Management Studio query windows, I executed the following code to join to the existing sessions (accessing the shared transaction):

USE Credit;
GO

EXEC sp_bindsession 'S/Z5_GOHLaGY<^i]S9LXZ-5---.fE---';

And with the first session window still open, I started off the following loop to update the charge table’s table with a charge date equal to the current date and time, and then executed the same logic in the other two windows (three active sessions in the loop):

WHILE 1 = 1 
BEGIN
    UPDATE  dbo.charge
    SET     charge_dt = SYSDATETIME();
END

After a few seconds, I cancelled each executing query. Of the three sessions, only one was able to actually perform updates – even though the other two sessions were actively joined to the same transaction. And if I looked at the TRANSACTION_MUTEX wait type, I can see that it did indeed increment:

SELECT  [wait_type],
        [waiting_tasks_count],
        [wait_time_ms],
        [max_wait_time_ms],
        [signal_wait_time_ms]
FROM sys.dm_os_wait_stats
WHERE wait_type = 'TRANSACTION_MUTEX';

The results for this particular test were as follows:

wait_type            waiting_tasks_count   wait_time_ms   max_wait_time_ms   signal_wait_time_ms
TRANSACTION_MUTEX    2                     181732         93899              0

So I see that there were two waiting tasks (the two sessions that were simultaneously trying to update the same table via the loop). Since I hadn’t executed SET NOCOUNT ON, I was able to see that only the first executed UPDATE loop got changes in. I tried this similar technique using a few different variations (for example – four overall sessions, with three waiting) – and the TRANSACTION_MUTEX incrementing showed similar behavior. I also saw the TRANSACTION_MUTEX accumulation when simultaneously updating a different table for each session – so modifications against the same object in a loop wasn’t required in order to reproduce the TRANSACTION_MUTEX wait time accumulation.

Distributed transactions

My next test involved seeing if TRANSACTION_MUTEX wait time was incremented for distributed transactions. For this test, I used two SQL Server instances and a linked server connected between the two of them. MS DTC was running and properly configured, and I executed the following code that performed a local DELETE and a remote DELETE via the linked server and then rolled back the changes:

USE Credit;
GO

SET XACT_ABORT ON;

-- Assumes MS DTC service is available, running, properly configured
BEGIN DISTRIBUTED TRANSACTION;

DELETE [dbo].[charge] WHERE charge_no = 1;
DELETE [JOSEPHSACK-PC\AUGUSTUS].[Credit].[dbo].[charge] WHERE charge_no = 1;

ROLLBACK TRANSACTION;

The TRANSACTION_MUTEX showed no activity on the local server:

wait_type            waiting_tasks_count   wait_time_ms   max_wait_time_ms   signal_wait_time_ms
TRANSACTION_MUTEX    0                     0              0                  0

However the waiting tasks count was incremented on the remote server:

wait_type            waiting_tasks_count   wait_time_ms   max_wait_time_ms   signal_wait_time_ms
TRANSACTION_MUTEX    1                     0              0                  0

So my expectation to see this was confirmed – given that we have one distributed transaction with more than one session involved in some way with the same transaction.

MARS (Multiple Active Result Sets)

What about the use of Multiple Active Result Sets (MARS)? Would we also expect to see TRANSACTION_MUTEX accumulate when associated with MARS usage?

For this, I used the following C# console application code tested from Microsoft Visual Studio against my SQL Server 2012 instance and the Credit database. The logic of what I’m actually doing isn’t very useful (returns one row from each table), but the data readers are on the same connection and the connection attribute MultipleActiveResultSets is set to true, so it was enough to verify if indeed MARS could drive TRANSACTION_MUTEX accumulation as well:

string ConnString = @"Server=.;Database=Credit;Trusted_Connection=True;MultipleActiveResultSets=true;";
SqlConnection MARSCon = new SqlConnection(ConnString);

MARSCon.Open();

SqlCommand MARSCmd1 = new SqlCommand("SELECT payment_no FROM dbo.payment;", MARSCon);
SqlCommand MARSCmd2 = new SqlCommand("SELECT charge_no FROM dbo.charge;", MARSCon);

SqlDataReader MARSReader1 = MARSCmd1.ExecuteReader();
SqlDataReader MARSReader2 = MARSCmd2.ExecuteReader();

MARSReader1.Read();
MARSReader2.Read();

Console.WriteLine("\t{0}", MARSReader1[0]);
Console.WriteLine("\t{0}", MARSReader2[0]);

After executing this code, I saw the following accumulation for TRANSACTION_MUTEX:

wait_type            waiting_tasks_count   wait_time_ms   max_wait_time_ms   signal_wait_time_ms
TRANSACTION_MUTEX    8                     2              0                  0

So as you can see, the MARS activity (however trivially implemented) caused an uptick in the TRANSACTION_MUTEX wait type accumulation. And the connection string attribute itself doesn’t drive this, the actual implementation does. For example, I removed the second reader implementation and just maintained a single reader with MultipleActiveResultSets=true, and as expected, there was no TRANSACTION_MUTEX wait time accumulation.

Conclusion

If you are seeing high TRANSACTION_MUTEX waits in your environment, I hope this post gives you some insight into three avenues to explore - to determine both where these waits are coming from, and whether or not they are necessary.