SQL Server Performance Forum – Threads Archive

Processor Deadlock?

Here’s an error message I’ve not come across before… Transaction (Process ID 66) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. As you can see, it is not a normal deadlock error message about locks, in fact the procedure that returned this error message does not even do any locking, it’s just a couple of very straightforward selects. Is this a processor deadlock? The procedure has been running quite happily for a few years, but a couple of weeks ago we switched to a new machine – a dual P4 with hyperthreading, so as far as SQL Server is concerned, 4 processors. The OS is Windows 2000 SP4, SQL Server is 2000 SP3. This has only happened once, so I’m not too concerned, more curious than anything. Is there anything I can do to stop this happening in the future? Cheers

Add a NOLOCK hint to your FROM clause: SELECT <fieldlist> FROM <tablename> WITH (NOLOCK)

The error identifies the resource or resources on which the transaction is deadlocked. The resources can be locks, parallelism (or communication buffer), waiting threads, or a combination of them. To resolve the deadlock, one of the participants in the cycle must be terminated. With SQL 2K , you can determine the resources involved in a deadlock and troubleshoot locking by using Profiler or Trace Flag 1204. Profiler provides information for basic deadlock detection. For more information about Trace Flag 1204 deadlock reports involving specific resources, see Deadlocks involving locks, Deadlocks involving parallelism, and Deadlocks involving threads under SQL books online. Using NOLOCK in the query will have possibility to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Articles for your reference to reduce deadlocks :
http://www.sql-server-performance.com/deadlocks.asp
http://www.sql-server-performance.com/sf_block_prevention.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Also check Performance in general, is all is very heavy, may be you need to optimize.
Luis Martin
Moderator
SQL-Server-Performance.com

Add a option caluse after select statement
SELECT <fieldlist> FROM <tablename>
where <whereclause>
OPTION (MAXDOP 1)
Becuase you sql is executing on more than 1 prcessor at excution time , due to max degree of parallelism configuration. Mahesh Paranjpe
SQL DBA

Thanks Mahesh! I’d been experiencing the same problem (after 2 years of no issues) with a query that does a dozen UNIONs. Any idea what would cause a server to start experiencing this issue? Thanks again! Regards,
Kevin W. Gainey

Query optimizer makes the execution plan for the procedures by looking at the available resources on the machine. So if it sees that there are more processors available, it’ll try to use parallelism for executing the query. If the statistics are not up to date, one of the part of the query may take longer to execute and we may see blocking condition. I would recommend updating the statistics for the database tables and forcing the recompile of procedure plans. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Hi Kevin, As per my knowledge you are facing problem not because of dozens of
Unions. I will try to explain you. 1) When you execute your query on a server having more than 1
processor. It executes on multiple processors. Due to that each
processor makes individual thread for you sql and tries to execute
on database. Due to no of locks, sql server gives you error that
it’s a deadlock.
2) To overcome to this situation you have to specify processor on
which your sql must be executed.
3) There is another option that you can set your server
configuration. By which only peculiar processor will be used
during any data related issues.
4) This will make negative impact on you sql server. This is because
your server will use only one processor capacity during any database
modification.Even though server is having more than one processor.
5) This is the reason behind to add option clause in select statement.
Mahesh Paranjpe
SQL DBA

]]>