Any Issues using BEGIN TRAN .... ROLLBACK for SELECT Queries? 2

[kjv1611]

I'm just now trying to make myself get comfortable with using the BEGIN TRAN ... ROLLBACK construction (Transactions) for UPDATES, etc. Well, I got to thinking.. I wonder if there would be any harm in just using it on ALL my queries, even the SELECT queries.

So, at a quick web search glance, and I do honestly mean QUICK, I didn't find any immediate obvious answers.

That, plus I thought I'd likely get better information from the folks here at tek-tips from what I've seen in the past on a topic like this.

Thanks for any info, suggestions, or references on the topic.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[yelworcm]

Wrapping all of your queries in transactions will likely add a little overhead on the server, but a lot of overhead on your application.

Transactions are meant to be rolled back occasionally, but when you think about a select statement, what would rolling it back mean?

Lastly, using an explicit transaction implies that you want the ability to rollback in the case of an error of some sort. If you wait for user input on whether something should be committed, then you will have all sorts of trouble with blocking locks, as these will not be freed up until the statement is committed or rolled back.

 

[gmmastros]

Putting select statements inside a transaction is useless unless you have multiple select statements.

For example, suppose you are returning 2 result sets. It's theoretically possible the results for the second query would be affected by another user that adds/edits/deletes data. By setting the transaction isolation level and then running your selects in a transaction, it guarantees that the results of the 2nd query don't change while the first query is running.

This microsoft article

http://msdn.microsoft.com/en-us/library/ms173763.aspx

has good information in it regarding your question.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

See, I'm glad I asked this question here. I learned something new from both of your posts. Thanks a bunch for sharing. My initial thought with it was that I would start using Transactions on everything if there is no real performance overhead, just so I'd get accustomed to using them. But now, with what you two have shared, I see it'd be best to only use it when I need it.

George,

I never would have thought of the second scenario in the past, but because of one of our processes, I can very easily think of scenarios where that would be helpful now.

And the more I think of it, I can also definitely see where it would be helpful.

Thanks a bunch to both of you!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57