It's a bit late for answer :) but hope it will be useful for others.
Answer contains three parts:
- What does it mean "Transaction context in use by another session."
- How to reproduce error "Transaction context in use by another session."
1. What does it mean "Transaction context in use by another session."
Important notice: Transaction context lock is acquired just before and released immediately after interaction between SqlConnection and SQL Server.
When you execute some SQL Query, SqlConnection "looks" is there any transaction wrapping it. It may be SqlTransaction ("native" for SqlConnection) or Transaction from System.Transactions assembly.
When transaction found SqlConnection uses it to communicate with SQL Server and at the moment they communicate Transaction context is exclusively locked.
What does TransactionScope? It creates Transaction and provides .NET Framework Components infromation about it, so everyone including SqlConnection can (and by design should) use it.
So declaring TransactionScope we're creating new Transaction which is available to all "transactable" objects instantiated in current Thread.
Described error means the following:
- We created several
SqlConnections under the same TransactionContext (which means they related to the same transaction)
- We asked these
SqlConnection to communicate with SQL Server simultaneously
- One of them locked current
Transaction context and next one throwed error
2. How to reproduce error "Transaction context in use by another session."
First of all, transaction context is used ("locked") right at the time of sql command execution. So it's difficult to reproduce such a behavior for sure.
But we can try to do it by starting multiple threads running relatively long SQL operations under the single transaction.
Let's prepare table [dbo].[Persons] in [tests] Database:
USE [tests]
GO
DROP TABLE [dbo].[Persons]
GO
CREATE TABLE [dbo].[Persons](
[Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [nvarchar](1024) NOT NULL,
[Nick] [nvarchar](1024) NOT NULL,
[Email] [nvarchar](1024) NOT NULL)
GO
DECLARE @Counter INT
SET @Counter = 500
WHILE (@Counter > 0) BEGIN
INSERT [dbo].[Persons] ([Name], [Nick], [Email])
VALUES ('Sheev Palpatine', 'DarthSidious', '[email protected]')
SET @Counter = @Counter - 1
END
GO
And reproduce "Transaction context in use by another session." error with C# code based on Shrike code example
using System;
using System.Collections.Generic;
using System.Threading;
using System.Transactions;
using System.Data.SqlClient;
namespace SO.SQL.Transactions
{
public static class TxContextInUseRepro
{
const int Iterations = 100;
const int ThreadCount = 10;
const int MaxThreadSleep = 50;
const string ConnectionString = "Initial Catalog=tests;Data Source=.;" +
"User ID=testUser;PWD=Qwerty12;";
static readonly Random Rnd = new Random();
public static void Main()
{
var txOptions = new TransactionOptions();
txOptions.IsolationLevel = IsolationLevel.ReadCommitted;
using (var ctx = new TransactionScope(
TransactionScopeOption.Required, txOptions))
{
var current = Transaction.Current;
DependentTransaction dtx = current.DependentClone(
DependentCloneOption.BlockCommitUntilComplete);
for (int i = 0; i < Iterations; i++)
{
// make the transaction distributed
using (SqlConnection con1 = new SqlConnection(ConnectionString))
using (SqlConnection con2 = new SqlConnection(ConnectionString))
{
con1.Open();
con2.Open();
}
var threads = new List<Thread>();
for (int j = 0; j < ThreadCount; j++)
{
Thread t1 = new Thread(o => WorkCallback(dtx));
threads.Add(t1);
t1.Start();
}
for (int j = 0; j < ThreadCount; j++)
threads[j].Join();
}
dtx.Complete();
ctx.Complete();
}
}
private static void WorkCallback(DependentTransaction dtx)
{
using (var txScope1 = new TransactionScope(dtx))
{
using (SqlConnection con2 = new SqlConnection(ConnectionString))
{
Thread.Sleep(Rnd.Next(MaxThreadSleep));
con2.Open();
using (var cmd = new SqlCommand("SELECT * FROM [dbo].[Persons]", con2))
using (cmd.ExecuteReader()) { } // simply recieve data
}
txScope1.Complete();
}
}
}
}
And in conclusion a few words about implementing transaction support in your application:
- Avoid multi-threaded data operations if it's possible (no matter loading or saving). E.g. save
SELECT/UPDATE/ etc... requests in a single queue and serve them with a single-thread worker;
- In multi-threaded applications use transactions. Always. Everywhere. Even for reading;
- Don't share single transaction between multiple threads. It causes strange, unobvious, transcendental and not reproducible error messages:
- "Transaction context in use by another session.": multiple simultaneous interactions with server under one transaction;
- "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.": not dependent transactions were completed;
- "The transaction is in doubt.";
- ... and I assume a lot of other ...
- Don't forget to set Isolation Level for
TransactionScope. Default is Serializable but in most cases ReadCommitted is enough;
- Don't forget to Complete()
TransactionScope and DependentTransaction
