public void TestScopedToTransactionOnly()
{
using var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString);
connection.Open();
using (var transaction = connection.BeginTransaction())
using (TransactionProvider.UseTransaction(transaction))
using (var transactionEngine = new TEngineFactory().Create <TTransactionProvider>())
{
var lockName = nameof(TestScopedToTransactionOnly);
using (transactionEngine.CreateLock(lockName).Acquire())
{
using (var handle = transactionEngine.CreateLock(lockName).TryAcquire())
{
(handle != null).ShouldEqual(transactionEngine.IsReentrant, "reentrant: " + this.GetType().Name);
}
using (ConnectionProvider.UseConnection(connection))
using (var connectionEngine = new TEngineFactory().Create <DefaultClientConnectionProvider>())
{
Assert.Catch <InvalidOperationException>(() => connectionEngine.CreateLock(lockName).TryAcquire());
}
}
}
}
public void TestCloseLockOnClosedConnection()
{
using (var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString))
using (ConnectionProvider.UseConnection(connection))
using (var connectionEngine = new TEngineFactory().Create <TConnectionProvider>())
using (var connectionStringEngine = new TEngineFactory().Create <DefaultConnectionStringProvider>())
{
var connectionStringLock = connectionStringEngine.CreateLock(nameof(TestCloseLockOnClosedConnection));
var @lock = connectionEngine.CreateLock(nameof(TestCloseLockOnClosedConnection));
Assert.Catch <InvalidOperationException>(() => @lock.Acquire());
connection.Open();
var handle = @lock.Acquire();
connectionStringLock.IsHeld().ShouldEqual(true, this.GetType().Name);
connection.Dispose();
Assert.DoesNotThrow(handle.Dispose);
// lock can be re-acquired
connectionStringLock.IsHeld().ShouldEqual(false);
}
}
public void TestConnectionDoesNotLeak()
{
var applicationName = nameof(TestConnectionDoesNotLeak) + Guid.NewGuid();
var connectionString = new SqlConnectionStringBuilder(ConnectionStringProvider.ConnectionString)
{
ApplicationName = applicationName,
}
.ConnectionString;
using (ConnectionStringProvider.UseConnectionString(connectionString))
using (var engine = this.CreateEngine())
{
var @lock = engine.CreateLock(nameof(TestConnectionDoesNotLeak));
for (var i = 0; i < 30; ++i)
{
using (@lock.Acquire())
{
CountActiveSessions().ShouldEqual(1, this.GetType().Name);
}
// still alive due to pooling
CountActiveSessions().ShouldEqual(1, this.GetType().Name);
}
}
using (var connection = SqlHelpers.CreateConnection(connectionString))
{
SqlTestHelper.ClearPool(connection);
// checking immediately seems flaky; likely clear pool finishing
// doesn't guarantee that SQL will immediately reflect the clear
var maxWaitForPoolsToClear = TimeSpan.FromSeconds(5);
var stopwatch = Stopwatch.StartNew();
do
{
var activeCount = CountActiveSessions();
if (activeCount == 0)
{
return;
}
Thread.Sleep(25);
}while (stopwatch.Elapsed < maxWaitForPoolsToClear);
}
int CountActiveSessions()
{
using var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString);
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = $@"SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE program_name = '{applicationName}'";
return((int)command.ExecuteScalar());
}
}
public IdleSessionKiller(string connectionString, TimeSpan idleTimeout)
{
this.cancellationTokenSource = new CancellationTokenSource();
var cancellationToken = this.cancellationTokenSource.Token;
this.task = Task.Run(async() =>
{
var applicationName = new SqlConnectionStringBuilder(connectionString).ApplicationName;
while (!cancellationToken.IsCancellationRequested)
{
using var connection = SqlHelpers.CreateConnection(connectionString);
await connection.OpenAsync(cancellationToken);
var spidsToKill = new List <short>();
using (var findIdleSessionsCommand = connection.CreateCommand())
{
var expirationDate = DateTime.Now - idleTimeout;
findIdleSessionsCommand.CommandText = @"
SELECT session_id FROM sys.dm_exec_sessions
WHERE session_id != @@SPID
AND login_name != 'sa'
AND (last_request_start_time IS NULL OR last_request_start_time <= @expirationDate)
AND (last_request_end_time IS NULL OR last_request_end_time <= @expirationDate)";
findIdleSessionsCommand.Parameters.Add(findIdleSessionsCommand.CreateParameter("expirationDate", expirationDate));
try
{
using var reader = await findIdleSessionsCommand.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
spidsToKill.Add(reader.GetInt16(0));
}
}
catch (DbException) when(cancellationToken.IsCancellationRequested)
{
cancellationToken.ThrowIfCancellationRequested();
}
}
foreach (var spid in spidsToKill)
{
using var killCommand = connection.CreateCommand();
killCommand.CommandText = "KILL " + spid;
try { await killCommand.ExecuteNonQueryAsync(); }
catch (Exception ex) { Console.WriteLine($"Failed to kill {spid}: {ex}"); }
}
await Task.Delay(TimeSpan.FromTicks(idleTimeout.Ticks / 2), cancellationToken);
}
});
}
public void TestIsNotScopedToTransaction()
{
using (var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString))
using (ConnectionProvider.UseConnection(connection))
using (var connectionEngine = new TEngineFactory().Create <TConnectionProvider>())
using (var connectionStringEngine = new TEngineFactory().Create <DefaultConnectionStringProvider>())
{
connection.Open();
using var handle = connectionEngine.CreateLock(nameof(TestIsNotScopedToTransaction)).Acquire();
using (var transaction = connection.BeginTransaction())
{
transaction.Rollback();
}
connectionStringEngine.CreateLock(nameof(TestIsNotScopedToTransaction)).IsHeld().ShouldEqual(true, this.GetType().Name);
}
}
public void TestIsolationLevelLeakage()
{
const string IsolationLevelQuery = @"
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'RepeatableRead'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS isolationLevel
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID";
var connectionString = new SqlConnectionStringBuilder(ConnectionStringProvider.ConnectionString)
{
ApplicationName = nameof(TestIsolationLevelLeakage),
// makes it easy to test for leaks since all connections are the same
MaxPoolSize = 1,
}
.ConnectionString;
using (var connection = SqlHelpers.CreateConnection(connectionString)) { SqlTestHelper.ClearPool(connection); }
using var engine = new TEngineFactory().Create <TransactionBasedConnectionStringProvider>();
var @lock = engine.CreateLock(nameof(TestIsolationLevelLeakage));
@lock.Acquire().Dispose();
using (var connection = SqlHelpers.CreateConnection(connectionString))
{
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = IsolationLevelQuery;
command.ExecuteScalar().ShouldEqual(IsolationLevel.ReadCommitted.ToString());
}
@lock.AcquireAsync().Result.Dispose();
using (var connection = SqlHelpers.CreateConnection(connectionString))
{
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = IsolationLevelQuery;
command.ExecuteScalar().ShouldEqual(IsolationLevel.ReadCommitted.ToString());
}
}
public void TestCloseTransactionLockOnClosedTransaction()
{
using var connectionStringEngine = new TEngineFactory().Create <DefaultConnectionStringProvider>();
using var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString);
connection.Open();
var lockName = nameof(TestCloseTransactionLockOnClosedTransaction);
IDisposable handle;
using (var transaction = connection.BeginTransaction())
using (TransactionProvider.UseTransaction(transaction))
using (var transactionEngine = new TEngineFactory().Create <TTransactionProvider>())
{
handle = transactionEngine.CreateLock(lockName).Acquire();
connectionStringEngine.CreateLock(lockName).IsHeld().ShouldEqual(true);
}
Assert.DoesNotThrow(handle.Dispose);
connectionStringEngine.CreateLock(lockName).IsHeld().ShouldEqual(false);
}
private void TestLockOnCompletedTransactionHelper(Action <DbTransaction> complete, [CallerMemberName] string lockName = "")
{
using var connectionStringEngine = new TEngineFactory().Create <DefaultConnectionStringProvider>();
using var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString);
connection.Open();
using (var transaction = connection.BeginTransaction())
using (TransactionProvider.UseTransaction(transaction))
using (var transactionEngine = new TEngineFactory().Create <TTransactionProvider>())
{
var handle = transactionEngine.CreateLock(lockName).Acquire();
connectionStringEngine.CreateLock(lockName).IsHeld().ShouldEqual(true);
complete(transaction);
Assert.DoesNotThrow(handle.Dispose);
connectionStringEngine.CreateLock(lockName).IsHeld().ShouldEqual(false, this.GetType().Name);
Assert.Catch <InvalidOperationException>(() => transactionEngine.CreateLock(lockName).Acquire());
}
}
public sealed override ConnectionInfo GetConnectionInfo()
{
var currentTransaction = Current <DbTransaction> .Value;
if (currentTransaction != null)
{
return(new ConnectionInfo {
Transaction = currentTransaction
});
}
var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString);
this.RegisterCleanupAction(CreateWeakDisposeAction(connection));
connection.Open();
var transaction = connection.BeginTransaction();
this.RegisterCleanupAction(CreateWeakDisposeAction(transaction));
return(new ConnectionInfo {
Transaction = transaction
});
}
public void TestCloseTransactionLockOnClosedConnection()
{
using var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString);
connection.Open();
using (var transaction = connection.BeginTransaction())
using (TransactionProvider.UseTransaction(transaction))
using (var transactionEngine = new TEngineFactory().Create <TTransactionProvider>())
using (var connectionStringEngine = new TEngineFactory().Create <DefaultConnectionStringProvider>())
{
var lockName = nameof(TestCloseTransactionLockOnClosedConnection);
var @lock = transactionEngine.CreateLock(lockName);
var handle = @lock.Acquire();
// use connectionStringEngine to avoid reentrance
connectionStringEngine.CreateLock(lockName).IsHeld().ShouldEqual(true);
connection.Dispose();
Assert.DoesNotThrow(handle.Dispose);
// lock can be re-acquired
connectionStringEngine.CreateLock(lockName).IsHeld().ShouldEqual(false);
}
}
protected override DbConnection CreateConnection(string connectionString) => SqlHelpers.CreateConnection(connectionString);
public MultiplexedConnectionLock(string connectionString)
{
this.connection = SqlHelpers.CreateConnection(connectionString);
}