Why I have this SQL Server deadlock? Included the T1222 deadlock traces

Question

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

Answer 1

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.

Answer 2

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).