23

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

Please throw some light on this.

Cheers !!

John Saunders
  • 160,644
  • 26
  • 247
  • 397
peakit
  • 28,597
  • 27
  • 63
  • 80
  • @Lance Roberts, where did tsql come from? this can apply to any database. OP never said sql server. – KM. Jul 07 '09 at 17:57

7 Answers7

30

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

user3347392
  • 64
  • 6
Jeff Siver
  • 7,434
  • 30
  • 32
  • 2
    "be used when you need a set of changes needs to be processed" I think the second 'needs' is unnecessary. – Jordy Boom Jul 07 '09 at 18:35
13

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

Paul Sonier
  • 38,903
  • 3
  • 77
  • 117
8

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

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
7

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.

KM.
  • 101,727
  • 34
  • 178
  • 212
6

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

GeorgeBarker
  • 991
  • 1
  • 11
  • 7
  • It was always interesting for me to see approaches on how to replace transactions with other techniques. – Ivan Voroshilin Feb 26 '14 at 18:35
  • 1
    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. – Kafoso Oct 15 '20 at 12:52
  • A good resource on the subject: https://martinfowler.com/bliki/Transactionless.html – Kafoso Oct 15 '20 at 13:22
1

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

Read this article

D Malan
  • 10,272
  • 3
  • 25
  • 50
SpringLearner
  • 13,738
  • 20
  • 78
  • 116
0

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

parsa ahani
  • 51
  • 4
  • 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](/help/how-to-answer). – Abhishek Dutt Jun 30 '22 at 07:11