当前位置： 文江博客话题详情

为什么我的 SQL Server 出现死锁？包括 T1222 死锁跟踪

发布于 2024-12-11 17:24:16 字数 8721 浏览 0 评论 0原文

我遇到了僵局，但我不明白为什么。消息末尾是 T1222 跟踪。这就是正在发生的情况：

UPDATE ADMISSIONS SET …
LAUNCHS A TRIGGER - > INSERT INTO PCM_RECENTCHANGES …
LAUNCHS A TRIGGER -> CALL FUNCTION GETLASTLOCATION
THE FUNCTION EXECUTES -> SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID

有时，当我们有几台计算机执行相同的操作时，我们会收到下面报告的死锁。我不明白的是，如果这只是一个读取查询，并且据我所知，没有人在该表上插入数据，那么我们如何在 SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID 处出现死锁。

僵局报告：

 deadlock-list
  deadlock victim=processd99ab0
   process-list
    process id=processd99ab0 taskpriority=0 logused=11760 waitresource=RID: 8:7:765:0 waittime=2249 ownerId=6288074 transactionname=user_transaction lasttranstarted=2011-10-25T15:38:43.667 XDES=0x262dec08 lockMode=S schedulerid=3 kpid=2692 status=suspended spid=65 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-10-25T15:38:43.710 lastbatchcompleted=2011-10-25T15:38:43.710 lastattention=2011-10-25T15:37:34.667 clientapp=Careman hostname=BCNW-ISOLER hostpid=4400 loginname=PicisSQL isolationlevel=read committed (2) xactid=6288074 currentdb=8 lockTimeout=4294967295 clientoption1=537002016 clientoption2=128056
     executionStack
      frame procname=NTPA_TEST_CAR.dbo.GETLASTLOCATION line=83 stmtstart=4218 stmtend=4478 sqlhandle=0x03000800ac4d05647a42db00819f00000000000000000000
 SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID
                              FROM ENVIRONMENTS 
                              WHERE ENCOUNTERDBOID = @ENCOUNTER
                              ORDER BY STARTED DESC     
      frame procname=NTPA_TEST_CAR.dbo.GETFACILITYBYTABLEDBOID line=31 stmtstart=1492 stmtend=1598 sqlhandle=0x03000800318fdd145641db00819f00000000000000000000
 SELECT @ret = dbo.GETLASTLOCATION('ADMISSION', @id)     
      frame procname=NTPA_TEST_CAR.dbo.TR_PCM_RECENTCHANGES_INSTEAD line=60 stmtstart=7396 stmtend=8576 sqlhandle=0x0300080062bc4b452d42db00819f00000000000000000000
 INSERT PCM_RECENTCHANGES (STARTED, ISDELETED, TABLENAME, PCM_KEY, PCM_MESSAGE, PATIENTDBOID, ENCOUNTERDBOID,FILTERID, FACILITYDBOID) 
                              SELECT inserted.STARTED, inserted.ISDELETED, inserted.TABLENAME, inserted.PCM_KEY, inserted.PCM_MESSAGE, inserted.PATIENTDBOID, inserted.ENCOUNTERDBOID,'ADT', CASE WHEN inserted.FACILITYDBOID IS NOT NULL THEN inserted.FACILITYDBOID ELSE dbo.GETFACILITYBYTABLEDBOID(TABLENAME, CASE WHEN inserted.TABLENAME = 'PATIENTS' THEN inserted.PATIENTDBOID ELSE inserted.PCM_KEY END) END 
                              FROM inserted 
                              -- EXIT BECAUSE WE DO NOT HAVE ANYTHING ELSE TO DO.     
      frame procname=NTPA_TEST_CAR.dbo.TR_ADMISSIONS_IU line=169 stmtstart=21690 stmtend=27250 sqlhandle=0x03000800b9db99535642db00819f00000000000000000000
 INSERT INTO PCM_RECENTCHANGES (STARTED, ISDELETED, TABLENAME, PCM_KEY, PCM_MESSAGE) 
                         SELECT      GetDate(),  
                               'F',  
                               'ADMISSIONS',
                               CONVERT(CHAR(21), i.ADMISSIONDBOID),
                               ISNULL( CONVERT(CHAR(48), A.ADMID1), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(48), i.ADMID2), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(48), i.ADMID3), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(19), i.HOSPITALSTARTED, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(19), i.ENDED, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(19), i.PREADMISSION, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(13), i.WEIGHT), REPLICATE(' ', 13) )
                                     +     
      frame procname=adhoc line=1 stmtstart=94 sqlhandle=0x0200000070ece538900ddb307676a81ad422678baf24495d
 UPDATE [ADMISSIONS] set [ENDED] = @1,[DISCHDBOID] = @2  WHERE [ADMISSIONDBOID]=@3     
      frame procname=adhoc line=1 sqlhandle=0x0200000026f85e24d2ee570e5908604daf7249bd6d4e9032
 update ADMISSIONS set ENDED={ts '2011-10-25 15:38:42'},DISCHDBOID=009000000000000000000 where ADMISSIONDBOID=004511105043973019010     
     inputbuf
 update ADMISSIONS set ENDED={ts '2011-10-25 15:38:42'},DISCHDBOID=009000000000000000000 where ADMISSIONDBOID=004511105043973019010    
    process id=processfc2c70 taskpriority=0 logused=13584 waitresource=RID: 8:7:758:8 waittime=2233 ownerId=6288077 transactionname=user_transaction lasttranstarted=2011-10-25T15:38:43.670 XDES=0xa228550 lockMode=S schedulerid=4 kpid=4340 status=suspended spid=86 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-10-25T15:38:43.723 lastbatchcompleted=2011-10-25T15:38:43.720 lastattention=2011-10-25T15:37:36.140 clientapp=Careman hostname=BCNW-DANIM hostpid=13812 loginname=PicisSQL isolationlevel=read committed (2) xactid=6288077 currentdb=8 lockTimeout=4294967295 clientoption1=537002016 clientoption2=128056
    executionStack
     frame procname=NTPA_TEST_CAR.dbo.GETLASTLOCATION line=83 stmtstart=4218 stmtend=4478 sqlhandle=0x03000800ac4d05647a42db00819f00000000000000000000
SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID
                             FROM ENVIRONMENTS 
                             WHERE ENCOUNTERDBOID = @ENCOUNTER
                             ORDER BY STARTED DESC     
     frame procname=NTPA_TEST_CAR.dbo.GETFACILITYBYTABLEDBOID line=31 stmtstart=1492 stmtend=1598 sqlhandle=0x03000800318fdd145641db00819f00000000000000000000
SELECT @ret = dbo.GETLASTLOCATION('ADMISSION', @id)     
     frame procname=NTPA_TEST_CAR.dbo.TR_PCM_RECENTCHANGES_INSTEAD line=60 stmtstart=7396 stmtend=8576 sqlhandle=0x0300080062bc4b452d42db00819f00000000000000000000
INSERT PCM_RECENTCHANGES (STARTED, ISDELETED, TABLENAME, PCM_KEY, PCM_MESSAGE, PATIENTDBOID, ENCOUNTERDBOID,FILTERID, FACILITYDBOID) 
                             SELECT inserted.STARTED, inserted.ISDELETED, inserted.TABLENAME, inserted.PCM_KEY, inserted.PCM_MESSAGE, inserted.PATIENTDBOID, inserted.ENCOUNTERDBOID,'ADT', CASE WHEN inserted.FACILITYDBOID IS NOT NULL THEN inserted.FACILITYDBOID ELSE dbo.GETFACILITYBYTABLEDBOID(TABLENAME, CASE WHEN inserted.TABLENAME = 'PATIENTS' THEN inserted.PATIENTDBOID ELSE inserted.PCM_KEY END) END 
                              FROM inserted 
                              -- EXIT BECAUSE WE DO NOT HAVE ANYTHING ELSE TO DO.     
      frame procname=NTPA_TEST_CAR.dbo.TR_ADMISSIONS_IU line=169 stmtstart=21690 stmtend=27250 sqlhandle=0x03000800b9db99535642db00819f00000000000000000000
 INSERT INTO PCM_RECENTCHANGES (STARTED, ISDELETED, TABLENAME, PCM_KEY, PCM_MESSAGE) 
                         SELECT      GetDate(),  
                               'F',  
                               'ADMISSIONS',
                               CONVERT(CHAR(21), i.ADMISSIONDBOID),
                               ISNULL( CONVERT(CHAR(48), A.ADMID1), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(48), i.ADMID2), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(48), i.ADMID3), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(19), i.HOSPITALSTARTED, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(19), i.ENDED, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(19), i.PREADMISSION, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(13), i.WEIGHT), REPLICATE(' ', 13) )
                                     +     
      frame procname=adhoc line=1 stmtstart=94 sqlhandle=0x0200000070ece538900ddb307676a81ad422678baf24495d
 UPDATE [ADMISSIONS] set [ENDED] = @1,[DISCHDBOID] = @2  WHERE [ADMISSIONDBOID]=@3     
      frame procname=adhoc line=1 sqlhandle=0x02000000ea3b4a253048c290f000fae3e15fc38eacf905c7
 update ADMISSIONS set ENDED={ts '2011-10-25 15:38:43'},DISCHDBOID=009000000000000000000 where ADMISSIONDBOID=004511105044149020010     
     inputbuf
 update ADMISSIONS set ENDED={ts '2011-10-25 15:38:43'},DISCHDBOID=009000000000000000000 where ADMISSIONDBOID=004511105044149020010    
   resource-list
    ridlock fileid=7 pageid=765 dbid=8 objectname=NTPA_TEST_CAR.dbo.ENVIRONMENTS id=lock1ae5d240 mode=X associatedObjectId=64466095439872
     owner-list
      owner id=processfc2c70 mode=X
     waiter-list
      waiter id=processd99ab0 mode=S requestType=wait
    ridlock fileid=7 pageid=758 dbid=8 objectname=NTPA_TEST_CAR.dbo.ENVIRONMENTS id=lock1afaf540 mode=X associatedObjectId=64466095439872
     owner-list
      owner id=processd99ab0 mode=X
     waiter-list
      waiter id=processfc2c70 mode=S requestType=wait
原文

I'm having a deadlock but I cannot understand why. At the end of the message is the T1222 trace. This is what is happening:

UPDATE ADMISSIONS SET …
LAUNCHS A TRIGGER - > INSERT INTO PCM_RECENTCHANGES …
LAUNCHS A TRIGGER -> CALL FUNCTION GETLASTLOCATION
THE FUNCTION EXECUTES -> SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID

Sometimes when we have several computers doing the same thing we get the deadlock reported below. What I don't get is how we have a deadlock at the SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID if this is just a read query and, as far as I know, no one is inserting data on that table.

The deadlock report:

 deadlock-list
  deadlock victim=processd99ab0
   process-list
    process id=processd99ab0 taskpriority=0 logused=11760 waitresource=RID: 8:7:765:0 waittime=2249 ownerId=6288074 transactionname=user_transaction lasttranstarted=2011-10-25T15:38:43.667 XDES=0x262dec08 lockMode=S schedulerid=3 kpid=2692 status=suspended spid=65 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-10-25T15:38:43.710 lastbatchcompleted=2011-10-25T15:38:43.710 lastattention=2011-10-25T15:37:34.667 clientapp=Careman hostname=BCNW-ISOLER hostpid=4400 loginname=PicisSQL isolationlevel=read committed (2) xactid=6288074 currentdb=8 lockTimeout=4294967295 clientoption1=537002016 clientoption2=128056
     executionStack
      frame procname=NTPA_TEST_CAR.dbo.GETLASTLOCATION line=83 stmtstart=4218 stmtend=4478 sqlhandle=0x03000800ac4d05647a42db00819f00000000000000000000
 SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID
                              FROM ENVIRONMENTS 
                              WHERE ENCOUNTERDBOID = @ENCOUNTER
                              ORDER BY STARTED DESC     
      frame procname=NTPA_TEST_CAR.dbo.GETFACILITYBYTABLEDBOID line=31 stmtstart=1492 stmtend=1598 sqlhandle=0x03000800318fdd145641db00819f00000000000000000000
 SELECT @ret = dbo.GETLASTLOCATION('ADMISSION', @id)     
      frame procname=NTPA_TEST_CAR.dbo.TR_PCM_RECENTCHANGES_INSTEAD line=60 stmtstart=7396 stmtend=8576 sqlhandle=0x0300080062bc4b452d42db00819f00000000000000000000
 INSERT PCM_RECENTCHANGES (STARTED, ISDELETED, TABLENAME, PCM_KEY, PCM_MESSAGE, PATIENTDBOID, ENCOUNTERDBOID,FILTERID, FACILITYDBOID) 
                              SELECT inserted.STARTED, inserted.ISDELETED, inserted.TABLENAME, inserted.PCM_KEY, inserted.PCM_MESSAGE, inserted.PATIENTDBOID, inserted.ENCOUNTERDBOID,'ADT', CASE WHEN inserted.FACILITYDBOID IS NOT NULL THEN inserted.FACILITYDBOID ELSE dbo.GETFACILITYBYTABLEDBOID(TABLENAME, CASE WHEN inserted.TABLENAME = 'PATIENTS' THEN inserted.PATIENTDBOID ELSE inserted.PCM_KEY END) END 
                              FROM inserted 
                              -- EXIT BECAUSE WE DO NOT HAVE ANYTHING ELSE TO DO.     
      frame procname=NTPA_TEST_CAR.dbo.TR_ADMISSIONS_IU line=169 stmtstart=21690 stmtend=27250 sqlhandle=0x03000800b9db99535642db00819f00000000000000000000
 INSERT INTO PCM_RECENTCHANGES (STARTED, ISDELETED, TABLENAME, PCM_KEY, PCM_MESSAGE) 
                         SELECT      GetDate(),  
                               'F',  
                               'ADMISSIONS',
                               CONVERT(CHAR(21), i.ADMISSIONDBOID),
                               ISNULL( CONVERT(CHAR(48), A.ADMID1), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(48), i.ADMID2), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(48), i.ADMID3), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(19), i.HOSPITALSTARTED, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(19), i.ENDED, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(19), i.PREADMISSION, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(13), i.WEIGHT), REPLICATE(' ', 13) )
                                     +     
      frame procname=adhoc line=1 stmtstart=94 sqlhandle=0x0200000070ece538900ddb307676a81ad422678baf24495d
 UPDATE [ADMISSIONS] set [ENDED] = @1,[DISCHDBOID] = @2  WHERE [ADMISSIONDBOID]=@3     
      frame procname=adhoc line=1 sqlhandle=0x0200000026f85e24d2ee570e5908604daf7249bd6d4e9032
 update ADMISSIONS set ENDED={ts '2011-10-25 15:38:42'},DISCHDBOID=009000000000000000000 where ADMISSIONDBOID=004511105043973019010     
     inputbuf
 update ADMISSIONS set ENDED={ts '2011-10-25 15:38:42'},DISCHDBOID=009000000000000000000 where ADMISSIONDBOID=004511105043973019010    
    process id=processfc2c70 taskpriority=0 logused=13584 waitresource=RID: 8:7:758:8 waittime=2233 ownerId=6288077 transactionname=user_transaction lasttranstarted=2011-10-25T15:38:43.670 XDES=0xa228550 lockMode=S schedulerid=4 kpid=4340 status=suspended spid=86 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-10-25T15:38:43.723 lastbatchcompleted=2011-10-25T15:38:43.720 lastattention=2011-10-25T15:37:36.140 clientapp=Careman hostname=BCNW-DANIM hostpid=13812 loginname=PicisSQL isolationlevel=read committed (2) xactid=6288077 currentdb=8 lockTimeout=4294967295 clientoption1=537002016 clientoption2=128056
    executionStack
     frame procname=NTPA_TEST_CAR.dbo.GETLASTLOCATION line=83 stmtstart=4218 stmtend=4478 sqlhandle=0x03000800ac4d05647a42db00819f00000000000000000000
SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID
                             FROM ENVIRONMENTS 
                             WHERE ENCOUNTERDBOID = @ENCOUNTER
                             ORDER BY STARTED DESC     
     frame procname=NTPA_TEST_CAR.dbo.GETFACILITYBYTABLEDBOID line=31 stmtstart=1492 stmtend=1598 sqlhandle=0x03000800318fdd145641db00819f00000000000000000000
SELECT @ret = dbo.GETLASTLOCATION('ADMISSION', @id)     
     frame procname=NTPA_TEST_CAR.dbo.TR_PCM_RECENTCHANGES_INSTEAD line=60 stmtstart=7396 stmtend=8576 sqlhandle=0x0300080062bc4b452d42db00819f00000000000000000000
INSERT PCM_RECENTCHANGES (STARTED, ISDELETED, TABLENAME, PCM_KEY, PCM_MESSAGE, PATIENTDBOID, ENCOUNTERDBOID,FILTERID, FACILITYDBOID) 
                             SELECT inserted.STARTED, inserted.ISDELETED, inserted.TABLENAME, inserted.PCM_KEY, inserted.PCM_MESSAGE, inserted.PATIENTDBOID, inserted.ENCOUNTERDBOID,'ADT', CASE WHEN inserted.FACILITYDBOID IS NOT NULL THEN inserted.FACILITYDBOID ELSE dbo.GETFACILITYBYTABLEDBOID(TABLENAME, CASE WHEN inserted.TABLENAME = 'PATIENTS' THEN inserted.PATIENTDBOID ELSE inserted.PCM_KEY END) END 
                              FROM inserted 
                              -- EXIT BECAUSE WE DO NOT HAVE ANYTHING ELSE TO DO.     
      frame procname=NTPA_TEST_CAR.dbo.TR_ADMISSIONS_IU line=169 stmtstart=21690 stmtend=27250 sqlhandle=0x03000800b9db99535642db00819f00000000000000000000
 INSERT INTO PCM_RECENTCHANGES (STARTED, ISDELETED, TABLENAME, PCM_KEY, PCM_MESSAGE) 
                         SELECT      GetDate(),  
                               'F',  
                               'ADMISSIONS',
                               CONVERT(CHAR(21), i.ADMISSIONDBOID),
                               ISNULL( CONVERT(CHAR(48), A.ADMID1), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(48), i.ADMID2), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(48), i.ADMID3), REPLICATE(' ', 48) )
                                     + ISNULL( CONVERT(CHAR(19), i.HOSPITALSTARTED, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(19), i.ENDED, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(19), i.PREADMISSION, 120), REPLICATE(' ', 19) )
                                     + ISNULL( CONVERT(CHAR(13), i.WEIGHT), REPLICATE(' ', 13) )
                                     +     
      frame procname=adhoc line=1 stmtstart=94 sqlhandle=0x0200000070ece538900ddb307676a81ad422678baf24495d
 UPDATE [ADMISSIONS] set [ENDED] = @1,[DISCHDBOID] = @2  WHERE [ADMISSIONDBOID]=@3     
      frame procname=adhoc line=1 sqlhandle=0x02000000ea3b4a253048c290f000fae3e15fc38eacf905c7
 update ADMISSIONS set ENDED={ts '2011-10-25 15:38:43'},DISCHDBOID=009000000000000000000 where ADMISSIONDBOID=004511105044149020010     
     inputbuf
 update ADMISSIONS set ENDED={ts '2011-10-25 15:38:43'},DISCHDBOID=009000000000000000000 where ADMISSIONDBOID=004511105044149020010    
   resource-list
    ridlock fileid=7 pageid=765 dbid=8 objectname=NTPA_TEST_CAR.dbo.ENVIRONMENTS id=lock1ae5d240 mode=X associatedObjectId=64466095439872
     owner-list
      owner id=processfc2c70 mode=X
     waiter-list
      waiter id=processd99ab0 mode=S requestType=wait
    ridlock fileid=7 pageid=758 dbid=8 objectname=NTPA_TEST_CAR.dbo.ENVIRONMENTS id=lock1afaf540 mode=X associatedObjectId=64466095439872
     owner-list
      owner id=processd99ab0 mode=X
     waiter-list
      waiter id=processfc2c70 mode=S requestType=wait

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

陌若浮生 2024-12-18 17:24:16

由于某种原因，两个事务都对 ENVIRONMENTS 具有 X 锁。

可能与 ADMISSIONS 存在更新级联外键关系，或者 X 锁可能是由同一事务中运行的较早语句获取的，并且与死锁报告中显示的代码完全无关（这显示了死锁发生时执行的语句，而不是首先获取资源的语句）。

For some reason both transactions have an X lock on ENVIRONMENTS.

Possibly there is an on update cascade foreign key relationship with ADMISSIONS or maybe the X lock was acquired by an earlier statement running in the same transaction and is nothing to do with the code shown in the deadlock report at all (this shows the statements executing when the deadlock occurred not the statements that acquired the resources in the first place).

回复 原文
樱＆纷飞 2024-12-18 17:24:16

如果 ENVIRONMENTS 是缓慢变化的数据，您可以考虑

with (NOLOCK)

在 ENVIRONMENTS 表上使用。这将降低发生死锁的风险。

SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID
                          FROM ENVIRONMENTS with (nolock)
                          WHERE ENCOUNTERDBOID = @ENCOUNTER
                          ORDER BY STARTED DESC

其次，您还可以删除 ORDER BY，因为它没有被使用。

接下来，您还可以考虑重写此部分，如下所示：

if exists(select top 1 1 FROM ENVIRONMENTS WHERE ENCOUNTERDBOID = @ENCOUNTER) 
set @Environment = @Encounter

当表 ENVIRONMENTS 中至少有一行符合条件时，您的 SQL 将始终将 @ENVIRONMENT 设置为 @ENCOUNTER。无需排序，因为设置的值始终等于 @ENCOUNTER 并且不返回 STARTED。

我列出了这些解决方案，因为它们都可能导致出现死锁情况。

If ENVIRONMENTS is slow-changing-data, you may consider using

with (NOLOCK)

on the ENVIRONMENTS table. This will reduce the risk of this dead-lock occurring.

SELECT TOP 1 @ENVIRONMENT = ENVIRONMENTDBOID
                          FROM ENVIRONMENTS with (nolock)
                          WHERE ENCOUNTERDBOID = @ENCOUNTER
                          ORDER BY STARTED DESC

Secondly, you can also remove the ORDER BY as it is not used.

Next you can also consider rewriting this section like:

if exists(select top 1 1 FROM ENVIRONMENTS WHERE ENCOUNTERDBOID = @ENCOUNTER) 
set @Environment = @Encounter

Your SQL will always set the @ENVIRONMENT to @ENCOUNTER when there is at least one row that qualifies in the table ENVIRONMENTS. There is no need to sort as the value set will always equal @ENCOUNTER and the STARTED is not returned.

I have listed these solutions because they all can contribute to your dead-lock situation.

回复 原文
~没有更多了~

关于作者

太阳公公是暖光

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

Gabu-gabumon

文章 0 评论 0

qq_CgiN62

文章 0 评论 0

荔枝明

文章 0 评论 0

赏烟花じ飞满天

文章 0 评论 0

独守阴晴ぅ圆缺

文章 0 评论 0

¤→小豸慧

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文