May 21, 2002 at 6:17 am
does the order of the tables in my select statement have any relevance to deadlocks? ie. will SQLserver (2000) attempt to put share locks on all tables at once (ie. all or none), or will it put locks on the tables it can and queue for the remaining to make up the select statement. Initial testing suggests it does all locks at once or none but could someone confirm this because I do seem to be getting some deadlocks with a simple select statement that is not in a transaction? Any other ideas gratefully received.
Many thanks.
Kevin Terrill
May 21, 2002 at 6:50 am
Generally no as the Query Optimizer Manager will makie decisions on the linking.
SO
SELECT * FROM T1 INNER JOIN T2 ON x=y LEFT JOIN T3 ON y=z
will in most cases still produce the same query execution plan and work order as
SELECT * FROM T3 RIGHT JOIN T1 INNER JOIN T2 ON x=y ON y=z
I have seen exceptions and I suggest looking at thru QA visual Execution Plan to compare. If it is not then figure out which way works optimal for you or provide hints. A deadlock occurrs when any two items attempt to lock down the same objects. Can be thru select, delete, insert, update or any combination. If you are seeing locks for long periods of times if may make sense to use a subquery to work on smaller items or larger to get a result set that can speed up access.
For instance if the above T1 had a million rows of data and the where clause limited it to 30 rows it may work better to do something like this.
SELECT * FROM (SELECT * FROM T1 WHERE valy=x) AS T1 INNER JOIN T2 ON x=y LEFT JOIN T3 ON y=z
This may help. Also may need to consider indexes as they help speed queries up too. The longer it takes a query to complete the higher the likelyhood of blocking other queries, deadlocks will only occurr if the primary query is blocked from a resource that a secound query is using which in turns is blocked by the primary query from another resource.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply