sqlsugar sqlite 多线程 sqlserver多线程查询

一、使用工具观察与分析数据库中锁信息

      对于并发系统、对于有大量读写数据库操作的业务系统等，当多人同时访问数据库时，最复杂的情况之一就是大量的事务与资源纠缠在一起，相互被锁而彼此等待，也称为死锁。当数据库中出现很多很多锁时，系统瞬间就无法提供正常服务。此时观察系统资源的使用情况，会发现CPU使用率不高，内存占用量也不高，还有很多未使用的内存，网络带宽也充足，硬盘也不繁忙，通过数据库管理工具查询的话，SQL SERVER中的数据也正常无误，但是使用系统的用户访问此数据库时却要需要等很多久很久，更多的就出现连接超时，数据库无响应。

      此现象类似高速公路上有大量的车辆进入，而在收费口却只开了一个、二个。造成收费口的车辆拥堵，而后方却又有大量的车辆涌入，这时所有的车辆都会慢下来，驾驶员彼此再减速刹车互相等待，最后导致后方的车子会被迫停下来，形成高速公路停车场。（就如第一次节假日高速公路免费通行那天，即2012年国庆高速公路大堵车的情形，高速公路成停车场）

      如果数据库中在设计时，设计不当，设计有过热数据表时，也就是所有应用程序的访问行为都跟某张数据表有关，甚至集中访问某些字段，这就像前述例子中的所有车辆都在同一地点（高速公路收费口）驶上高速公路。这时耗时与快速的两种访问行为混合在一起，就像大货车与小轿车一起争车道，很容易发生彼此锁定，形成死锁而动弹不得的情况。

      此类性能延迟现象，尤其在数据使用一段较长时间之后容易发生，当新系统刚上线时，或SQL语句写的不好，如Select * from SaleInfo 且没有where条件，初期因数据量不大，可以瞬间取回结果而不影响别人。但当系统使用一段时间之后，数据库中的数量增加，使用人数增多，使用方式改变。不好的SQL语句就出现运行迟缓，这时数据库开始积聚等待处理的需求，也就是彼此干扰而造成锁定，交互作用后，可能瞬间瘫痪整个系统。当你观察当前数据库的锁定时，可能会发现存在几千个锁定，很难理出头绪。

       本文首先来讨论可以使用哪些工具来观察与分析SQL SERVER中的锁信息。当然，所有的性能调校都需要先了解情况，最好是可重复发生的情景，例如在每天什么时间，多少用户上线后，执行了什么样的功能后，等等。先通盘了解情况，然后评估SQL SERVER的问题，再进一步深入观察SQL SERVER。

第一步，使用SQL SERVER提供图形界面工具观察哪些进程被加锁。

 1 ) SQL SERVER 2005可以通过Microsoft SQL Server Management Studio的“管理--》活动监视器”--》“右键”--》“进程信息”，这里呈现连接、锁定的各种细节。如下图1。

 

                                                                             图1

 

 

2) SQL SERVER 2005 的Microsoft SQL Server Management Studio提供图形界面工具中的信息不会自动更新。刷新“活动监视器”界面中的内容，需要手动使用鼠标点击上方工具栏上的“刷新”按钮。如下图2，是未点击“刷新”按钮之前的显示的内容，图3，是点击“刷新”按钮之后的，显示的内容。

　　　　　　　　图2

　　　　　　　　图3

 

2） SQL SERVER 2005中可以通过下图中的设置，设置成自动更新的时间周期。如下图4。

 

 

                               图4

 

 

4） SQL SERVER 2005的Microsoft SQL Server Management Studio中的“活动监视器--》进程信息”提供相当多的信息，其中“等待类型”与“阻塞者”可呈现进程彼此的关系及进程的状态信息。

如果要查询“锁”的细节信息，则可以点击右键窗口中的“活动监视器--》按进程分类的锁”及“活动监视器--》按对象分类的锁”两个标记进行观察。

活动监视器--》按进程分类的锁

如下图5。

 

                                               图5

活动监视器--》按对象分类的锁

如下图6。

 

                                                图6

 

接下来说一下SQL SERVER 2008中的活动监视器的使用：

1) SQL SERVER 2008的活动监视器就不在管理中了。一、点击工具栏上的图标

。二、使用右键点击数据库服务器，如下图7。然后都会出现如图8的图形界面工具。 

 

                图7

 

                       图8

 

2） SQL SERVER 2008中默认就是自动更新的，自动更新时间间隔为10秒。可以通过下图中的设置，设置成自动更新的时间周期。如下图9。右键单击“概述”，选择“刷新间隔”，然后选择活动监视器获取新的实例信息所用的间隔。

 

 

          图9

 

 

3） SQL SERVER 2008的Microsoft SQL Server Management Studio中的“活动监视器--》进程”只提供了一些基本的信息，其中“等待类型”与“阻塞者”可呈现进程彼此的关系及进程的状态信息。所提供的锁定进程的信息没有SQL SERVER 2005中所提供的详细。

 

                                      图10

第二步，通过SQL语句分析锁定情况

由于SQL SERVER 2008相比SQL SERVER 2005中的“活动监视器”有了比较大的改变，所以下而我们通过SQL语句进行分析，使用SQL语句进行分析需要通过SP_WHO、SP_WHO2、SP_LOCK等系统存储过程、Master.sys.sysprocesses系统视图，或从SQL 2005（2008）新提供的动态视图管理（DMV）sys.dm_exec_session、sys.dm_tran_locks等获取相关信息。

通过master.sys.sysprocesses 视图找出最初锁住资源及导致后面一连串进程被迫停止的等待源头。

下面我们举一个例子来具体说明，以下代码在SQL SERVER 2005/2008中都可以使用：

--1.创建测试表

CREATE TABLE [dbo].[Book](

[bookid] [int] NOT NULL,

[Name] [varchar](60) NULL,

[category] [varchar](10) NULL,

[numberofcopies] [int] NULL,

[AuthorID] [int] NULL,

 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 

(

[bookid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (1, N'SQL 2008', N'MS', 4, 1)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (2, N'SharePoint 2007', N'MS', 3, 2)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (3, N'SharePoint 2010', N'MS', 5, 2)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (5, N'DB2', N'IBM', 10, 3)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (7, N'SQL 2012', N'MS', 7, 1)

 

--2.测试示例

--列出最初锁定资源，导致一连串其他进程被锁住的起始源头。

例一

if exists(select * from master.sys.sysprocesses where spid in (select blocked from master.sys.sysprocesses))

begin

---确定进程被锁住的其他资源

select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)

,用户机器名称=SUBSTRING(hostname,1,12)

,是否被锁住=convert(char(3),blocked)

,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型

,last_batch 最后批处理时间,open_tran 未提交事务的数量

from master.sys.sysprocesses

--列出锁住别人（在别的进程中blocked字段中出现的值）但自己未被锁住(blocked=0)

Where spid in (select blocked from master.sys.sysprocesses) and blocked=0

end

else 

begin

select '没有被锁住的进程'

end 

 

---接下来我们通过以下代码来构造一条进程被另外一条进程锁住的情况。

--例二

Use test

Go

Begin tran

update book set Name='MS SQL 2008'

where bookid=1

---切换到另一个查询界面，执行以下代码

--例三

Use test

Go

select * from Book where bookid=1

go

 

 3. 在SQL SERVER 2005/2008中的Microsoft SQL Server Management Studio中打开一个新的查询界面，执行 exec SP_LOCK。结果如下图。

 

图1

从图1中可以观察到两个进程的相互作用，其中进程“53”要求模式为“独占（X）”，已经被获取允许“GRANT”；进程“56”要求模式为“共享（S）”正等候（WAIT）处理。

 

如上图1中进程“56”（执行SELECT语句的查询连接）被进程“53”（执行UPDATE语句的查询连接）封锁的现象，并从Book数据表锁定可以看出是因为“独占”锁定某一条索引键值（要求类型为KEY），导致进程“56”放置共享锁定（要求模式为“S”），而在等待状态（要求状态为WAIT）。

4. 在SQL SERVER 2005/2008中的Microsoft SQL Server Management Studio中打开一个新查询界面，通过另外一条连接来执行程序代码（例一），执行结果如下图。

 

图2

在上图2中可以看出例二查询代码开启事务之后，未关闭事务，因此状态（status)为sleeping，但并未被其他进程锁住(blk)，所以“是否被锁住”列的数据为0，没有执行命令，也没有等待某种资源。另外由于该查询的数据库连接是Test，所以数据库名称为Test。

5. 查询sysprocesses系统视图呈现有问题的交易的现象可能有许多种，但最常见的一种就是status字段等于sleeping，waittype字段等于0x0000，last_batch字段表示离最后一次批处理执行的时候已经有一段距离了，以及open_tran字段大于0。例如，直接执行代码例二，这时事务已经开启，但是迟迟没有结束，就可能是程序没有做好事务管理。

可以在在SQL SERVER 2005/2008中的Microsoft SQL Server Management Studio中打开一个新查询界面中执行下面的语句，以查询有问题的连接

select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)

,用户机器名称=SUBSTRING(hostname,1,12)

,是否被锁住=convert(char(3),blocked)

,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型

,last_batch 最后批处理时间,open_tran 未提交事务的数量

from master.sys.sysprocesses

Where  status='sleeping' and waittype=0x0000 and open_tran>0

 

 

如下图。

 

图3

status字段等于sleeping表示没有指示符正在执行，waittype字段等于0x0000代表此连接没有等待任何资源，last_batch字段表示最后一次SQL语句执行的时间，如果此时间离现在有一段时间了，以及open_tran字段大于0，就有问题了。一段时间过去了，没有等待任何资源，也没有执行任何SQL语句，那么为什么还要开启事务？

除了上述查询sysprocesses系统视图之外，SQL SERVER 2005/2008 可以通过“sys.dm_tran_locks动态管理视图”呈现目前使用中相关的锁定信息。返回的每一条记录都代表一个已经授权或等待授权的锁定。在结果集的数据行中，主要分成“资源”与“请求”两类，其字段分别以resource与request为前缀。资源群组描述已经锁定或等待的资源，而请求群组则描述已经获取或等待中的锁定请求。

 

 

--例四

select t1.resource_type [资源锁定类型],DB_NAME(resource_database_id) as 数据库名

,t1.resource_associated_entity_id 锁定对象,t1.request_mode as 等待者请求的锁定模式

,t1.request_session_id 等待者SID

,t2.wait_duration_ms 等待时间

,(select TEXT from sys.dm_exec_requests r cross apply

sys.dm_exec_sql_text(r.sql_handle) where r.session_id=t1.request_session_id) as 等待者要执行的SQL

,(select SUBSTRING(qt.text,r.statement_start_offset/2+1,

(case when r.statement_end_offset=-1 then DATALENGTH(qt.text) else r.statement_end_offset end -r.statement_start_offset)/2+1

)

from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle)qt

where r.session_id=t1.request_session_id) 等待者正要执行的语句

,t2.blocking_session_id [锁定者SID]

,(select TEXT from sys.sysprocesses p cross apply

sys.dm_exec_sql_text(p.sql_handle)

where p.spid=t2.blocking_session_id

) 锁定者执行语句

from sys.dm_tran_locks t1,sys.dm_os_waiting_tasks t2

where t1.lock_owner_address=t2.resource_address

 

在Sql 2005 的TEST执行上面的代码，结果如下图。(这是针对Wbk_pde_list数据表)

 

图4

在Sql 2008 的TEST数据库上执行上面的代码，结果如下图。(这是针对BOOK数据表)

 

 

图5

 

备注：

以上第二步是通过在Microsoft SQL Server Management Studio中执行代码进行查询与分析加锁情况，而在SQL SERVER 2005中则可以通过Microsoft SQL Server Management Studio管理获取相当多的信息，方便让你决定当前应该采取什么样步骤。

上而第二步中获取的信息都可以在SQL SERVER 2005中通过Microsoft SQL Server Management Studio中的“活动监视器”获取。

例如通过“活动监视器--》按对象分类的锁”，下拉菜单中选择相应的对象。（如下图）

 

 

 

接下来看看SP_WHO2这个系统存储过程，如果你查询这个系统存储过程的源代码，就可以发现这个系统存储过程是整理master.sys.sysprocesses系统视图中的内容。在此用sp_who2来说明一下。

第一步，在查询分析器中执行例二，例三代码。(就是上一篇文章SQL SERVER 查询性能优化——分析事务与锁（二）中的示例)--例二

第二步，再打开一个查询分析器界面，在此界面中输入exec sp_who2，如下图，在此界面中你可以很容易的观察到锁与被锁的关联，看到进程“56”被“53”锁住。

Use test

Go

Begin tran

update book set Name='MS SQL 2008'

where bookid=1

---切换到另一个查询界面，执行以下代码

--例三

Use test

Go

select * from Book where bookid=1

go

 

 

 

 

你可以通过dbcc inputbuffer(53)来查看进程“53”所执行的查询语句。如下图1、2。

 

Sql 2008中的 wbk_pde_list表

 

 

图1

Book表

 

图2

 

当然，如果你使用SQL SERVER 2005也可以通过Microsoft SQL Server Management Studio中的“活动监视器--》进程信息”直接以鼠标双击某条进程，便可以看到此进程所执行的查询语句。如下图3。

 

 图3

你还可以通过sp_lock系统存储过程来观察进程“53”和“56”的结果。执行如下命令

Exec sp_lock 53

Exec sp_lock 56

然后得到如下图结果：

Book表

 

图4

 

以上语句执行结果，同SQL SERVER 2005中的Microsoft SQL Server Management Studio中的“活动监视器--》按进程分类的锁”有异曲同工之处。

Sql 2005

 

 图5

当然在Sql 2008中就只能执行以下的SQL 语句了。

Exec sp_lock 54

Exec sp_lock 55

 

图6

 

如上，图6中的Type字段如果是PAG，则Resource表示的是该分页在数据库的第几个文件上。以及分页编号。我们可以通过DBCC PAGE来观察该分布。

 如果indId为1，则表示为聚集索引，则dbcc page查询出来的是整个分页的细节，如果IndId大于1，则表示为非聚集索引，则dbcc page查询出来的是索引键值与哈希值。如下图7。

Dbcc traceon(3604)

dbcc page(28,1,10683,3)

 

Book

 

图7

 

结合图5对象ID、说明与图7中的KeyHashValue字段相比较，就可以进一步看出什么样的记录被锁住了。

 也可以结合结合图6中的RESOURCE与图7中的KeyHashValue字段相比较，就可以进一步看出什么样的记录被锁住了。

注：此处的图7不是图6的明细。

select db_name(28) 数据库名称,OBJECT_NAME(117575457) 表名

,(select name from sys.indexes where OBJECT_ID=117575457 and index_ID=54) 索引名称

 

 

 

 

另外可以打开 SQL Profiler观察多人交互情况。

 

 

综上所述，你可以从以下几方面来观察数据库是否因为锁与被锁而造成系统运行出现问题。

1.通过Microsoft SQL Server Management Studio或SP_WHO2系统存储过程来观察数据库中是否有许多进程被锁。

2.观察master.sys.sysprocesses系统视图内，被锁进程中的waittime字段的值是否异常的大。

3.SQL Profiler工具所录制的结果中，有许多attention事件，代表SQL语句执行过久没有响应，前端程序放弃执行。

4.SQL SERVER所在服务器并没有显的很忙碌。例如，CPU，内存，硬盘，网络等硬件资源使用率并不是很高，但系统的效率却不高，或是正相反，上述资源由于某个操作而持续高度使用，但是该操作一直做不完，导致它持有的资源都无法释放。

5.通过Microsoft SQL Server Management Studio、性能监视器、SQL PROFILER等结果，进行交叉分析以相互印证。

 

二、死锁的原因及相关处理

       死锁的原因很多，尤其是前端应用程序没有合理的使用事务，或者对错误处理不当而导致事务长期持有而没有关闭。接下来讲讲最常见的几种死锁情况，并提供可能的解决方法。

1.费时的查询事务

2.不正确的事务或事务隔离等级设置

3.事务未正确处理

4.未检测到的分布式死锁

5.锁定数据粒度太高或太低

6.Compile Blocking

 

  （一）费时的查询事务

       当查询或事务所花的时间较长时，可由SQL SERVER 2005/2008 动态管理视图sys.dm_exec_requests提供相关信息（也可观察sysprocesses系统视图），如status字段为“running”，wait_type为非“NULL”值。“running”代表该进程依然在执行，而wait_type则表示该进程是否在等待资源。如下图。

 

       这也可以从SQL SERVER 2005的Microsoft SQL Server Management Studio管理工具中的活动监视器--》进程信息--》查看相关信息。如下图，所圈出的相关字段可以观察进程之间的相关信息。

 

 

 

      如上图所示，进程“55”被“54”锁住，也可以从“等待类型”列中看出相关信息。

     通过SQL PROFILER工具中观察“T SQL”事件下的“SQL StmtCompleted/SQL BatchComplete”，或是“存储过程”事件类别下的“SP StmtCompleted/SP BatchComplete/RPC Complete ”等事件，可观察SQL语句执行情况，并通过TextData(呈现T-SQL语句执行内容)及Duration(语句执行所需的时间)字段判断哪一句语句是否执行时间过长而导致锁定行为。如下图1、图2。

 

图1

      如图1中圈出来的地方，没有结束时间，所以此SQL语句还在继续等待。

 

图2

     如图2中圈出来的地方，虽然有执行结束时间，但是duration的执行时间过长，也就是说此SQL语句等待了这么长的时间，等待其他事务释放资源。

 

     如果查询语句使用大量系统资源导致查询耗时过长，可能伴随的现象有：处理器，硬盘I/O，内存等的使用率很高。SQL PROFILER工具中的“错误和警告”事件类别中的Missing Column Statistics，产生过多“存储过程”事件类型的SP:Recompile事件也值得注意，前者表示无法产生有效的执行计划，后者表示存储过程的编写方式，无法提供高速缓存执行计划。“错误和警告”事件类别中的Hash warning和Sort warning则反映可能没有好的索引可供使用。

 

建议解决方法

      如果事务执行时间过长，一直锁住资源不放可能导致其他想要执行的事务被锁。例如：设置事务隔离级别为“可重复读取”，当查询语句（SELECT)执行时间过长时，则更新语句（UPDATE）则无法对数据进行更新，最终导致系统瘫痪。出现此类情况，可以试着使用以下方式进行解决

1.新增或设置适当的索引以增加查询速度

2.更新统计信息以避免执行计划使用旧的统计信息

3.重新设计数据表、存储过程等对象

4.检查是否过度使用触发器和游标。

如果无法通过以上方式提高 工作效率，则可能要考虑修改系统的工作流程

1. 分割工作，不要同时执行所有的需求

2. 切割工作时间，将工作排至系统不繁忙的时段执行

3. 切割工作属性，将工作交给另一个数据库去执行，把查询与更新分成两个数据库来执行。

 

 

（二）不正确的事务或事务隔离级别设置

      当死锁是由于不正确的事务或事务隔离级别设置所导致时，SQL SERVER 2005/2008动态管理视图sys.dm_exec_requests会提供相关信息，该SESSION_ID的status字段值为“running”，wait_type非“NULL”值，通过sys.dm_exec_session动态视图的transaction_isolation_level字段可以看出进程所设置的事务隔离级别。且从Microsoft SQL Server Management Studio管理工具中的“活动监视器--》进程信息”视图，该进程的“打开的事务”字段显示为非“0”值，表示为该进程仍握有事务资源。

       通过SQL PROFILER工具查找“TextData”，观察前端传递命令中是否含有不当的事务设置，例如，设置隐含式事务（SET IMPLICIT_TRANSACTION ON）、事务隔离等级或是设置锁定提示等。

 

建议解决方式：

       事务设置大多与实际业务逻辑有关，不容易界定是否有必要，如果你通过跟踪文件找到不正确的事务或事略等级隔离设置时，也需要与开发者讨论设置的必要性。尤其是当事务中包含大量数据的运算的情况，可能需要研究如何切割成较小的事务，但仍需要符合原来的数据完整性和业务逻辑要求。

 

 

（三）事务未正确处理

 

      开启了事务，但是没有回滚或没有提交，形成了未提交事务。它的特征与观察方式与上面所述相同。从下图中可以看出进程“54”仍持有事务，但此进程停滞不做事，也无等待任何资源，但仍持有事务，从SQL SERVER 2005的Microsoft SQL Server Management Studio管理工具中的“活动监视器--》进程信息”视图，进一步观察“上一批”字段，检查进程是否已经持有资源一段时间。

 

 

在SQL 2005(2008)中执行代码示例一，得到如下图。

 

select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)

,用户机器名称=SUBSTRING(hostname,1,12)

,是否被锁住=convert(char(3),blocked)

,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型

,last_batch 最后批处理时间,open_tran 未提交事务的数量

from master.sys.sysprocesses

--列出锁住别人（在别的进程中blocked字段中出现的值）但自己未被锁住(blocked=0)

Where spid in (select blocked from master.sys.sysprocesses) and blocked=0

 

 

 

 

建议解决方式

      利用SQL PROFILER 工具中的事务事件类别，录制SQL SERVER所触发的事务事件，也可以通过dbcc opentran （‘<数据库>’）命令观察针对某个数据库执行最久的事务事件，由哪个程序拥有，如果没有指定数据库名称或ID，则返回当前连接所在的数据库执行最久的事务事件，一般未提交事务可能是由于未做好错误处理所造成的。

 执行dbcc opentran命令的之后，如下图。其中UID是无意义的。

 

 

     例如，执行命令逾时，放弃批处理但未回滚事务。其中的错误处理，应该如下例一般。

If @@trancount>0

   Rollback tran

 

---或是设置：

 

Set XACT_ABORT on

 

 

(上述设置是指当SQL SERVER 在发生任何错误时，都要回滚事务)

 

（四）未检测到的分布式死锁

        某应用程序持有数据库资源，开启事务之后又与用户交互，而在与用户的交互过程中出现了错误，导致数据库资源迟迟不能释放。SQL SERVER 2005/2008 动态管理视图sys.dm_exec_requests提供相关信息，该SESSION_ID的status字段值为“sleeping”，wait_type为“NULL”值。如果是SQL 2005则可以通过Microsoft SQL Server Management Studio管理工具中的“活动监视器--》进程信息”视图，该进程的“开启事务字段”显示非“0”值。如下图。

 

 

      在SQL 2005(2008)中执行代码，即SQL SERVER 查询性能优化——分析事务与锁（二）中的“例一”，也就是下面的代码，得到如下图。

select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)

,用户机器名称=SUBSTRING(hostname,1,12)

,是否被锁住=convert(char(3),blocked)

,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型

,last_batch 最后批处理时间,open_tran 未提交事务的数量

from master.sys.sysprocesses

--列出锁住别人（在别的进程中blocked字段中出现的值）但自己未被锁住(blocked=0)

Where spid in (select blocked from master.sys.sysprocesses) and blocked=0

 

 

 

      由于应用程序持有事务，而且应用程序出错之后，没对事务的相应处理，也没有需要等待的资源，但持有事务，与前一种（三）情况类似，但通过SQL PROFILER工具进行跟踪，却无法发现任何错误事件。

建议解决方式

     应用程序所造成的分布式死锁，很难加以跟踪分析，需要程序开发人员自行记录该应用程序的行为，比较多用户情况下，在进行哪些工作之后，系统就迟滞无法正常执行下去。这需要程序开发人员保持良好的开发习惯：事务越晚开启越好，使用资源越少越好，一旦开启了事务迟早关闭，事务执行过程中不要与用户有任何交互，要输入的参数或内容应该在开启事务之前就应该输入完毕，对相关数据的各种校验也要在开启事务之前进行校验，事务应该只是在往数据库中插入更新数据时开启，插入更新完毕之后，就立即关闭。

 

 

（五）锁定数据粒度太低或太高

        用户设置不当的锁定粒度时，如果设置事务一律使用Row lock或table lock均可能产生问题，或是当系统资源使用过度，也很容易产生被锁定的情形。

建议解决方式

       可以通过SQL PROFILER 观察“TextData”字段所呈现的SQL语句，观察该应用程序是否设置了锁定提示，若想要暂时停止锁定提示造成的影响，可以通过以下语句

       Dbcc traceon(8755)

或以SQL SERVER 激活参数-T 8755 来停止锁定提示功能，若有改善，可以重新考虑从应用程序从新移除锁定提示的可能性。

 

（六）Compile Blocking

         此现象是由于编译存储过程导致被锁定，在master.sys.sysprocesses视图中或sp_lock存储过程中观察到的等待资源字段中的内容是“COMPILE”，或者使用SQL PROFILER 录制过程中出现大量的“SP:REComplie”事件。由于重新编译需要耗费CPU资源，所以，此种锁定是在一长串的被锁定连接中，单一锁定者锁定时间不长，但整个链接各点都有一点耗时，所以在链接尾端的被锁定者需要等待较长时间。同时会出现CPU的使用率比较高。

        当存储过程中使用了缓存数据表，而该缓存数据表还需要设置结构，如需要要设置主键或者利用缓存数据表开打开游标，则每次调用该存储过程进，都会要求重新编译。或这个存储过程是当应用程序执行时，常常会被调用的热门存储过程，就会出现Compile Blocking的状况出现。

       但存储过程第一次使用时，也会需要编译，所以不要一看到是在等待编译，就识以为是COMPILE Blocking现象。

建议解决方式

      使用sp_executesql执行语句，即使用sp_executesql执行SQL语句，SQL语句不会编译为存储过程执行计划的一部分，因此在执行该类语句时，SQL SERVER 会自由的使用高速缓存中的现有语句计划，或者在执行阶段建立新的执行计划，不管任何一种情况，调用存储过程的计划都不会受影响，也不必进行重新编译。

      EXECUTE语句也有相同的效果，但不建议你使用。因为使用EXECUTE没有使用SP_EXECUTESQL语句的效率高，因为前者不允许查询参数化。

 

三、基本原则：

     1. 事务不可以跨批处理，语句越短越好，事务期间不要与用户进行交互

     2. 小心处理逾时放弃，或者执行错误等情况。

    3. 正确建立索引。可以参考本人前面的相关文章。

    4. 数据表最好有聚集索引，而且聚集索引的键值不要太大，因为所有的非聚集索引存储的都是聚集索引的键值。不要使用经常需要进行更新的字段做为聚集索引的键值，因为聚集索引一旦进行了变更，则所有的非聚集索引也要跟着进行变更，导致大量的锁定。索引建少了，影响查询效率，建多了，浪费维护的资源与降低新增、修改、删除的效率，所以建好索引之后，要小心观察SQL SERVER 使用索引的情况，将多余的索引删除，对于数据密度大，或者查询条件鉴别率太低的字段不要建立索引。

     5. 尽量不要激活Implicit Transaction，以免它长时间的持有事务。

     6. 尽量降低事务隔离级别

     7. 进行压力测试以了解当大用户量时，交互将造成何种程度的锁定问题。

 

  四、 防止与处理死锁

        1.尽量避免或尽快处理锁定，当锁定与被锁定过多时，就可能造成死锁

        2.访问资源的顺序要相同。例如连接A先访问资源1，然后访问资源2，而连接B的访问顺序与之相反，则可能发生死锁。不要在开启事务的情况下，调用外部程序，容易造成分布式死锁。

        3.让不同的连接使用相同的锁定。或两条连接因为修改相同的资源而互相锁定，如果你的系统对于更新数据的正确性不做强制性要求，可以考虑使用sp_getbindtoken和sp_bindsession两个系统存储过程，让连接共享锁定，则两条连接同时更新数据，也就可能造成数据更新遗失。

例：

use Test

go

create proc sp_upd_OPINION

@OPINIONID varchar(20),

@bindToken varchar(255) output

as

exec sp_getbindtoken @bindToken output

update WBK_OPINION set OPINION_VALUE='true'

where OPINION_ID=@OPINIONID

go

create proc sp_upd_OPINION2

@OPINIONID varchar(20),

@bindSession varchar(255) output

as

exec sp_bindsession @bindSession 

update WBK_OPINION set OPINION_VALUE='False'

where OPINION_ID=@OPINIONID

 

go

----在第一个连接中执行

declare @bindToken varchar(255)

begin tran

exec sp_upd_opinion 'PreEntryIDUse',@bindToken output

select * from WBK_OPINION

select @@trancount  --事务数量为

select @bindToken

 

 

 

----在第二个连接中执行

---其中@binToken是由第一个连接执行完毕之后，而获取的

begin tran

exec sp_upd_opinion2 'PreEntryIDUse',@bindToken 

select * from WBK_OPINION

select @@trancount  --事务数量为

 

 

 

---在第三个连接中执行以下语句，由于不在同一个事务之内，所以会被锁定

update WBK_OPINION set OPINION_VALUE='true'

where OPINION_ID='PreEntryIDUse' 

 

 

rollback tran  ---回滚

 

 

         1. 你可以根据以上代码，自行编码相应的测试示例，通过Management studio分别使用三条连接来执行更新示例代码，你会发现享有相同TOKEN的两条连接会一同更新，而其获取的@@TRANCOUNT系统变量也是一样的。而不在同一事务中的其他连接则会被锁住。@@TRANCOUNT也与前述的事务无关。

        2.提交不同的数据访问路径。如果两条不同连接的SQL语句，因为抢相同索引而导致死锁，可以考虑为不同的访问语句建立不同的索引，通过索引提示强制让两条连接访问各自的索引。或者是两条不同的连接访问相同的数据表，如果引用不同的索引，但各自的访问顺序正彼此交错，形成死锁，则可强制两条连接使用相同的索引，以维护访问先后秩序。

      不管如何，采用此类解决方式时，都要考虑额外的性能损耗，因为你通过索引提示强制了索引访问，让查询优化程序不能凭借数据的特性使用最佳的索引。

 

五、发生死锁后的处理

       通过设置SET DEADLOCK_PRIORITY LOW，让不重要的事务自动放弃，并在这些连接执行的业务逻辑中，加上针对死锁的错误处理。

      事实上，在非常复杂的高并发量的系统中，要完全预防死锁，或者要知道什么样的用户在特殊的访问次序中会发生死锁，是非常困难的。所以应用程序应该对死锁错误“1205”要有相应的处理，以完成原有的业务逻辑的处理或是善后清除处理。