Transactions

Default behavior

All TinyORM commands (QueryAsync, QueryMultipleAsync, etc.) run in a separate READ COMMITTED transaction by default if no outer transaction is declared. If an outer transaction is already declared, it will be joined by default.

All queries run with NOCOUNT=ON and XACT_ABORT=ON by default (can be turned off in SQL).

var sql = "SELECT transaction_id FROM sys.dm_tran_current_transaction; SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID";
var q1 = await db.QueryMultipleAsync(sql);
var q2 = await db.QueryMultipleAsync(sql);

{ Console.WriteLine("{0} {1}", q1[0][0], q1[1][0]); }
{ Console.WriteLine("{0} {1}", q2[0][0], q2[1][0]); }

/* "2" is READ COMMITTED
[transaction_id, 38185] [transaction_isolation_level, 2]
[transaction_id, 38188] [transaction_isolation_level, 2]
*/

Declarative ambient transactions

var sql = "SELECT transaction_id FROM sys.dm_tran_current_transaction; SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID";
using (var ts = DbContext.CreateTransactionScope())
{
	var q1 = await db.QueryMultipleAsync(sql);
	var q2 = await db.QueryMultipleAsync(sql);

	{ Console.WriteLine("{0} {1}", q1[0][0], q1[1][0]); }
	{ Console.WriteLine("{0} {1}", q2[0][0], q2[1][0]); }

	ts.Complete();
}
/*
[transaction_id, 41154] [transaction_isolation_level, 2]
[transaction_id, 41154] [transaction_isolation_level, 2]
*/

Note:

Do not use var ts = new TransactionScope() pattern - it will create a SERIALIZABLE transaction, which is almost never what you want (will likely severely degrade parallel transaction performance and throughput). Treat new TransactionScope() as an anti-pattern, and always use DbContext.CreateTransactionScope() instead, which defaults to a sane READ COMMITTED isolation level. It's another case of poor Microsoft API design with the best intentions...

Changing Isolation Level

var sql = "SELECT transaction_id FROM sys.dm_tran_current_transaction; SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID";
using (var ts = DbContext.CreateTransactionScope(
	TransactionScopeOption.Required,
	new TransactionOptions { IsolationLevel = IsolationLevel.Serializable }))
{
	var q1 = await db.QueryMultipleAsync(sql);
	var q2 = await db.QueryMultipleAsync(sql);

	{ Console.WriteLine("{0} {1}", q1[0][0], q1[1][0]); }
	{ Console.WriteLine("{0} {1}", q2[0][0], q2[1][0]); }

	ts.Complete();
}
/* "4" is SERIALIZABLE
[transaction_id, 42943] [transaction_isolation_level, 4]
[transaction_id, 42943] [transaction_isolation_level, 4]
*/

Concurrent independent transactions

// 'outerScope' is some preexisting ambient transaction beyond your control
using (var outerScope = DbContext.CreateTransactionScope())
{
	var start = DateTime.UtcNow;
	var q1Task = Task.Run(async () =>
	{
		using (var ts1 = DbContext.CreateTransactionScope(TransactionScopeOption.RequiresNew))
		{
			var result = await db.QueryAsync("WAITFOR DELAY '00:00:02'; SELECT [Answer] = 2;");
			ts1.Complete();
			return result;
		}
	});

	var q2Task = Task.Run(async () =>
	{
		using (var ts2 = DbContext.CreateTransactionScope(TransactionScopeOption.RequiresNew))
		{
			var result = await db.QueryAsync("WAITFOR DELAY '00:00:02'; SELECT [Answer] = 3;");
			ts2.Complete();
			return result;
		}
	});

	await Task.WhenAll(q1Task, q2Task);
	var end = DateTime.UtcNow;
	var time = (end - start);
	Console.WriteLine("Sum: {0}", q1Task.Result[0].Answer + q2Task.Result[0].Answer);
	Console.WriteLine($"{time.TotalSeconds} seconds.");
	outerScope.Complete();
}
// Sum: 5
// 2.0021145 seconds.

Query timeouts

{
	var result = await db.QueryAsync("WAITFOR DELAY '00:00:02'; SELECT [Answer] = 2;"); // default timeout
	Console.WriteLine(result[0]);
}
// [Answer, 2] after 2-second wait

{
	var result = await db.QueryAsync("WAITFOR DELAY '00:00:02'; SELECT [Answer] = 2;", commandTimeout: 1);
	Console.WriteLine(result[0]);
}
// throws SqlException "Execution Timeout Expired" after 1 second