You use transactions when the set of database operations you are making needs to be atomic.
That is - they all need to succeed or fail. Nothing in between.
Transactions are to be used to ensure that the database is always in a consistent state.
In general, unless there is a good reason not to use them (long running process for instance), use them.
The answer is simple: use them all the time, unless you have a very good reason not to (for instance, don't use atomic transactions for "long running activities" between businesses). The default should always be yes. You are in doubt? - use transactions.
Why are transactions beneficial? They help you deal with crashes, failures, data consistency, error handling, they help you write simpler code etc. And the list of benefits will continue to grow with time.
Don't think in terms of transactions only when you talk to two or more databases. Starting with technologies like LTM, transactions are made available to be used for any multi-action operation. "X = A+B; Y = C*D;" can be transactional or atomic, as some prefer to name it, and can be seen as a single unit of work.
That is also why support for transactions is available almost everywhere. In database systems, in COM+, in ASP.NET, in .Net Framework with System.Transactions and EntepriseServices, in Indigo, in Biztalk etc
You might be saying now: "but my friend X doesn't use transactions - why it is important that I use them?". It is mostly a problem on the education side. The industry didn't talk a lot about transactions and their usage until recently. The original scope of transactions was limited to database scenarios. But technologies around transactions evolved and continue to evolve, in terms on capabilities, ease of use, flexibility, performance etc.
Mark as answer if satisfied....
Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator