I'm writing a pretty straightforward e-commerce app in asp.net, do i need to use transactions in my stored procedures?
Read/Write ratio is about 9:1
+7
A:
Many people ask - do I need transactions? Why do I need them? When to 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.
Here is some more info from http://blogs.msdn.com/florinlazar/
Codeslayer
2008-09-13 08:52:26
"use them all the time, unless you have a very good reason not to" this is like telling people to wear helmets all the time, unless they have a good reason not to.
Jeff Atwood
2008-09-13 09:10:04
That is assuming that using transactions is annoying or ridiculous in any way, which I don't think is the case.
Vinko Vrsalovic
2008-09-13 09:23:41
I think @Jeff Atwood did not read the full para before coming to the conclusion of donning helmets.
Codeslayer
2008-09-13 09:37:00
"I usually don't fall over when walking" is a very good and sensible reason not to wear a helmet."I use a ORM that handles optimistic locking for my long running business activities" is a very good and sensible reason not to use (long living) transactions."I don't know" isn' a good reason.
David Schmitt
2008-09-13 10:08:11
@David I believe Codeslayer's main message is saying start with the assumption you need them, and look for reasons not to use them. What are those reasons? Florin shows some. If you don't know what your code is behaving like, you have worse problems than the usage or not of transactions
Vinko Vrsalovic
2008-09-13 13:02:20
@Vinko - OK, I'll give you an example of when NOT to use them:We've got a pair of tables which log every change out to a logging database (this is financials information). It's a separate database, so requires the crossing of a process boundary, which marshalls all sorts of additional resources into the mix when it's wrapped inside of the updating / inserting transaction - taking as long as 45 seconds to commit. When you commit the first transaction prior to inserting into those two tables, however, the time drops to about 2 seconds for the entire sequence.I tend to agree with you, but..
David T. Macknet
2010-08-13 10:57:48
@David: Good example, you are just proving Codeslayer's point. You started using them, saw a performance price you couldn't pay, and then stopped using them. Exactly what should be done...
Vinko Vrsalovic
2010-08-13 20:22:52
@Vinko - yes, I am, indeed, proving Codeslayer's point. I'm also providing an example of when transactions can become a problem. In my particular example, a 4,500 line stored procedure had to grow to 5,600 lines, in order to accommodate rolling back some things which would have been rolled back by the transaction otherwise. So, having the transaction would have saved lots of extra work, had I been able to keep it. Getting rid of it meant I had to do my own cleanup in case of errors.
David T. Macknet
2010-08-20 10:31:28
+1
A:
The answer is, it depends. You do not always need transaction safety. Sometimes it's overkill. Sometimes it's not.
I can see that, for example, when you implement a checkout process you only want to finalize it once you gathered all data, etc.. Think about a payment f'up, you can rollback - that's an example when you need a transaction. Or maybe when it's wise to use them.
Do you need a transaction when you create a new user account? Maybe, if it's across 10 tables (for whatever reason), if it's just a single table then probably not.
It also depends on what you sold your client on and who they are, and if they requested it, etc.. But if making a decision is up to you, then I'd say, choose wisely.
My bottom line is, avoid premature optimization. Build your application, keep in mind that you may want to go back and refactor/optimize later when you need it. Look at a couple opensource projects and see how they implemented different parts of their app, learn from that. You'll see that most of them don't use transactions at all, yet there are huge online stores that use them.
Till
2008-09-13 09:06:01
+1
A:
Of course, it depends.
It depends upon the work that the particular stored procedure performs and, perhaps, not so much the "read/write ratio" that you suggest. In general, you should consider enclosing a unit of work within a transaction if it is query that could be impacted by some other, simultaneously running query. If this sounds nondeterministic, it is. It is often difficult to predict under what circumstances a particular unit of work qualifies as a candidate for this.
A good place to start is to review the precise CRUD being performed within the unit of work, in this case within your stored procedure, and decide if it a) could be affected by some other, simultaneous operation and b) if that other work matters to the end result of this work being performed (or, even, vice versa). If the answer is "Yes" to both of these then consider wrapping the unit of work within a transaction.
What this is suggesting is that you can't always simply decide to either use or not use transactions, rather you should apply them when it makes sense. Use the properties defined by ACID (Atomicity, Consistency, Isolation, and Durability) to help decide when this might be the case.
One other thing to consider is that in some circumstances, particularly if the system must perform many operations in quick succession, e.g., a high-volume transaction processing application, you might need to weigh the relative performance cost of the transaction. Depending upon the size of the unit of work, a commit (or rollback) of a transaction can be resource expensive, perhaps negatively impacting the performance of your system unnecessarily or, at least, with limited benefit.
Unfortunately, this is not an easy question to precisely answer: "It depends."
Danny Whitt
2008-09-13 09:49:10
+3
A:
Remember in SQL Server all single statement CRUD operations are in an implicit transaction by default. You just need to turn on explict transactions (BEGIN TRAN) if you need to make multiple statements act as an atomic unit.
Mike L
2008-09-13 16:07:22
A:
Use them if:
- There are some errors that you may want to test for and catch which won't be caught except by you going out and doing the work (looking things up, testing values, etc.), usually from within a transaction so that you can roll back the whole operation.
- There are multi-step operations of any sort, which should, logically, be rolled back as a group if they fail.
David T. Macknet
2008-09-15 12:55:22