Transactions best practices [closed]

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.

Answer 1

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.

Answer 2

Personally, developing a website that is high traffic perfomance based, I stay away from database transactions whenever possible. Obviously they are neccessary, so I use an ORM, and page level object variables to minimize the number of server side calls I have to make.

Nested transactions are an awesome way to minimize your calls, I steer in that direction whenever I can as long as they are quick queries that wont cause locking. NHibernate has been a savior in these cases.

Answer 3

I use transactions on every write operation to the database.
So there are quite a few small "transactions" wrapped in a larger transaction and basically there is an outstanding transaction count in the nesting code. If there are any outstanding children when you end the parent, its all rolled back.

I prefer client-side transaction handling where available. If you are relegated to doing sps or other server side logical units of work, server side transactions are fine.

Answer 4

Wow! Lots of questions!

Until a year ago I relied 100% on transactions. Now its only 98%. In special cases of high traffic websites (like Sara mentioned) and also high partitioned data, enforcing the need of distributed transactions, a transactionless architecture can be adopted. Now you'll have to code referential integrity in the application.

Also, I like to manage transactions declaratively using annotations (I'm a Java guy) and aspects. That's a very clean way to determine transaction boundaries and it includes transaction propagation functionality.