Deadlocking and Query Optimisation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Deadlocking and Query Optimisation

Why would a "Select" statement be chosen as deadlock victim. I am running this single transaction in query analyzer and this query is got deadlocked. "Select" statements just do "Reads" on database , so why would they deadlocked…….Any idea select ordernumber, sum(ordered), s2.storename, left(sod.source, 4)
from SalesOrder so
join SalesOrderDetail sod on sod.SalesOrderID = so.ID
join StyleColourSize scs on sod.Barcode = scs.Barcode
join CTS_Store store on so.Branch = store.storename
join CTS_Store s2 on s2.PerOrgCode = store.DefaultDistHub
where styleno = ‘160275’
and colour = ‘kelp’
and ordered <> 0
group by ordernumber, s2.storename, left(sod.source, 4)
In SQL server default ISOLATION is READ COMMITTED
so SELECT statement also required to get the row/page/table locks… What is the other statement invovled in this dead lock… To avoid this behaviour you can use NOLOCK hint or ISOLATION LEVEL READ_UNCOMMITTED but you get dirty data….
Mohammed U.
Not sure for other statement as I was not running any profiler. Could you please brief out what you mean by "dirty data" and what kind of locks does "select" statement apply on database. Does READ COMMITED type also holds the data and cause deadlocks ?
Did you look execution plan?
What indexes are using? Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
More information in: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19081 Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
Attach is the index info from execution plan Its using 2 types of scan 1. Clustered Index Scan on StyleColorSize which cost to (30%)
2. Clustered Index Seek on SalesOrderdETAIL WHICH COST upto (41%)
3. Clustered Index Seek on Sales order (pk_…) which cost upto (24%) I am not an expert, after you explain above can you also explain whats differnce between SCAN and SEEK and which one is better also how to read execution plan and figure out areas which are issues ? Thanks a lot for your help and advice
After reading my last link: Did you try DTA to find better indexes? Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
Hi Luis, Sorry didn’t mean to duplicate the data. Apreciate your answers thanks
Your last link refer to my duplicate forum and not to any Link .
No problem. Run the query with first: SET STATISTIC IO ON and finish with: SET STATISTICS IO OFF and share you results.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
Its not deadlockingm may be other query or process at time is not running, but the execution plan for this is same as before
To resolve the deadlock issue, you need to find the other brocess that caused the deadlock. Most likely you have an UPDATE statement running on the StyleColorSize table. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
One question, is this related to SQL 2005. If so you can have better changes to optimize the query performance and avoid deadlocks in this case. Also mention about SQL service pack and no. of users on thsi server for more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Yes its on SQL server 2005 with service pack 1 installed on the server. These servers are clustered and are alsop being replicated. Database size is around 180 GB. Number of users connected are around 200.
Also, I often run queries in Read Uncommitted mode when checking up on data, but when doing data updates I need to know the currently committed data. I know that read committed gets read locks, and so I expect to get blocked by update or exclusive locks from other processes. My real concern is that I should only be blocked; a single sql statement, or a transaction with only one sql statement, should never get deadlocked. It either gets the lock first and completes, or it gets blocked until the other process completes. But it is never holding any other locks open, because it’s just a single statement, so it can’t be both blocking and waiting (which is a requisite for deadlocking.) The only thing I can think of is that SQL Server grabbed the read locks incrementally:
My process got the read lock for the first table in my query
Then process 2 got an update lock on the second table
Then my process tried to get a read lock on the second table it joined to, but was blocked by process 2
Then process 2 tried to get an update lock on the first table That’s the only possibility I can come up with, but it doesn’t make sense, because I thought queries were atomic. If this sort of thing could happen, you’d expect to get deadlocks all the time, as people queried tables that stored procs were updating transactionally.
The old copies of the updated row or rows are stored in the tempdb database. If the updated row is committed, the row version in the tempdb is deleted. If the update is aborted, the old values for the row(s) are restored in the original table, and the copy in the tempdb is deleted. The SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement is optional if you enable the READ COMMITTED isolation. So your existing applications can automatically make use of this feature without any change to stored procedures or any embedded SQL. Try to use this option for these queries. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks for the information Satya, however I still didn’t understand because max a "select" statement can do is cause a block, however how can that be deadlocked ??
quote:Originally posted by viksar Thanks for the information Satya, however I still didn’t understand because max a "select" statement can do is cause a block, however how can that be deadlocked ??

Technically it is poosible that a select being deadlocked with an update. Can you post the code which updates the StyleColorSize table along with the indexes on that table.? Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Take a look Bart Duncan’s SQL Weblog…great article… http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Mohammed U.
quote:Originally posted by MohammedU Take a look Bart Duncan’s SQL Weblog…great article… http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

I can guarantee that everyone will learn something new from that. Also have a look at
http://www.eps-publishing.com/article.aspx?quickid=0309101&page=1 Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Thanks guys for such great info, these articles are definatelly informative. I want few more tips on issues related to deadlocking. Whats the difference between seek and scan and which is more useful, Also How can i read out execution plan results and understand the output of execution plan and query cost.
"Also How can i read out execution plan results and understand the output of execution plan and query cost." This one is for Yukon, but may be can help you. http://www.sql-server-performance.com/jc_yukon_beta1_execution_plans.asp Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
Read the following book by Kalen Delaney Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization
http://www.microsoft.com/MSPress/books/8565.aspx
Mohammed U.
This book is yet to be released so can’t view, are there any more online topics or books available online on better undertanding of Query optimization, Index seek and scan, execution plans and query cost. Probably which helps in making better queries and SP’s
It will be released in 2-3 months…I forgot about it…
Check BOL topic: graphical execution plan [SQL Server] Mohammed U.
quote:Originally posted by viksar Thanks guys for such great info, these articles are definatelly informative. I want few more tips on issues related to deadlocking. Whats the difference between seek and scan and which is more useful, Also How can i read out execution plan results and understand the output of execution plan and query cost.

See Craig Freedman’s blog posts on the topic. http://blogs.msdn.com/craigfr/archive/2006/06/26/647852.aspx Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
hey guys, do you know any links on checking for performance. I want few more tips on issues related to deadlocking. Whats the difference between seek and scan and which is more useful, Also How can i read out execution plan results and understand the output of execution plan and query cost.
http://www.sql-server-performance.com/deadlocks.asp
http://msdn2.microsoft.com/en-us/library/ms188246.aspx
http://msdn2.microsoft.com/en-us/library/ms190465.aspx
http://www.extremeexperts.com/sql/Yukon/DeadLockDetection.aspx MohammedU.
Moderator
SQL-Server-Performance.com
Viksar
I would suggest to refer the links above before asking any more question, hope you understand what I mean. http://www.sql-server-performance.com/query_execution_plan_analysis.asp is a good one on understanding Execution plan analysis. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>