Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
  Phone
  800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

    Privacy Policy

 

   
  SQL Server Tips by Burleson

Deadlocks

Deadlocks or deadly embraces as they are called on other database platforms occur when processes cannot proceed because they are waiting on a set of resources held by each other or held by other processes. Deadlock problems are a serious red flag that application design issues exist in the database.

It is easy to understand whether or not there is a deadlock problem. Depending on the SQL Server version, one of the two following queries can be used to get a count of deadlocks, the first being for SQL Server 7, and the other being for 2000 and higher:

* deadlock7.sql

-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact rtp AT rampant.cc
-- *************************************************

select
sum(cntr_value)
from
master.dbo.sysperfinfo
where
object_name = 'SQLServer:Locks' and
counter_name = 'Number of Deadlocks/sec'

* deadlock8.sql

-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact rtp AT rampant.cc
-- *************************************************

select
cntr_value
from
master.dbo.sysperfinfo
where
object_name = 'SQLServer:Locks' and
counter_name = 'Number of Deadlocks/sec' and
instance_name = '_Total'


Consistently seeing deadlock counts greater than zero will indicate that some user processes are experiencing delays in completing their work.

When SQL Server identifies a deadlock, it resolves the situation by choosing the process that can break the deadlock. This process is called the deadlock victim. SQL Server rolls back the deadlock victim’s transaction, and notifies the process application by returning an error message. It also cancels the process’ request and allows the transactions of the remaining processes to continue. SQL Server always attempts to choose the least expensive thread running the transaction as the deadlock victim.


The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2011 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter