当前位置: 首页 >> 新闻中心
新闻中心
事务(进程 ID 60)与另一个进程被死锁在锁资源上,并且已被选作死锁牺牲品。请重新运行

事务(进程 ID 60)与另一个进程被死锁在锁资源上,并且已被选作死锁牺牲品。请重新运行

该事务。

一、问题描述

近期,由于二十多台电脑同时访问一台SQL Server 2005 服务器,并且数据每间隔3 分钟从

另一个Oracle 数据库中读取数据信息供20 多台电脑查询与显示,在信息显示时,经常报下

面的错误,导致程序出错。

‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐

事务(进程 ID 60)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运

行该事务。

在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean

breakConnection)

在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean

breakConnection)

在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject

stateObj)

在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler,

SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject

stateObj)

在 System.Data.SqlClient.SqlDataReader.HasMoreRows()

在 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)

在 System.Data.SqlClient.SqlDataReader.Read()

在 HonryLCD.honry.lcd.LcdPatientFrm.getBed()

二、处理说明

1、查看锁状态

连上数据库后,在查询界面中按 Ctrl+2 键可以查询状态,如下:

2、事务(进程 ID 59)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲

品。请重

update t_sms_send set msg_flag = -1 where id in (select top 100 id from t_sms_send where

msg_flag=-2)

此句应该改为update t_sms_send set msg_flag = -1 where id in (select top 100 id from t_sms_send with

(nolock) where msg_flag=-2)

事务(进程 ID )与另一个进程已被死锁在 lock 资源上,且该事务已被选作死锁牺牲品。

请重新运行该事务

其实所有的死锁最深层的原因就是一个:资源竞争 表现一:

一个用户A 访问表A(锁住了表A),然后又访问表B

另一个用户B 访问表B(锁住了表B),然后企图访问表A

这时用户A 由于用户B 已经锁住表B,它必须等待用户B 释放表B,才能继续,好了他老人家

就只好老老实实在这等了

同样用户B 要等用户A 释放表A 才能继续这就死锁了

解决方法:

这种死锁是由于你的程序的BUG 产生的,除了调整你的程序的逻辑别无他法

仔细分析你程序的逻辑,

1:尽量避免同时锁定两个资源

2: 必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源.

表现二:

用户A 读一条纪录,然后修改该条纪录

这是用户B 修改该条纪录

这里用户A 的事务里锁的性质由共享锁企图上升到独占锁(for update),而用户B 里的独占锁

由于A 有共享锁存在所以必须等A 释

放掉共享锁,而A 由于B 的独占锁而无法上升的独占锁也就不可能释放共享锁,于是出现

了死锁。

这种死锁比较隐蔽,但其实在稍大点的项目中经常发生。

解决方法:

让用户A 的事务(即先读后写类型的操作),在select 时就是用Update lock

语法如下:

select * from table1 with(updlock) where ....

==========================

在联机事务处理(OLTP)的数据库应用系统中,多用户、多任务的并发性是系统最重要的技术

指标之一。为了提高并发性,目前大部分RDBMS 都采用加锁技术。然而由于现实环境的复

杂性,使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加锁技术,最

小化死锁是开发联机事务处理系统的关键。

死锁产生的原因

在联机事务处理系统中,造成死机主要有两方面原因。一方面,由于多用户、多任务的并发

性和事务的完整性要求,当多个事务处理对多个资源同时访问时,若双方已锁定一部分资源

但也都需要对方已锁定的资源时,无法在有限的时间内完全获得所需的资源,就会处于无限

的等待状态,从而造成其对资源需求的死锁。

另一方面,数据库本身加锁机制的实现方法不同,各数据库系统也会产生其特殊的死锁情况。

如在Sybase SQL Server 11 中,最小锁为2K 一页的加锁方法,而非行级锁。如果某张表的记

录数少且记录的长度较短(即记录密度高,如应用系统中的系统配置表或系统参数表就属于

此类表),被访问的频率高,就容易在该页上产生死锁。

几种死锁情况及解决方法

清算应用系统中,容易发生死锁的几种情况如下:

● 不同的存储过程、触发器、动态SQL 语句段按照不同的顺序同时访问多张表;

● 在交换期间添加记录频繁的表,但在该表上使用了非群集索引(non‐clustered);

● 表中的记录少,且单条记录较短,被访问的频率较高;

● 整张表被访问的频率高(如代码对照表的查询等)。

以上死锁情况的对应处理方法如下:

● 在系统实现时应规定所有存储过程、触发器、动态SQL 语句段中,对多张表的操作总是

使用同一顺序。如:有两个存储过程proc1、proc2,都需要访问三张表zltab、z2tab 和z3tab,

如果proc1 按照zltab、z2tab 和z3tab 的顺序进行访问,那么,proc2 也应该按照以上顺序访

问这三张表。

● 对在交换期间添加记录频繁的表,使用群集索引(clustered),以减少多个用户添加记录到

该表的最后一页上,在表尾产生热点,造成死锁。这类表多为往来账的流水表,其特点是在

交换期间需要在表尾追加大量的记录,并且对已添加的记录不做或较少做删除操作。

● 对单张表中记录数不太多,且在交换期间select 或updata 较频繁的表可使用设置每页最

大行的办法,减少数据在表中存放的密度,模拟行级锁,减少在该表上死锁情况的发生。这

类表多为信息繁杂且记录条数少的表。

如:系统配置表或系统参数表。在定义该表时添加如下语句:

with max_rows_per_page=1

● 在存储过程、触发器、动态SQL 语句段中,若对某些整张表select 操作较频繁,则可能在

该表上与其他访问该表的用户产生死锁。对于检查账号是否存在,但被检查的字段在检查期

间不会被更新等非关键语句,可以采用在select 命令中使用at isolation read uncommitted 子

句的方法解决。该方法实际上降低了select 语句对整张表的锁级别,提高了其他用户对该表

操作的并发性。在系统高负荷运行时,该方法的效果尤为显著。

例如:

select*from titles at isolation read uncommitted

● 对流水号一类的顺序数生成器字段,可以先执行updata 流水号字段+1,然后再执行select

获取流水号的方法进行操作。

小结

笔者对同城清算系统进行压力测试时,分别对采用上述优化方法和不采用优化方法的两套系

统进行测试。在其他条件相同的情况下,相同业务笔数、相同时间内,死锁发生的情况如下:

采用优化方法的系统: 0 次/万笔业务;

不采用优化方法的系统:50~200 次/万笔业务。

所以,使用上述优化方法后,特别是在系统高负荷运行时效果尤为显著。总之,在设计、开

发数据库应用系统,尤其是OLTP 系统时,应该根据应用系统的具体情况,依据上述原则对

系统分别优化,为开发一套高效、可靠的应用系统打下良好的基础。

3SQL Server 死锁总结

1. 死锁原理

根据操作系统中的定义:死锁是指在一组进程中的各个进程均占有不会释放的资源,但因互相申请被其

他进程所站用不会释放的资源而处于的一种永久等待状态。

死锁的四个必要条件:

互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用。

请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源。

非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺。

循环等待条件(Circular wait):系统中若干进程组成环路,该环路中每个进程都在等待相邻进程正占用的资

源。

对应到 SQL Server 中,当在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源,此

时会造成这些任务永久阻塞,从而出现死锁;这些资源可能是:单行(RID,堆中的单行)、索引中的

键(KEY,行锁)、页(PAG,8KB)、区结构(EXT,连续的8 页)、堆或B 树(HOBT) 、表(TAB,

包括数据和索引)、文件(File,数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、

分配单元(Allocation_Unit)、整个数据库(DB)。一个死锁示例如下图所示:

说明:T1、T2 表示两个任务;R1 和R2 表示两个资源;由资源指向任务的箭头(如R1-<T1,R2-<T2)

表示该资源被改任务所持有;由任务指向资源的箭头(如T1-<S2,T2-<S1)表示该任务正在请求对应目标资

源;

其满足上面死锁的四个必要条件:

(1).互斥:资源S1 和S2 不能被共享,同一时间只能由一个任务使用;

(2).请求与保持条件:T1 持有S1 的同时,请求S2;T2 持有S2 的同时请求S1;

(3).非剥夺条件:T1 无法从T2 上剥夺S2,T2 也无法从T1 上剥夺S1;

(4).循环等待条件:上图中的箭头构成环路,存在循环等待。

2. 死锁排查

(1). 使用SQL Server 的系统存储过程sp_who 和sp_lock,可以查看当前数据库中的锁情况;进而根据

objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪个资源被锁,

用dbcc ld(@blk),可以查看最后一条发生给SQL Server 的Sql 语句;

CREATE Table #Who(spid int,

ecid int,

status nvarchar(50),

loginname nvarchar(50),

hostname nvarchar(50),

blk int,

dbname nvarchar(50),

cmd nvarchar(50),

request_ID int);

CREATE Table #Lock(spid int,

dpid int,

objid int,

indld int,

[Type] nvarchar(20),

Resource nvarchar(50),

Mode nvarchar(10),

Status nvarchar(10)

);

INSERT INTO #Who

EXEC sp_who active --看哪个引起的阻塞,blk

INSERT INTO #Lock

EXEC sp_lock --看锁住了那个资源id,objid

DECLARE @DBName nvarchar(20);

SET @DBName='NameOfDataBase'

SELECT #Who.* FROM #Who WHERE dbname=@DBName

SELECT #Lock.* FROM #Lock

JOIN #Who

ON #Who.spid=#Lock.spid

AND dbname=@DBName;

--最后发送到SQL Server 的语句

DECLARE crsr Cursor FOR

SELECT blk FROM #Who WHERE dbname=@DBName AND blk<<0;

DECLARE @blk int;

open crsr;

FETCH NEXT FROM crsr INTO @blk;

WHILE (@@FETCH_STATUS = 0)

BEGIN;

dbcc inputbuffer(@blk);

FETCH NEXT FROM crsr INTO @blk;

END;

close crsr;

DEALLOCATE crsr;

--锁定的资源

SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock

JOIN #Who

ON #Who.spid=#Lock.spid

AND dbname=@DBName

WHERE objid<<0;

DROP Table #Who;

DROP Table #Lock;

(2). 使用 SQL Server Profiler 分析死锁: 将 Deadlock graph 事件类添加到跟踪。此事件类使用死锁涉及到

的进程和对象的XML 数据填充跟踪中的 TextData 数据列。SQL Server 事件探查器可以将 XML 文档提取

到死锁 XML (.xdl) 文件中,以后可在 SQL Server Management Studio 中查看该文件。

3. 避免死锁

上面 1 中列出了死锁的四个必要条件,我们只要想办法破其中的任意一个或多个条件,就可以避免死锁

发生,一般有以下几种方法(FROM Sql Server 2005 联机丛书):

(1).按同一顺序访问对象。(注:避免出现循环)

(2).避免事务中的用户交互。(注:减少持有资源的时间,较少锁竞争)

(3).保持事务简短并处于一个批处理中。(注:同(2),减少持有资源的时间)

(4).使用较低的隔离级别。(注:使用较低的隔离级别(例如已提交读)比使用较高的隔离级别(例如可序

列化)持有共享锁的时间更短,减少锁竞争)

(5).使用基于行版本控制的隔离级别:2005 中支持快照事务隔离和指定READ_COMMITTED 隔离级别的

事务使用行版本控制,可以将读与写操作之间发生的死锁几率降至最低:

SET ALLOW_SNAPSHOT_ISOLATION ON --事务可以指定 SNAPSHOT 事务隔离级别;

SET READ_COMMITTED_SNAPSHOT ON --指定 READ_COMMITTED 隔离级别的事务将使用行版本

控制而不是锁定。默认情况下(没有开启此选项,没有加with nolock 提示),SELECT 语句会对请求的资源

加S 锁(共享锁);而开启了此选项后,SELECT 不会对请求的资源加S 锁。

注意:设置 READ_COMMITTED_SNAPSHOT 选项时,数据库中只允许存在执行 ALTER DATABASE 命

令的连接。在ALTER DATABASE 完成之前,数据库中决不能有其他打开的连接。数据库不必一定要处于

单用户模式中。

(6).使用绑定连接。(注:绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个

或多个会话共享相同的事务和锁(但每个回话保留其自己的事务隔离级别),并可以使用同一数据,而不会

有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话,也可以从包含不同会话的多个应用程序

中创建绑定会话。在一个会话中开启事务(begin tran)后,调用exec sp_getbindtoken @Token out;来取得

Token,然后传入另一个会话并执行EXEC sp_bindsession @Token 来进行绑定(最后的示例中演示了绑定

连接)。

4. 死锁处理方法:

(1). 根据2 中提供的sql,查看那个spid 处于wait 状态,然后用kill spid 来干掉(即破坏死锁的第四个必

要条件:循环等待);当然这只是一种临时解决方案,我们总不能在遇到死锁就在用户的生产环境上排查死

锁、Kill sp,我们应该考虑如何去避免死锁。

(2). 使用SET LOCK_TIMEOUT timeout_period(单位为毫秒)来设定锁请求超时。默认情况下,数据库没

有超时期限(timeout_period 值为-1,可以用SELECT @@LOCK_TIMEOUT 来查看该值,即无限期等待)。

当请求锁超过timeout_period 时,将返回错误。timeout_period 值为0 时表示根本不等待,一遇到锁就

返回消息。设置锁请求超时,破环了死锁的第二个必要条件(请求与保持条件)。

服务器: 消息 1222,级别 16,状态 50,行 1

已超过了锁请求超时时段。

(3). SQL Server 内部有一个锁监视器线程执行死锁检查,锁监视器对特定线程启动死锁搜索时,会标识线

程正在等待的资源;然后查找特定资源的所有者,并递归地继续执行对那些线程的死锁搜索,直到找到一

个构成死锁条件的循环。检测到死锁后,数据库引擎选择运行回滚开销最小的事务的会话作为死锁牺牲品,

返回1205 错误,回滚死锁牺牲品的事务并释放该事务持有的所有锁,使其他线程的事务可以请求资源并继

续运行。

5. 两个死锁示例及解决方法

5.1 SQL 死锁

(1). 测试用的基础数据:

CREATE TABLE Lock1(C1 int default(0));

CREATE TABLE Lock2(C1 int default(0));

INSERT INTO Lock1 VALUES(1);

INSERT INTO Lock2 VALUES(1);

(2). 开两个查询窗口,分别执行下面两段sql

--Query 1

Begin Tran

Update Lock1 Set C1=C1+1;

WaitFor Delay '00:01:00';

SELECT * FROM Lock2

Rollback Tran;

--Query 2

Begin Tran

Update Lock2 Set C1=C1+1;

WaitFor Delay '00:01:00';

SELECT * FROM Lock1

Rollback Tran;

上面的 SQL 中有一句WaitFor Delay '00:01:00',用于等待1 分钟,以方便查看锁的情况。

(3). 查看锁情况

在执行上面的 WaitFor 语句期间,执行第二节中提供的语句来查看锁信息:

Query1 中,持有Lock1 中第一行(表中只有一行数据)的行排他锁(RID:X),并持有该行所在页的意向

更新锁(PAG:IX)、该表的意向更新锁(TAB:IX);Query2 中,持有Lock2 中第一行(表中只有一行数据)的

行排他锁(RID:X),并持有该行所在页的意向更新锁(PAG:IX)、该表的意向更新锁(TAB:IX);

执行完 Waitfor,Query1 查询Lock2,请求在资源上加S 锁,但该行已经被Query2 加上了X 锁;

Query2 查询Lock1,请求在资源上加S 锁,但该行已经被Query1 加上了X 锁;于是两个查询持有资源并

互不相让,构成死锁。

(4). 解决办法

a). SQL Server 自动选择一条SQL 作死锁牺牲品:运行完上面的两个查询后,我们会发现有一条

SQL 能正常执行完毕,而另一个SQL 则报如下错误:

服务器: 消息 1205,级别 13,状态 50,行 1

事务(进程 ID xx)与另一个进程已被死锁在 lock 资源上,且该事务已被选作死锁牺牲品。请重新运行该

事务。

这就是上面第四节中介绍的锁监视器干活了。

b). 按同一顺序访问对象:颠倒任意一条SQL 中的Update 与SELECT 语句的顺序。例如修改第二条

SQL 成如下:

--Query2

Begin Tran

SELECT * FROM Lock1--在Lock1 上申请S 锁

WaitFor Delay '00:01:00';

Update Lock2 Set C1=C1+1;--Lock2:RID:X

Rollback Tran;

当然这样修改也是有代价的,这会导致第一条 SQL 执行完毕之前,第二条SQL 一直处于阻塞状态。单独

执行Query1 或Query2 需要约1 分钟,但如果开始执行Query1 时,马上同时执行Query2,则Query2 需

要2 分钟才能执行完;这种按顺序请求资源从一定程度上降低了并发性。

c). SELECT 语句加With(NoLock)提示:默认情况下SELECT 语句会对查询到的资源加S 锁(共享

锁),S 锁与X 锁(排他锁)不兼容;但加上With(NoLock)后,SELECT 不对查询到的资源加锁(或者加Sch-S

锁,Sch-S 锁可以与任何锁兼容);从而可以是这两条SQL 可以并发地访问同一资源。当然,此方法适合解

决读与写并发死锁的情况,但加With(NoLock)可能会导致脏读。

SELECT * FROM Lock2 WITH(NOLock)

SELECT * FROM Lock1 WITH(NOLock)

d). 使用较低的隔离级别。SQL Server 2000 支持四种事务处理隔离级别(TIL),分别为:READ

UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE;SQL Server 2005

中增加了SNAPSHOT TIL。默认情况下,SQL Server 使用READ COMMITTED TIL,我们可以在上

面的两条SQL 前都加上一句SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,来降低

TIL 以避免死锁;事实上,运行在READ UNCOMMITTED TIL 的事务,其中的SELECT 语句不对结果资

源加锁或加Sch-S 锁,而不会加S 锁;但还有一点需要注意的是:READ UNCOMMITTED TIL 允许脏

读,虽然加上了降低TIL 的语句后,上面两条SQL 在执行过程中不会报错,但执行结果是一个返回1,一

个返回2,即读到了脏数据,也许这并不是我们所期望的。

e). 在SQL 前加SET LOCK_TIMEOUT timeout_period,当请求锁超过设定的timeout_period 时间

后,就会终止当前SQL 的执行,牺牲自己,成全别人。

f). 使用基于行版本控制的隔离级别(SQL Server 2005 支持):开启下面的选项后,SELECT 不会对

请求的资源加S 锁,不加锁或者加Sch-S 锁,从而将读与写操作之间发生的死锁几率降至最低;而且不会

发生脏读。啊

SET ALLOW_SNAPSHOT_ISOLATION ON

SET READ_COMMITTED_SNAPSHOT ON

g). 使用绑定连接(使用方法见下一个示例。)

5.2 程序死锁(SQL 阻塞)

看一个例子:一个典型的数据库操作事务死锁分析,按照我自己的理解,我觉得这应该算是C#程序

中出现死锁,而不是数据库中的死锁;下面的代码模拟了该文中对数据库的操作过程:

//略去的无关的code

SqlConnection conn = new SqlConnection(connectionString);

conn.Open();

SqlTransaction tran = conn.BeginTransaction();

string sql1 = "Update Lock1 SET C1=C1+1";

string sql2 = "SELECT * FROM Lock1";

ExecuteNonQuery(tran, sql1); //使用事务:事务中Lock 了Table

ExecuteNonQuery(null, sql2); //新开一个connection 来读取Table

public static void ExecuteNonQuery(SqlTransaction tran, string sql)

{

SqlCommand cmd = new SqlCommand(sql);

if (tran != null)

{

cmd.Connection = tran.Connection;

cmd.Transaction = tran;

cmd.ExecuteNonQuery();

}

else

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

cmd.Connection = conn;

cmd.ExecuteNonQuery();

}

}

}

执行到 ExecuteNonQuery(null, sql2)时抛出SQL 执行超时的异常,下图从数据

库的角度来看该问题:

代码从上往下执行,会话 1 持有了表Lock1 的X 锁,且事务没有结束,回话1 就一直持有X 锁不释

放;而会话2 执行select 操作,请求在表Lock1 上加S 锁,但S 锁与X 锁是不兼容的,所以回话2 的被阻

塞等待,不在等待中,就在等待中获得资源,就在等待中超时。。。从中我们可以看到,里面并没有出现

死锁,而只是SELECT 操作被阻塞了。也正因为不是数据库死锁,所以SQL Server 的锁监视器无法检测

到死锁。

我们再从C#程序的角度来看该问题:

C#程序持有了表Lock1 上的X 锁,同时开了另一个SqlConnection 还想在该表上请求一把S 锁,图中

已经构成了环路;太贪心了,结果自己把自己给锁死了。。。

虽然这不是一个数据库死锁,但却是因为数据库资源而导致的死锁,上例中提到的解决死锁的方法在

这里也基本适用,主要是避免读操作被阻塞,解决方法如下:

a). 把SELECT 放在Update 语句前:SELECT 不在事务中,且执行完毕会释放S 锁;

b). 把SELECT 也放加入到事务中:ExecuteNonQuery(tran, sql2);

c). SELECT With(NOLock)提示:可能产生脏读;

d). 降低事务隔离级别:SELECT 语句前加SET TRANSACTION ISOLATION LEVEL READ

UNCOMMITTED;同上,可能产生脏读;

e). 使用基于行版本控制的隔离级别(同上例)。

g). 使用绑定连接:取得事务所在会话的token,然后传入新开的connection 中;执行EXEC

sp_bindsession @Token 后绑定了连接,最后执行exec sp_bindsession null;来取消绑定;最后需要注意的

四点是:

(1). 使用了绑定连接的多个connection 共享同一个事务和相同的锁,但各自保留自己的事务隔离级别;

(2). 如果在sql3 字符串的“exec sp_bindsession null”换成“commit tran”或者“rollback tran”,

则会提交整个事务,最后一行C#代码tran.Commit()就可以不用执行了(执行会报错,因为事务已经结束了

-,-)。

(3). 开启事务(begin tran)后,才可以调用exec sp_getbindtoken @Token out 来取得Token;如果不

想再新开的connection 中结束掉原有的事务,则在这个connection close 之前,必须执行“exec

sp_bindsession null”来取消绑定连接,或者在新开的connectoin close 之前先结束掉事务(commit/tran)。

(4). (Sql server 2005 联机丛书)后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发

工作中使用该功能,并着手修改当前还在使用该功能的应用程序。请改用多个活动结果集 (MARS) 或分布

式事务。

tran = connection.BeginTransaction();

string sql1 = "Update Lock1 SET C1=C1+1";

ExecuteNonQuery(tran, sql1); //使用事务:事务中Lock 了测试表Lock1

string sql2 = @"DECLARE @Token varchar(255);

exec sp_getbindtoken @Token out;

SELECT @Token;";

string token = ExecuteScalar(tran, sql2).ToString();

string sql3 = "EXEC sp_bindsession @Token;Update Lock1 SET C1=C1+1;exec sp_bindsession null;";

SqlParameter parameter = new SqlParameter("@Token", SqlDbType.VarChar);

parameter.Value = token;

ExecuteNonQuery(null, sql3, parameter); //新开一个connection 来操作测试表Lock1

tran.Commit();

附:锁兼容性(FROM SQL Server 2005 联机丛书)

锁兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已被另一事务锁定,则仅当请求锁的

模式与现有锁的模式相兼容时,才会授予新的锁请求。如果请求锁的模式与现有锁的模式不兼容,则请求

新锁的事务将等待释放现有锁或等待锁超时间隔过期。

4with(nolock)解释

所有Select With (NoLock)解决阻塞死锁

在查询语句中使用 NOLOCK 和 READPAST

处 理 一 个 数 据 库 死 锁 的 异 常 时 候 , 其 中 一 个 建 议就 是 使 用 NOLOCK 或者 READPAST 。有

关 NOLOCK 和 READPAST 的一些技术知识点:

对于非银行等严格要求事务的行业,搜索记录中出现或者不出现某条记录,都是在可容忍范围内,所

以碰到死锁,应该首先考虑,我们业务逻辑是否能容忍出现或者不出现某些记录,而不是寻求对双方

都加锁条件下如何解锁的问题。

NOLOCK 和 READPAST 都是处理查询、插入、删除等操作时候,如何应对锁住的数据记录。但是这

时候一定要注意NOLOCK 和 READPAST 的局限性,确认你的业务逻辑可以容忍这些记录的出现或

者不出现:

简单来说:

NOLOCK 可能把没有提交事务的数据也显示出来.

READPAST 会把被锁住的行不显示出来

不使用 NOLOCK 和 READPAST ,在 Select 操作时候则有可能报错误:事务(进程 ID **)与另一个进

程被死锁在锁 资源上,并且已被选作死锁牺牲品。

下面就来演示这个情况。

为了演示两个事务死锁的情况,我们下面的测试都需要在SQL Server Management Studio 中打开两个

查询窗口。保证事务不被干扰。

演示一 没有提交的事务,NOLOCK 和 READPAST 处理的策略:

查询窗口一请执行如下脚本:

CREATE TABLE t1 (c1 int IDENTITY(1,1), c2 int)

go

BEGIN TRANSACTION

insert t1(c2) values(1)

在查询窗口一执行后,查询窗口二执行如下脚本:

select count(*) from t1 WITH(NOLOCK)

select count(*) from t1 WITH(READPAST)

结果与分析:

查询窗口二依次显示统计结果为: 1、0

查询窗口一的命令没有提交事务,所以 READPAST 不会计算没有提交事务的这一条记录,这一条被

锁住了,READPAST 看不到;而NOLOCK 则可以看到被锁住的这一条记录。

如果这时候我们在查询窗口二中执行:

select count(*) from t1 就会看到这个执行很久不能执行完毕,因为这个查询遇到了一个死锁。

清除掉这个测试环境,需要在查询窗口一中再执行如下语句:

ROLLBACK TRANSACTION

drop table t1

演示二:对被锁住的记录,NOLOCK 和 READPAST 处理的策略

这个演示同样需要两个查询窗口。

请在查询窗口一中执行如下语句:

CREATE TABLE t2 (UserID int , NickName nvarchar(50))

go

insert t2(UserID,NickName) values(1,'郭红俊')

insert t2(UserID,NickName) values(2,'蝈蝈俊')

go

BEGIN TRANSACTION

update t2 set NickName = '蝈蝈俊.net' where UserID = 2

请在查询窗口二中执行如下脚本:

select * from t2 WITH(NOLOCK) where UserID = 2

select * from t2 WITH(READPAST) where UserID = 2

结果与分析:

查询窗口二中, NOLOCK 对应的查询结果中我们看到了修改后的记录,READPAST 对应的查询结果

中我们没有看到任何一条记录。这种情况下就可能发生脏读

5、事务(进程 ID 314)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺

牲品。请重新运行该事务。

事务(进程 ID )与另一个进程已被死锁在 lock 资源上,且该事务已被选作死锁牺牲品。

请重新运行该事务

其实所有的死锁最深层的原因就是一个:资源竞争 表现一:

一个用户A 访问表A(锁住了表A),然后又访问表B

另一个用户B 访问表B(锁住了表B),然后企图访问表A

这时用户A 由于用户B 已经锁住表B,它必须等待用户B 释放表B,才能继续,好了他老人家

就只好老老实实在这等了

同样用户B 要等用户A 释放表A 才能继续这就死锁了

解决方法:

这种死锁是由于你的程序的BUG 产生的,除了调整你的程序的逻辑别无他法

仔细分析你程序的逻辑,

1:尽量避免同时锁定两个资源

2: 必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源.

表现二:

用户A 读一条纪录,然后修改该条纪录

这是用户B 修改该条纪录

这里用户A 的事务里锁的性质由共享锁企图上升到独占锁(for update),而用户B 里的独占锁

由于A 有共享锁存在所以必须等A 释

放掉共享锁,而A 由于B 的独占锁而无法上升的独占锁也就不可能释放共享锁,于是出现

了死锁。

这种死锁比较隐蔽,但其实在稍大点的项目中经常发生。

解决方法:

让用户A 的事务(即先读后写类型的操作),在select 时就是用Update lock

语法如下:

select * from table1 with(updlock) where ....

==========================

在联机事务处理(OLTP)的数据库应用系统中,多用户、多任务的并发性是系统最重要的技术

指标之一。为了提高并发性,目前大部分RDBMS 都采用加锁技术。然而由于现实环境的复

杂性,使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加锁技术,最

小化死锁是开发联机事务处理系统的关键。

死锁产生的原因

在联机事务处理系统中,造成死机主要有两方面原因。一方面,由于多用户、多任务的并发

性和事务的完整性要求,当多个事务处理对多个资源同时访问时,若双方已锁定一部分资源

但也都需要对方已锁定的资源时,无法在有限的时间内完全获得所需的资源,就会处于无限

的等待状态,从而造成其对资源需求的死锁。

另一方面,数据库本身加锁机制的实现方法不同,各数据库系统也会产生其特殊的死锁情况。

如在Sybase SQL Server 11 中,最小锁为2K 一页的加锁方法,而非行级锁。如果某张表的记

录数少且记录的长度较短(即记录密度高,如应用系统中的系统配置表或系统参数表就属于

此类表),被访问的频率高,就容易在该页上产生死锁。

几种死锁情况及解决方法

清算应用系统中,容易发生死锁的几种情况如下:

● 不同的存储过程、触发器、动态SQL 语句段按照不同的顺序同时访问多张表;

● 在交换期间添加记录频繁的表,但在该表上使用了非群集索引(non‐clustered);

● 表中的记录少,且单条记录较短,被访问的频率较高;

● 整张表被访问的频率高(如代码对照表的查询等)。

以上死锁情况的对应处理方法如下:

● 在系统实现时应规定所有存储过程、触发器、动态SQL 语句段中,对多张表的操作总是

使用同一顺序。如:有两个存储过程proc1、proc2,都需要访问三张表zltab、z2tab 和z3tab,

如果proc1 按照zltab、z2tab 和z3tab 的顺序进行访问,那么,proc2 也应该按照以上顺序访

问这三张表。

● 对在交换期间添加记录频繁的表,使用群集索引(clustered),以减少多个用户添加记录到

该表的最后一页上,在表尾产生热点,造成死锁。这类表多为往来账的流水表,其特点是在

交换期间需要在表尾追加大量的记录,并且对已添加的记录不做或较少做删除操作。

● 对单张表中记录数不太多,且在交换期间select 或updata 较频繁的表可使用设置每页最

大行的办法,减少数据在表中存放的密度,模拟行级锁,减少在该表上死锁情况的发生。这

类表多为信息繁杂且记录条数少的表。

如:系统配置表或系统参数表。在定义该表时添加如下语句:

with max_rows_per_page=1

● 在存储过程、触发器、动态SQL 语句段中,若对某些整张表select 操作较频繁,则可能在

该表上与其他访问该表的用户产生死锁。对于检查账号是否存在,但被检查的字段在检查期

间不会被更新等非关键语句,可以采用在select 命令中使用at isolation read uncommitted 子

句的方法解决。该方法实际上降低了select 语句对整张表的锁级别,提高了其他用户对该表

操作的并发性。在系统高负荷运行时,该方法的效果尤为显著。

例如:

select*from titles at isolation read uncommitted

● 对流水号一类的顺序数生成器字段,可以先执行updata 流水号字段+1,然后再执行select

获取流水号的方法进行操作。

小结

笔者对同城清算系统进行压力测试时,分别对采用上述优化方法和不采用优化方法的两套系

统进行测试。在其他条件相同的情况下,相同业务笔数、相同时间内,死锁发生的情况如下:

采用优化方法的系统: 0 次/万笔业务;

不采用优化方法的系统:50~200 次/万笔业务。

所以,使用上述优化方法后,特别是在系统高负荷运行时效果尤为显著。总之,在设计、开

发数据库应用系统,尤其是OLTP 系统时,应该根据应用系统的具体情况,依据上述原则对

系统分别优化,为开发一套高效、可靠的应用系统打下良好的基础。

============

‐‐转

/********************************************************

// 创建 :

// 日期 :

// 修改 :

//

// 说明 : 查看数据库里阻塞和死锁情况

********************************************************/

use master

go

CREATE procedure sp_who_lock

as

begin

declare @spid int,@bl int,

@intTransactionCountOnEntry int,

@intRowcount int,

@intCountProperties int,

@intCounter int

create table #tmp_lock_who (

id int identity(1,1),

spid smallint,

bl smallint)

IF @@ERROR<>0 RETURN @@ERROR

insert into #tmp_lock_who(spid,bl) select 0 ,blocked

from (select * from sysprocesses where blocked>0 ) a

where not exists(select * from (select * from sysprocesses

where blocked>0 ) b

where a.blocked=spid)

union select spid,blocked from sysprocesses where blocked>0

IF @@ERROR<>0 RETURN @@ERROR

‐‐ 找到临时表的记录数

select @intCountProperties = Count(*),@intCounter = 1

from #tmp_lock_who

IF @@ERROR<>0 RETURN @@ERROR

if @intCountProperties=0

select '现在没有阻塞和死锁信息' as message

‐‐ 循环开始

while @intCounter <= @intCountProperties

begin

‐‐ 取第一条记录

select @spid = spid,@bl = bl

from #tmp_lock_who where Id = @intCounter

begin

if @spid =0

select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))

+ '进程号,其执行的SQL 语法如下'

else

select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被'

+ '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL 语法如下'

DBCC INPUTBUFFER (@bl )

end

‐‐ 循环指针下移

set @intCounter = @intCounter + 1

end

drop table #tmp_lock_who

return 0

end

GO

==========================

呵呵,解决死锁,光查出来没有多大用处,我原来也是用这个存储过程来清理死锁的

我解决死锁的方式主要用了:

1 优化索引

2 对所有的报表,非事务性的select 语句 在from 后都加了 with (nolock) 语句

3 对所有的事务性更新尽量使用相同的更新顺序来执行

现在已解决了死锁的问题,希望能对你有帮助

with (nolock)的用法很灵活 可以说只要有 from 的地方都可以加 with (nolock) 标记来取消

产生意象锁,这里 可以用在 delete update,select 以及 inner join 后面的from 里,对整个

系统的性能提高都很有帮助

==========================

use master ‐‐必须在master 数据库中创建

go

if exists (select * from dbo.sysobjects where id = object_id(N [dbo].[p_lockinfo] ) and

OBJECTPROPERTY(id, N IsProcedure ) = 1)

drop procedure [dbo].[p_lockinfo]

GO

/*‐‐处理死锁

查看当前进程,或死锁进程,并能自动杀掉死进程

因为是针对死的,所以如果有死锁进程,只能查看死锁进程

当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

‐‐邹建 2004.4‐‐*/

/*‐‐调用示例

exec p_lockinfo

‐‐*/

create proc p_lockinfo

@kill_lock_spid bit=1, ‐‐是否杀掉死锁的进程,1 杀掉, 0 仅显示

@show_spid_if_nolock bit=1 ‐‐如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示

as

declare @count int,@s nvarchar(1000),@i int

select id=identity(int,1,1),标志,

进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,

数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU 时间=cpu,

登陆时间=login_time,打开事务数=open_tran, 进程状态=status,

工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,

域名=nt_domain,网卡地址=net_address

into #t from(

select 标志='死锁的进程',

spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,

status,hostname,program_name,hostprocess,nt_domain,net_address,

s1=a.spid,s2=0

from master..sysprocesses a join (

select blocked from master..sysprocesses group by blocked

)b on a.spid=b.blocked where a.blocked=0

union all

select '|_牺牲品_>',

spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,

status,hostname,program_name,hostprocess,nt_domain,net_address,

s1=blocked,s2=1

from master..sysprocesses a where blocked<>0

)a order by s1,s2

select @count=@@rowcount,@i=1

if @count=0 and @show_spid_if_nolock=1

begin

insert #t

select 标志='正常的进程',

spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,

open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address

from master..sysprocesses

set @count=@@rowcount

end

if @count>0

begin

create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))

if @kill_lock_spid=1

begin

declare @spid varchar(10),@标志 varchar(10)

while @i<

begin

select @spid=进程ID,@标志=标志 from #t where

insert #t1 exec('dbcc inputbuffer('+@spid+')')

if @标志='死锁的进程' exec('kill '+@spid)

set @i=@i+1

end

end

else

while @i<

begin

select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where

insert #t1 exec(@s)

set @i=@i+1

end

select a.*,进程的SQL 语句=b.EventInfo

from #t a join #t1 b on a.id=b.id

end

GO

三、最终解方法

1、将所有查询中添加了 with (nolock)

例如:select * from tables with (nolock) where 1=1

2、在SQL 2005 中修改 SET LOCK_TIMEOUT 0 值为0 时表示根本不等待

查询方法如下:

SELECT @@LOCK_TIMEOUT

3、容错,不让程序跑的自动关闭了。__