SQL死锁分析与处理

类型：电子教程大小：8.5M语言：中文评分：8.3标签:立即下载-死锁/* * * * * * * * * * * * * * * * *。* * * * * * * * * * * * * * * * * * * * * * * *死锁是指两个或多个事务相互阻塞，并等待对方释放其锁。SQL Server将通过回滚一个事务并返回一个错误来解决阻塞问题，这样其他事务就可以完成它们的工作。

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

将nocount设置为on；如果object_id('T1 ')不是nulldrop表T1 go创建表T1(ID int主键，Col1 int，Col2 nvarchar(20))插入T1选择1，101，' A '插入T1选择2，102，' B '插入T1选择3，103，' C'go

如果object_id('T2 ')不是nulldrop表t2go创建表T2(ID int主键，Col1 int，Col2 nvarchar(20))插入T2选择1，201，“X”插入T2选择2，202，“Y”插入T2选择3，203，“Z”

Go生成表格数据：/* t 1: idcol1col 2-1 101 a2 101 B3 101 c

t 2: id Col1 Col2-1-201 X2 201 Y3 201 Z */

防止死锁：1。尽量减少阻塞。阻塞越少，死锁的几率就越小。2.在事务中按顺序访问表(上例：死锁2)。3.检查错误处理程序中的错误1205，并在错误发生时重新提交事务。4.在错误处理程序中添加一个进程，将错误详细信息写入日志。5.合理使用索引(上例：死锁1、死锁3)。当死锁发生时，事务会自动提交，并且可以通过日志监视死锁

死锁1(索引):-连接窗口1-1步骤：开始事务更新t1设置列2=列2 ' a ’,其中列1=101

-3步：从T2选择*其中col1=201提交交易

-连接窗口2

-2步骤：开始交易更新T2集列2=列2 ' b ’,其中列1=203

-4步：从t1中选择*其中col1=103提交交易

-连接窗口1:收到死锁错误，连接窗口2收到结果：

/*消息1205，级别13，状态51，第3行事务(进程ID 53)被锁定资源上的另一个进程锁定，并被选为死锁的受害者。请重新执行交易。*/

-连接窗口2:获取结果

/*----3 103C */

处理方法：-在t1和t2表的列1条件列中创建索引IX _ t1 _ col1ont1(列1)创建索引IX _ t2 _ col1ont2(列1)

-连接窗口1-1步骤：开始交易更新t1设置col2=col2 ' a ’,其中col1=101

-3步：从T2选择*(索引=IX _ T2 _ col1)，其中col 1=201-因为几乎没有表数据，所以只能指定索引提示来确保SQL Server使用索引提交传输

-连接窗口2

-2步骤：开始交易更新T2集列2=列2 ' b ’,其中列1=203

-4-步骤：用(index=ix _ t1 _ col1)从t1中选择*其中col 1=103-由于缺少表数据，只能指定索引提示以确保SQL Server使用索引提交事务

-连接窗口1:/* idcol1col2-1 201x

(1行受影响)

*/-连接窗口2/* idcol1col2-3 103c

(1行受影响)*/

死锁2(访问表顺序):

-连接窗口1:-1步骤：开始交易更新t1设置col1=col1 1，其中id=1

-3步骤：从T2选择col 1，其中id=1提交交易

-连接窗口2:-2步骤：开始交易更新T2集列1=列1 1其中id=1

-4步从t1中选择第1列，其中id=1提交交易

-连接窗口1:

/*第1 - 201栏

(1行受影响)*/

-连接窗口2:

/* col 1-消息1205，级别13，状态51，第1行事务(进程ID 54)被锁定资源上的另一个进程锁定，并被选为死锁的受害者。请重新执行交易。*/

处理方法：

-更改访问表格的顺序

-连接窗口1:-1步骤：开始交易更新t1设置col1=col1 1，其中id=1

-3步骤：从T2选择col 1，其中id=1提交交易

-连接窗口2:-2步骤：从t1开始选择列1，其中id=1-将等待连接窗口1提交-4步骤更新T2集列1=列1 1，其中id=1提交事务

死锁3(单表):

-连接窗口1:

而1=1更新T1设置col1=203-col1，其中ID=2

-连接窗口2:声明@ I nvarchar (20)而1=1 set @ I=(从t1中选择col2，带有(index=IX _ t1 _ col1)，其中col 1=102)；-由于缺少表数据，只能指定索引提示，以确保SQL Server使用索引

-连接窗口1/*消息1205，级别13，状态51，第4行事务(进程ID 53)被锁定资源上的另一个进程锁定，并被选为死锁的受害者。请重新执行交易。*/

处理方法：1:删除col1上的非聚集索引，这会影响SELECT的速度。不建议在t12上删除索引IX_t1_col1，创建覆盖索引a，在t1B上删除索引IX_t1_col1，并在t1 (col1，col2)上创建索引IX _ t1 _ col1 _ col2

通过SQL Server事件探查器：检查死锁信息

启动SQL Server配置文件——连接实例——事件选择范围——显示所有事件选择： TSQL —— SQL : stmtstartinglock 3354死锁图(这是SQL2005中的新事件。生成包含死锁信息的xml值)——Lock:DeadlockChain死锁链中的进程生成此事件，该事件可以标识死锁进程的ID并跟踪操作——Lock 3360 Deadlock。