SQL2008中SQL应用之- 死锁(Deadlocking)

在另一方释放资源前,会话1和会话2都不可能继续。所以,SQL Server会选择死锁中的一个会话作为“死锁牺牲品”。

注意:死锁牺牲品的会话会被杀死,事务会被回滚。

注意:死锁与正常的阻塞是两个经常被混淆的概念。

发生死锁的一些原因:

1、应用程序以不同的次序访问表。例如会话1先更新了客户然后更新了订单,而会话2先更新了订单然后更新了客户。这就增加了死锁的可能性。

2、应用程序使用了长时间的事务,在一个事务中更新很多行或很多表。这样增加了行的“表面积”,从而导致死锁冲突。

3、在一些情况下,SQL Server发出了一些行锁,之后它又决定将其升级为表锁。如果这些行在相同的数据页面中,并且两个会话希望同时在相同的页面升级锁粒度,就会产生死锁。

一、使用 SQL Server Profiler 分析死锁

http://msdn.microsoft.com/zh-cn/library/ms188246.aspx

二、使用跟踪标志位找出死锁

本文主要介绍使用DBCC TRACEON、DBCC TRACEOFF和DBCC TRACESTATUS命令来确保死锁被正确记录到SQL Server Management Studio SQL日志中。这些命令用来启用、关闭、和检查跟踪标志位的状态。

 DBCC TRACEON,启用跟踪标志位。用法:DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]

详细参看 MSDN:http://msdn.microsoft.com/zh-cn/library/ms187329.aspx

 DBCC TRACESTATUS,检查跟踪标志位状态。用法:DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] ) [ WITH NO_INFOMSGS ]

详细参看 MSDN:http://msdn.microsoft.com/zh-cn/library/ms187809.aspx

 DBCC TRACEOFF,关闭跟踪标志位。用法:DBCC TRACEOFF (trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]

详细参看 MSDN:http://msdn.microsoft.com/en-us/library/ms174401.aspx

下面我们模拟一个死锁:

在第一个SQL查询窗口执行:


代码如下:

use AdventureWorks
go
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 90
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 91
COMMIT TRAN
END

在第二个查询窗口执行:


代码如下:

use AdventureWorks
go

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 91
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 90
COMMIT TRAN
END

等待几秒后,其中一个查询窗口会提示:


代码如下:

/*
Msg 1205, Level 13, State 51, Line 9
Transaction (Process ID 52) was deadlocked on lock resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.
*/

此时,查看,SQL Server Management Studio的SQL 日志,发现死锁事件没有被记录。
打开第三个查询窗口,执行:


代码如下:

DBCC TRACEON (1222, -1)
GO
DBCC TRACESTATUS

为了模拟另一个死锁,将重启动“胜利”的那个连接查询(没有被杀死的那个),然后重启死锁丢失的会话,几秒后又出现另一个死锁了。
死锁发生后,停止另一个执行的查询。现在,SQL Server Management Studio的SQL 日志中包含了死锁事件的详细错误信息。包括相关的数据库和对象、锁定模式以及死锁中的SQL语句。
SQL2008中SQL应用之- 死锁(Deadlocking)

在检查完毕后,关闭跟踪标志位:


代码如下:

DBCC TRACEON (1222, -1)
GO
DBCC TRACESTATUS

解析: 

在本例中,我们使用跟踪标志位1222。跟踪标志位1222能把详细的死锁信息返回到SQL日志中,标志位-1表示跟踪标志位1222应该对所有SQL Server连接在全局中启用。

三、设置死锁优先级

我们也可以使用SET DEADLOCK_PRIORITY命令来增加一个查询会话被选为死锁牺牲品的可能性。此命令的语法如下:
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

http://msdn.microsoft.com/en-us/library/ms186736.aspx

例如,上例中,第一个查询窗口如果使用以下的死锁优先级命令,几乎可以肯定会被选为死锁牺牲品。(正常情况下,SQL Server会把它认为取消或回滚代价最小的连接作为默认的死锁牺牲品):


代码如下:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET DEADLOCK_PRIORITY LOW
BEGIN TRAN

UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 2
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 1
COMMIT TRAN

解析:可以将优先级设为High或Normal,High表示除非另一个会话有相同的优先级,否则它不会被选为牺牲品。Normal是默认行为,如果另一个会话是High,它可能会被选中。如果另一个是Low,则它可以安全地不被选中。如果两个会话有相同的优先级,则回滚代价最小的事务会被选中。

关于死锁的其他资源,可能会有补充:

happyhippy的SQL Server死锁总结,也总结的不错。  http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html

时间: 2011-06-15

SQL2005查看死锁存储过程sp_who_lock

下面是我整理的监控sql server数据库,在性能测试过程中是否出现死锁.堵塞的SQL语句,还算比较准备,留下来备用. 调用方法:选中相应的数据库,执行exec sp_who_lock USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[sp_who_lock] as begin declare @spid int, @bl int, @intTransactionCo

SQLServer 中的死锁说明

两个进程发生死锁的典型例子是:进程T1中获取锁A,申请锁B:进程T2中获取锁B,申请锁A,我们下面动手来演示一下这种情况: 1. 创建一个Database,名为InvDB. 2. 执行下面脚本创建person表并填充两条数据: 3. 在SQL Server Management Studio的两个窗口中同时执行下面的查询: 这段代码在默认的READ COMMITTED隔离级别下运行,两个进程分别在获取一个排它锁的情况下,申请对方的共享锁从而造成死锁. 可见一个进程可以正常更新并显示结果,而另一个

查找sqlserver查询死锁源头的方法 sqlserver死锁监控

查找出SQLServer的死锁和阻塞的源头 --查找出SQLServer死锁和阻塞的源头 复制代码 代码如下: use mastergodeclare @spid int,@bl intDECLARE s_cur CURSOR FORselect  0 ,blockedfrom (select * from sysprocesses where  blocked>0 ) awhere not exists(select * from (select * from sysprocesses whe

SqlServer表死锁的解决方法分享

其实不光是上面描述的情况会锁住表,还有很多种场景会使表放生死锁,解锁其实很简单,下面用一个示例来讲解: 1 首先创建一个测试用的表: 复制代码 代码如下: CREATE TABLE Test ( TID INT IDENTITY(1,1) ) 2 执行下面的SQL语句将此表锁住: 复制代码 代码如下: SELECT * FROM Test WITH (TABLOCKX) 3 通过下面的语句可以查看当前库中有哪些表是发生死锁的: 复制代码 代码如下: SELECT request_session_

sql server 2000阻塞和死锁问题的查看与解决方法

数据库发生阻塞和死锁的现象: 一.数据库阻塞的现象:第一个连接占有资源没有释放,而第二个连接需要获取这个资源.如果第一个连接没有提交或者回滚,第二个连接会一直等待下去,直到第一个连接释放该资源为止.对于阻塞,数据库无法处理,所以对数据库操作要及时地提交或者回滚.二.数据库死锁的现象:第一个连接占有资源没有释放,准备获取第二个连接所占用的资源,而第二个连接占有资源没有释放,准备获取第一个连接所占用的资源.这种互相占有对方需要获取的资源的现象叫做死锁.对于死锁,数据库处理方法:牺牲一个连接,保证另外

C#中lock死锁实例教程

在c#中有个关键字lock,它的作用是锁定某一代码块,让同一时间只有一个线程访问该代码块,本文就来谈谈lock关键字的原理和其中应注意的几个问题: lock的使用原型是: lock(X) { //需要锁定的代码.... } 首先要明白为什么上面这段话能够锁定代码,其中的奥妙就是X这个对象,事实上X是任意一种引用类型,它在这儿起的作用就是任何线程执行到lock(X)时候,X需要独享才能运行下面的代码,若假定现在有3个线程A,B,C都执行到了lock(X)而ABC因为此时都占有X,这时ABC就要停下

python中的多线程实例教程

本文以实例形式较为详细的讲述了Python中多线程的用法,在Python程序设计中有着比较广泛的应用.分享给大家供大家参考之用.具体分析如下: python中关于多线程的操作可以使用thread和threading模块来实现,其中thread模块在Py3中已经改名为_thread,不再推荐使用.而threading模块是在thread之上进行了封装,也是推荐使用的多线程模块,本文主要基于threading模块进行介绍.在某些版本中thread模块可能不存在,要使用dump_threading来代

ThinkPHP中ajax使用实例教程

本文实例讲述了ThinkPHP中使用ajax的方法,提交表单如下图所示: 点击提交,不需要刷新本页,将内容提交到数据库当中,并在本页显示提交的内容.如下图所示: 一.jquery实现方法: MessageAction.class.php页面代码如下: <?php class MessageAction extends Action{ function index(){ $this->display(); } function add(){ //ajaxReturn(数据,'提示信息',状态)

VC++中内存对齐实例教程

内存对其是VC++程序设计中一个非常重要的技巧,本文即以实例讲述VC++实现内存对其的方法.具体分析如下: 一.概述 我们经常看到求 sizeof(A) 的值的问题,其中A是一个结构体,类,或者联合体. 为了优化CPU访问和优化内存,减少内存碎片,编译器对内存对齐制定了一些规则.但是,不同的编译器可能有不同的实现,本文只针对VC++编译器,这里使用的IDE是VS2012. #pragma pack()是一个预处理,表示内存对齐.布局控制#pragma,为编译程序提供非常规的控制流信息. 二.结构

在windows中追加/删除虚拟打印机实例教程

由于项目需要在windwos系统中添加多台虚拟打印机(能够正常打印出纸),查找了一下系统函数. 使用 rundll32 printui.dll,PrintUIEntry,在CMD中运行,在弹出框中得到以下提示. 用法: rundll32 printui.dll,PrintUIEntry [options] [@commandfile] /a[file] 二进制文件名 /b[name] 基本打印机名 /c[name] 如果操作在远程机器上,unc 机器名 /dl 删除本地打印机 /dn 删除网络打

C#中lock用法详解

本文实例讲述了C#中lock的用法.分享给大家供大家参考.具体分析如下: lock 关键字可以用来确保代码块完成运行,而不会被其他线程中断.这是通过在代码块运行期间为给定对象获取互斥锁来实现的. 先来看看执行过程,代码示例如下: lock 语句用于获取某个给定对象的互斥锁,执行一个语句,然后释放该锁. lock-statement:(lock 语句:) 复制代码 代码如下: lock(expression) embedded-statement(lock   (   表达式   )   嵌入语句

Git 命令行教程及实例教程(附github注册)

本篇博客主要讲解以下问题: Git 常用命令 创建新仓库 检出仓库 添加与提交 推送改动 分支 更新与合并 标签 替换本地改动 Git实例教程 操作小技巧 Git 常用命令常用命令 创建新仓库 创建新文件夹,打开,然后执行 git init 以创建新的 git 仓库. 检出仓库 执行如下命令以创建一个本地仓库的克隆版本: git clone /path/to/repository 如果是远端服务器上的仓库,你的命令会是这个样子: git clone username@host:/path/to/

web标准布局实例教程,用定位轻松解决CSS复杂布局

我相信来经典论坛学习标准的朋友,99%都不是为了进W3C,不是为了成为专业的研究人员,那么大家来的目的是什么呢?很简单,其实都是想通过标准的学习让自己多增加一项技能而已,让自己在找工作的时候不会在被"需要熟练掌握web标准"而难倒,在下面的实例中xhtml并没有什么语意,文章的目的只是让然大家能更好的掌握CSS中相对.绝对定位的用法(新手问此问题的确实很多),及如何有效的结合背景,实现比较复杂的布局.不足之处还望前辈们能指点一二,感谢! ------------------------

Vue.js组件使用开发实例教程

组件 组件可以扩展HTML元素,封装可重用的代码,在较高的层面上,组件是自定义元素,vue.js的编译器为它添加特殊功能,在有些情况下,组件也可以是原生HTML元素的形式,以is特性扩展. Vue.js的组件可以理解为预先定义好了行为的ViewModel类.一个组件可以预定义很多选项,但最核心的是以下几个: 模板(template):模板声明了数据和最终展现给用户的DOM之间的映射关系. 初始数据(data):一个组件的初始数据状态.对于可复用的组件来说,这通常是私有的状态. 接受的外部参数(p

Java8中的 Lambda表达式教程

 1. 什么是λ表达式 λ表达式本质上是一个匿名方法.让我们来看下面这个例子: public int add(int x, int y) { return x + y; } 转成λ表达式后是这个样子: (int x, int y) -> x + y; 参数类型也可以省略,Java编译器会根据上下文推断出来: (x, y) -> x + y; //返回两数之和 或者 (x, y) -> { return x + y; } //显式指明返回值 可见λ表达式有三部分组成:参数列表,箭头(-&g