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:
using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadCommitted
}))
Transactions.IsolationLevel
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.
var opts = new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead };
using (var tx = new TransactionScope(TransactionScopeOption.Required, opts, TransactionScopeAsyncFlowOption.Enabled))
{