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 !!

Swift

@Lance Roberts, where did tsql come from? this can apply to any database. OP never said sql server. – Lover

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".

Poundage

"be used when you need a set of changes needs to be processed" I think the second 'needs' is unnecessary. – Ciri

Answer 2

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

Ironwork

Answer 3

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

Whitnell

Answer 4

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.

Lover

Answer 5

Anytime you want to lock up your database and potentially crash your production application, anytime you want to litter your application with hidden scalability nightmares go ahead and create a transaction. Make it big, slow, and put a loop inside.

Seriously, none of the above answers acknowledge the trade-off and potential problems that come with heavy use of transactions. Be careful, and consider the risk/reward each time.

Ebay doesn't use them at all. I'm sure there are many others.

http://www.infoq.com/interviews/dan-pritchett-ebay-architecture

Kulun

It was always interesting for me to see approaches on how to replace transactions with other techniques. – Exurbanite

What this answer fails to address is that eBay - in their application code - handle data inserts and updates very differently. Their software and architecture is built to support transactionless database communication. This is very much not the same as: "You should avoid transactions because god-tier eBay does it." Use transactions or completely refactor your code and system architecture. Those are your real options. Although, a third option does exist: Accept brain damage and data corruption. – Frear

A good resource on the subject: martinfowler.com/bliki/Transactionless.html – Frear

Answer 6

Whenever any operation falls under ACID(Atomicity,Consistency,Isolation,Durability) criteria you should use transactions

Read this article

Gunsmith

Answer 7

When you want to use atomic or isolation property of database for a set of changes.

Atomicity: An atomic transaction is an indivisible and irreducible series of database operations such that either all occurs, or nothing occurs(according to wikipedia).

Isolation: isolation determines how transaction integrity is visible to other users and systems(according to wikipedia).

Scrim

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Oas