How to Decide to use Database Transactions

Question

How do you guys decide that you should be wrapping the sql in a transaction?

Please throw some light on this.

Cheers !!

Answer 1

A transaction should be used when you need a set of changes to be processed completely to consider the operation complete and valid. In other words, if only a portion executes successfully, will that result in incomplete or invalid data being stored in your database?

For example, if you have an insert followed by an update, what happens if the insert succeeds and the update fails? If that would result in incomplete data (in this case, an orphaned record), you should wrap the two statements in a transaction to get them to complete as a "set".

Answer 2

If you are executing two or more statements that you expect to be functionally atomic, you should wrap them in a transaction.