Transactions best practices

Question

How much do you rely on database transactions? Do you prefer small or large transaction scopes ? Do you prefer client side transaction handling (e.g. TransactionScope in .NET) over server side transactions or vice-versa? What about nested transactions? Do you have some tips&tricks related to transactions ? Any gotchas you encountered working with transaction ? All sort of answers are welcome.

Accepted Answer

I always wrap a transaction in a using statement.

```
using(IDbTransaction transaction )
{
// logic goes here.
   transaction.Commit();
}
```

Once the transaction moves out of scope, it is disposed. If the transaction is still active, it is rolled back. This behaviour fail-safes you from accidentally locking out the database. Even if an unhandled exception is thrown, the transaction will still rollback.

In my code I actually omit explicit rollbacks and rely on the using statement to do the work for me. I only explicitly perform commits.

I've found this pattern has drastically reduced record locking issues.

Transactions best practices

Solution

Problem