Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Using Transaction in Select Statements

Author  Topic 

sridhar3004
Starting Member

34 Posts

Posted - 2013-05-10 : 09:44:47
Hi,

We've a query that is frequently used in the application
The query is a select statement query involving joins to multiple tables and is enclosed in a TRANSACTION block. The syntax is correct

Since the query is used often, by the end of the day, we get timeout error.

Is it possible that because the query uses the TRANSACTION keyword this could happen??? Otherwise the query fetches the result in less than a second


Warm Regards
Sridhar

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-10 : 10:11:51

It's possible that it is the transaction that is causing the timeouts. When you do a select a row from a table, it acquires a shared lock on the row (unless you specify otherwise). That would prevent any updates to that row (unless row versioning is enabled, which is not by default), because an update requires an exclusive lock, which is incompatible with everything else. If it is a readonly database (i.e., there are no updates to the tables involved in the selects), the transaction should not have an impact (unless you are acquiring update locks deliberately).

First thing to look at might be to see if it is a set of select statements, do you really need the transaction. I am not saying you don't - just inspect whether you really need it.

You might also consider enabling row versioning: http://msdn.microsoft.com/en-us/library/ms175095(v=sql.90).aspx However, that should be done with care because it can adversely affect the tempdb performance and space usage.


Go to Top of Page

sridhar3004
Starting Member

34 Posts

Posted - 2013-05-11 : 03:06:34
Thank you very much for your reply.
Let me inspect a few things before I take a final call
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-11 : 03:17:22
Tough to imagine a SELECT statement that requires or could benefit from an explicit transaction.

If it is a single SELECT, no matter how many joins, then 100% sure it is unnecessary and likely causing performance problems.

If there are multiple SELECT statements in a stored proc or batch (but only SELECTs) certainly you can and should remove the transaction. Though the proc may need to be re-written.

In short, the explicit transaction is probably causing performance problems.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-11 : 16:52:39
Russell, I agree with everything you said - I assumed that there were multiple statements (even though OP said "a select statement"). Then, I stretched my imagination to think that there might be tablock or updlock or something similar on some of the select statements.

On the other hand, if it is a single select statement preceded by a begin tran and succeeded by a commit, wouldn't that be effectively equivalent to the the implicit transaction that SQL Server would have done anyway?
Go to Top of Page
   

- Advertisement -