static void

ADO.Net

Transactions

• For high-load read-write tables, you need transactions on the selects (otherwise they become deadlock victims). Use
  • SELECT * FROM Users WITH (NOLOCK)
  • In sql SET TRANSACTION LEVEL READ UNCOMMITTED;
  • A TransactionScope with IsolationLevel.ReadUncommitted
• If using .Net transactions: check overlap with TSQL BEGIN/END TRANSACTION
• Manual Transactions: SqlConnection.BeginTransaction returns a transaction object which must be attached to the commands. Change Transaction.IsolationLevel when writing data.
• 1.x Automatic Transactions: Uses COM+ DTC. Can use distributed databases. Classes must inherit from System.EnterpriseServices.ServicedComponent and add [Transaction] attributes with the options (TransactionOption, IsolationLevel etc).
  • ContextUtil.SetComplete and .SetAbort static methods (you can .SetAbort at the start, then .SetComplete only when done). Or use [AutoComplete] attribute on methods (but if you catch errors it won't detect the abort).

.Net 2 System.Transactions

• 2.0 System.Transactions: wrap ADO in using (TransactionScope tscope = new TransactionScope()) and at the end tscope.Complete= true (an exception leaves it false when it disposes, so aborts).
  As soon as a second transaction enlists, it is promoted to a distributed transaction under DTC
  • Default = new TransactionScope(TransactionScopeOption.Required) (if a higher transaction, joins it)
  • RequiresNew for new transaction that doesn't affect higher level transactions
  • Suppress for non-transactional bits (logging)
  • Set a timeout TimeSpan if could deadlock (or to test failure)
  • Default = TransactionOptions =IsolationLevel.Serializable.
  • You can explicitly create an ICommittableTransaction t= Transaction.Create(), call connection.EnlistTransaction, then t.Commit()

The WITH (READPAST) hint skips any locked rows while reading. It can conflict with default isolation levels (e.g. serializable; error is You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.). Fix by specifying something manually:

Transactions.IsolationLevel

MSDN

Name	Locks	Notes
ReadUncommitted	Write lock. No read locks (so dirty reads possible)	Lowest level.
ReadCommitted (default SQLServer, EF v1-5)	Short read locks (released as soon as read), write locks.	Read data may change before it is written.
Snapshot (default EF v6)	A form of readcommitted, but checks on commit for changes.	Throws exception if data has changed between read and write.
RepeatableRead	Read data locked until commit.	Read data cannot change but new data may be added before write.
Serializable (default ADO TransactionScope)	Locks fully. Cannot write when someone is reading it.	Highest level, so risk of lock blocking delays and deadlocks.

There's another level with the wonderful name IsolationLevel.Chaos which SQLServer doesn't support.

Snapshot must be enabled in the database: ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

Async/Await vs TransactionScope

TransactionScope is thread static, which is a problem with multi-threading and async/await. .net 4.5.1 + has new ctors on TransactionScope for TransactionScopeAsyncFlowOption.Enabled which make it work.