How to Decide to use Database Transactions

Question

Hi,

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

Please throw some light on this.

Cheers !!

Answer 1

if your have more than a single data modifying statement to execute to complete a task, all should be within a transaction.

This way, if the first one is successful, but any of the following ones has an error, you can rollback (undo) everything as if nothing was ever done.

Answer 2

Whenever you wouldn't like it if part of the operation can complete and part of it doesn't.

Answer 3

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

Answer 4

A transaction should be used when you need a set of changes needs 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".