question

Sudheer avatar image
Sudheer asked

disadvantage of using transaction

What is the disadvantage of using transaction in stored procedures.... Thanks in advance
sql-server-2012stored-procedurestransaction
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If I find one I'll let you know. Although saying that you really don't want to use transactions for select statements.
2 Likes 2 ·
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.
1 Like 1 ·
JohnM avatar image
JohnM answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tanglesoft avatar image
tanglesoft answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

denisrichi avatar image
denisrichi answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.