static void

ADO.Net

Transactions

.Net 2 System.Transactions

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:

using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions
{
    IsolationLevel = IsolationLevel.ReadCommitted
}))

Transactions.IsolationLevel

MSDN

NameLocksNotes
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.

var opts = new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead };
using (var tx = new TransactionScope(TransactionScopeOption.Required, opts, TransactionScopeAsyncFlowOption.Enabled))
{