asked
disadvantage of using transaction
What is the disadvantage of using transaction in stored procedures.... Thanks in advance
To me, all the answers given talking about disadvantages are not disadvantages. They are the ill-effects if it is not implemeted correctly. If you want to make sure that ACID properties remain intact for sure as per requirement, then you must use it. If the implementation is not correct, then it is user's mistake. What if a user issue a DELETE statement and forgot to write the the WHERE clause? Is it the disadvantage of DELETE? I do not think so. Such features are provided to help us. Now it is up to us whether we use them for good OR for bad. That is my thought and everyone can have different opinion.
The only thing that I can think of is if you fail to handle the COMMIT/ROLLBACK appropriately, you can lock things up, depending on what your doing. I would hope that in a procedure (versus ad-hoc) you'd catch this before rolling to production but I've seen crazier things happen.
As best practice I would never put transactions into a stored procedure unless there was a genuine reason to protect a set of statements as a whole. If I ever come across the need to use them I ensure that any value or result that is not dependent on the transaction is determine prior to commencement. Then do everything possible to ensure that the duration of the transaction is a quick as possible. Poorly designed transactions can have a detrimental impact on the scalability if poorly designed as they have much larger possibility of locking rows and pages than implicit single statements.
There is no disadvantage to using transactions. They should be used when performing multiple operations against one or more tables to allow you to return the database to a consistent state in case there is an error in one of the operations
17 People are following this question.
