I have a stored procedure which ran parallely by two processes. Code for sp : DECLARE @inputXML XML DECLARE @tempTable TABLE (afid INT, ccid INT) INSERT INTO @tempTable SELECT ref.value('@afid', 'INT'),ref.value('@ccid', 'INT') FROM @inputXML.nodes('/Ilist/aidA/afid') R ( ref ) DELETE FROM StylinInfoTemp FROM @tempTable AS temp WHERE temp.afid= StylinInfoTemp.afid AND temp.ccid= StylinInfoTemp.ccid this is the schema for StylinInfoTemp Columns : cisid int afid int ccid int entity int dtime int guid uniqueidentifier addFlags int Index is present on cisid, afid, ccid here is the schema for tempTable (afid INT, ccid INT) This tempTable is populated on the input received by the stored proc. tempTable in itself can never have duplicate entries. However StylinInfoTemp table may have duplicate entries on afId and ccid. But is unique on cisid, afid, ccid eg Check duplicate values on afid below However the stored procedure tempTable will always have unique afid. When this stored proc executes I am getting a deadlock on the same page, I am having trouble understanding how exactly is this leading to a deadlock and why do we have same page Id below Can someone help me understanding why deadlock, why same page id, how can two process hold and request for the same page id at the same time(image above) and how can I resolve this DeadLock XML : https://codebeautify.org/xmlviewer/y228f34a3 Query Plan: DELETE FROM StylinInfoTemp FROM @tempTable AS temp WHERE temp.afid = StylinInfoTemp.afid AND temp.ccid = StylinInfoTemp.ccid |--Clustered Index Delete(OBJECT:([Magneto].[dbo].[StylinInfoTemp].[StylinInfoTemp_cis_afid_ccid_PK])) |--Nested Loops(Left Semi Join, WHERE:(@tempTable.[afid] as [temp].[afid]=[Magneto].[dbo].[StylinInfoTemp].[afId] AND @temp.[ccid] as [temp].[ccid]=[Magneto].[dbo].[StylinInfoTemp].[ccid])) |--Clustered Index Scan(OBJECT:([Magneto].[dbo].[StylinInfoTemp].[StylinInfoTemp_cis_afid_ccidd_PK])) |--Table Scan(OBJECT:(@tempTable AS [temp]))

We are testing our SQL Server database for concurrency issues and it appears that a single, simple update statement being run concurrently in transactions is resulting in deadlocks. Here is the statement:

(@Version nvarchar(7),@TestAttemptId uniqueidentifier,@TestFormName nvarchar(1),
@ActorId uniqueidentifier,@TimeStamp datetime,@Id uniqueidentifier,@UpdatedOn
 datetime,@UpdatedBy uniqueidentifier)
UPDATE [dbo].[TestAttempts] 
Set [TestFormName]=@TestFormName,[UpdatedOn]=@UpdatedOn,
[UpdatedBy]=@UpdatedBy 
Where  [Id]=@Id

Here's the diagram provided by the xdl we've retrieved for one of the deadlocks: Since the primary key is one of the problem locks, here's the primary key's definition:

ALTER TABLE [dbo].[TestAttempts] ADD  CONSTRAINT [PK_TestAttempts] PRIMARY KEY CLUSTERED 
(
	[Id] ASC,
	[TestAdministrationId] ASC,
	[TestTakerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

This concurrency test essentially involves the same code being run multiple times concurrently but updating different records. I'm fairly certain that there aren't any queries being run in the transaction prior to this update that would lock the key or the table. It's mainly a sequence of updates being performed, and this particular update only runs once in each process. So I'm confused as to how the same update query could deadlock here based only its primary key and the table itself. I'd expect each individual process to access the objects in the same sequence. --- EDIT --- As requested, here's the full DDL for the table and its indexes:

Create Table [dbo].[TestAttempts]
(
    [Id] UniqueIdentifier Not Null,
    [TestTakerId] Int Not Null,
    [TestAdministrationId] UniqueIdentifier Not Null,
    [TestFormName] NVARCHAR(50) NULL, 
    [DocumentId] UniqueIdentifier Null,
	[TimeRemaining] TIME NULL,
    [Score] Decimal(18, 5) Null,
    [Pass] Bit Null,
    [StartedOn] DateTime Null,
    [EndedOn] DateTime Null,
    [LocationName] NVARCHAR(255) NULL, 
    [KioskUsed] BIT NOT NULL DEFAULT 0,
	[STAUsed] BIT NOT NULL DEFAULT 0, 
    [CreatedOn] DateTime Not Null,
    [CreatedBy] UniqueIdentifier Not Null,
    [UpdatedOn] DateTime Null,
    [UpdatedBy] UniqueIdentifier Null,
    [Notes] NTEXT NULL, 
    [WhiteboardHistory] NTEXT NULL, 
    Constraint [FK_TestAttempts_Documents] Foreign Key ([DocumentId]) References [dbo].[Documents]([Id]),
    Constraint [PK_TestAttempts] Primary Key Clustered ([Id], [TestAdministrationId], [TestTakerId])
) On [PRIMARY]
Go

Create Index [IX_TestAttempts_TestAdministrationId_TestTakerId] On [dbo].[TestAttempts]([TestAdministrationId], [TestTakerId])
Go

Create Index [IX_TestAttempts_DocumentId] On [dbo].[TestAttempts]([DocumentId])

GO
CREATE NONCLUSTERED INDEX [_dta_index_TestAttempts_17_591456956__K2_K3_K1_K10] ON [dbo].[TestAttempts]
(
	[TestTakerId] ASC,
	[TestAdministrationId] ASC,
	[Id] ASC,
	[EndedOn] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [_dta_index_TestAttempts_17_591456956__K1_K3_K4] ON [dbo].[TestAttempts]
(
	[Id] ASC,
	[TestAdministrationId] ASC,
	[TestFormName] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [_dta_index_TestAttempts_17_591456956__K10_K3_K2_K1_K9] ON [dbo].[TestAttempts]
(
	[EndedOn] ASC,
	[TestAdministrationId] ASC,
	[TestTakerId] ASC,
	[Id] ASC,
	[StartedOn] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

And the full XDL deadlock graph:

unknown    
        
unknown    
      
      
(@Version nvarchar(7),@TestAttemptId uniqueidentifier,@TestFormName nvarchar(1),@ActorId uniqueidentifier,@TimeStamp datetime,@Id uniqueidentifier,@UpdatedOn datetime,@UpdatedBy uniqueidentifier)UPDATE [dbo].[TestAttempts] Set [TestFormName]=@TestFormName,[UpdatedOn]=@UpdatedOn,[UpdatedBy]=@UpdatedBy Where  [Id]=@Id   
    
    
      
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
      
      
        
unknown    
        
unknown    
      
      
(@Version nvarchar(7),@TestAttemptId uniqueidentifier,@TestFormName nvarchar(1),@ActorId uniqueidentifier,@TimeStamp datetime,@Id uniqueidentifier,@UpdatedOn datetime,@UpdatedBy uniqueidentifier)UPDATE [dbo].[TestAttempts] Set [TestFormName]=@TestFormName,[UpdatedOn]=@UpdatedOn,[UpdatedBy]=@UpdatedBy Where  [Id]=@Id

I've also pasted a query plan here (Note it's not the exact plan from the executions that caused the deadlocks; they are no longer available. It's a plan that results on the same database from running the same update statement): https://www.brentozar.com/pastetheplan/?id=rJLNaP7R2

We are experiencing deadlock 2-3 times a day in SQL Server. Upon analysis, we have found that the deadlock is caused by a combination of keylock and pagelock occurring simultaneously. We are actually using EF to fetch data and the query involves more than 6 foreign key relations. But on some days, the system hangs more than 6 times a day due to deadlocks. We have one clustered index column in our main table (Primary key field) I shared the deadlock graph for your reference unknown unknown (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT [Project4].[C3] AS [C1], [Project4].[C59] AS [C2], [Project4].[C60] AS [C3], [Project4].[C61] AS [C4], [Project4].[C62] AS [C5], [Project4] unknown unknown (@0 int,@1 datetime2(7),@2 int,@3 datetime2(7),@4 int,@5 int,@6 int)UPDATE [dbo].[Mapping] SET [AssToId] = NULL, [MapStatus] = @0, [Updated] = @1, [Umag] = @2, [MappingCompleteDate] = @3, [DActive] = @4, [Old_Umag] = @5 WHERE ([Id] = @6) unknown unknown (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT [Project4].[C3] AS [C1], [Project4].[C59] AS [C2], [Project4].[C60] AS [C3], [Project4].[C61] AS [C4], [Project4].[C62] AS [C5], [Project4] unknown unknown (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 datetime2(7),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 datetime2(7))SELECT [Project4].[C3] AS [C1], [Project4].[C59] AS [C2], [Project4].[C60] AS [C3], [Project4].[C61] AS [C4], [Project4].[C62] AS [C5], [Project4]

I have an application that runs the following query in multiple parallel threads: update Table1 SET Col1 = 'Some value' WHERE Order_ID = @orderID Order_ID is NOT the primary key on this table, but the @orderID is different for each thread, this update used to run fine until a non-clustered index was introduced: CREATE NONCLUSTERED INDEX MyIndex ON dbo.Table1 (Col2, Col3, Col4 ... Order_ID) INCLUDES (Col5, Col6, Col7 ... Col1) After this index was introduced, the following deadlock started occurring: I considered the deadlock sequence presented on this answer , but I think if that were the case my deadlock should also happen without the index. How could my index be causing this deadlock and how should it be fixed without complete removal of the index? **-- Edit --** This is the .xdl file saved by the SQL Profiler: unknown unknown UPDATE Securities_Fund_Deals SET Market_Deal_Reference = '[No Deal Ref]' WHERE Securities_Orders_ID = 100002 unknown unknown UPDATE Securities_Fund_Deals SET Market_Deal_Reference = '[No Deal Ref]' WHERE Securities_Orders_ID = 100003

UPDATE TOP(1000) Transaction WITH(ROWLOCK) 
			SET f_del = 1 , f_editTime = GETDATE() 
			WHERE ISNULL(f_del,0) '') 
			AND DATEDIFF(ss, f_time, GETDATE()) > 15 * 6    
            
            
Proc [Database Id = 7 Object Id = 715201648]   
          
          
            
              


DELETE TOP(1000) Transaction 
       WHERE 
					( 
						(ISNULL(f_accessState,0)2)
						OR
						(ISNULL(f_accessState,0)> 100000000 
         AND DATEDIFF(DAY, f_time, GETDATE())>7)    


              
exec DatatoOld    
            
            
exec DatatoOld

CREATE NONCLUSTERED INDEX [IX_Transaction_f_fgzl_f_time_f_State_8FBEB7] 
       ON [dbo].[Transaction] 
(
 	[f_fgzl] ASC,
 	[f_time] ASC,
 	[f_state] ASC
) 
INCLUDE 
(
 	[f_editTime],
 	[f_del],
 	[f_money]
)

The UPDATE statement runs once per minute, while the DELETE statement runs once per day. f_time column logic indicates that the same row would not be UPDATED/DELETED at the same time. Both queries will not seek the non clustered index. What could be the cause of this deadlock? **12/23 Edit:** By using extended events to track locks while rerunning the queries, I found out that only X locks are acquired on the index and no U locks are acquired at all during the index update. The update statement should not even have U locks on the index at all during the deadlock.

In searching documentation online, I have found that there can be row, page, or table locks. However, I ran a SQL Server Profiler trace and noticed both a Page Lock and an Object lock. I can easily discern the table involved in the Page lock from the "Object name". However, the Object lock does not give any further details. How can I know what the non-victim process locked?c

Captured the XML graph for a given deadlock involving some large / multi query sp's. However, not really sure how to interpret the line, stmtstart, stmtend of the frame element to determine which line of sproc TSQL encountered the deadlock. Line 214 doesn't seem to line up with any query in the SSMS editor but I am not sure how the engine normalizes sproc TSQL when line numbering. How do you resolve the captured line number with the line number as displayed in SSMS sproc definition?

exec sp_executesql @NSQL, N'@ARG1 int', @ARG1 = @MyArg1

I'm trying to make sense of why SQL Server (2014) is placing an eXclusive key lock during a deadlock scenario. I've pasted the entire deadlock graph below. I'm confused because the deadlock is happening between two SELECT statements, both running as a single READ COMMITTED statement, and NOT within a transaction (so there're no UPDATES etc occurring elsewhere in the same transaction). I believe the deadlock occurs because each process is creating a range of key locks on the index and due to the order they are acquired, there is a deadlock occurring. However, if the processes were creating only Shared locks, there should be no deadlock (based on my understanding)! So the fundamental question is - why would a eXclusive key lock be acquired by these SELECT statements? I expect this just comes down to a misunderstanding on my part about locking. Any advice would be much appreciated. SELECT ID, Message FROM [dbo].[Messages] WHERE MessageType = @Type Proc [Database Id = 32 Object Id = 1686297067] SELECT ID, Message FROM [dbo].[Messages] WHERE MessageType = @Type Proc [Database Id = 32 Object Id = 1686297067]

I have a windows application that is used by multiple users, often there are deadlocks in the database related to lock resources. Looking at the deadlock graph I see 2 processes and 2 resources, arrows representing the own/request, and that 1 process is killed to allow the other process to continue. Seems logical so far. I am trying to understand what exactly does a process in the deadlock graph represents because the query shown when hover over the Process is not the same as the query that holds the lock on the resource. So assuming there is a long transaction- begin transaction, followed by 100 lines of sql and commit. Now will each sql code run in single process?

I have setup extended event session with the following event: **xml_deadlock_report** When I look at the deadlock graph, it shows me the processes asking or requesting for the resource, and the type of locks requested and owned by the respective process. Hovering over the process shows me the SQL text. I can also see more details and export the xml/xdl. However, the **SQL text shown is the query in the buffer at the time of the deadlock**. This is not the same as the query that owns/requests the lock. I want to see the actual SQL query in this process that owns the lock or is requesting for the lock. Is there a way to capture the exact SQL text involved in deadlock (locks) using extended events?

I have a node.js script that is attempting to geocode records in a table. The script selects 100 records at a time that do not have lat/log data, geocodes them using a third party api and writes the results to the lat/long column for the corresponding record. This script has been causing deadlock errors in the database like the one below: > Transaction (Process ID 69) was deadlocked on lock resources with > another process and has been chosen as the deadlock victim. Rerun the > transaction. I setup SQL Server Profiler to run a trace and track deadlock data and was able to get one of the deadlock graphs but I can't quite figure out why deadlock is occuring. The two statements are both updates against different records in the same table. I checked the Isolation Level and it is Read Committed. While I haven't dealt with a lot database deadlocks, I didn't think these types of transactions would cause a deadlock. I've included the XML below in case it is helpful: unknown unknown UPDATE Members SET MbrLat = 28.03195, MbrLong = -81.09178 WHERE MemberUID = 'ID456' unknown unknown UPDATE Members SET MbrLat = 25.9510963, MbrLong = -81.1176539 WHERE MemberUID = 'ID123' Can anyone see why this deadlock would be occurring? Any suggestions for how I might get around these deadlock errors or what I should do to debug the issue? **UPDATES** - in response to the questions below: - I don't have an index on MemberUID - The execution plan can be found here .

A deadlock issue is encountered when adding column to an existing table, from my attempt to understand the graph, a linked server calling sp_columns_100_rowset is having an update lock on sysschobjs, which is very strange, any idea why is this happening? Deadlock xml: https://pastebin.com/ssGrYZ7q

We’re running into issues with unresolved deadlocks on the most recent version of SQL-Server 2017, has anybody seen a problem like this before? Detailed explanation: We migrated our database from Sybase-ASE to SQL Server 2017 (RTM-CU23) two weeks ago. In that time there have been a number of deadlocks reported in the System Health Events, three of which SQL Server was unable to kill, resulting in our business processes being blocked. We had to kill the SPID by hand, so that processing was able to continue. The queries themselves are rather complex and return a large resultset, because of this SQL-Server runs several threads in parallel (we are not limiting “maxdop”). The queries causing the deadlock were not the same every time, but they always included parallelism and tables with triggers. During an unresolved deadlock, the “xml_deadlock_report” event, which is generated every 100ms, shows the “waittime” of most stuck threads increasing, otherwise the status and content does not change. One of the stuck threads however has a waittime which does not increase but varies between 96 and 105ms. In the “xml_deadlock_report” the process owning this thread is reported as the “victim” (shown in the picture below).The problem is that this process does not actually get killed. This problem looks related to the recent KB Entries KB4338715 and KB4089473, but as noted above we're running the latest version. We’re looking for any ideas or hints that could help us.

This example regarding deadlock detection was shown during one of my lectures and I have a doubt about it. The image is shown below. For deadlock detection mechanism, why does T4 point only to T2 and not T1? Chronologically, T1 requests for the exclusive lock before T4. So shouldn't T4 point to T1 as well?

Have a deadlock XDL file with several exchange events listed. Not sure if these are good or bad or what causes them. What causes exchange events in SQL deadlock graph?

We have a stored procedure which purges old data. On a regular basis, it is getting deadlocked with an application stored procedure. Below is the deadlock graph: Deadlock XML: delete from DBNAME..TABLE_NAME with(rowlock) where email_id in (select email_id from ARCHIVAL_BATCH EXEC @return_value = [dbo].[PURGE_SP_NAME] @arch_mon_crit = 93, @batch_size = 3, @po_sql_code = @po_sql_code OUTPUT, @po_sql_msg = @po_sql_msg OUTPU DECLARE @return_value int, @po_sql_code int, @po_sql_msg nvarchar(2000) EXEC @return_value = [dbo].[PURGE_SP_NAME] @arch_mon_crit = 93, @batch_size = 3, @po_sql_code = @po_sql_code OUTPUT, @po_sql_msg = @po_sql_msg OUTPUT SELECT @po_sql_code as N'@po_sql_code', @po_sql_msg as N'@po_sql_msg' SELECT 'Return Value' = @return_value delete from DBNAME..TABLE_NAME with(rowlock) where email_id in (select email_id from ARCHIVAL_BATCH EXEC @return_value = [dbo].[PURGE_SP_NAME] @arch_mon_crit = 93, @batch_size = 3, @po_sql_code = @po_sql_code OUTPUT, @po_sql_msg = @po_sql_msg OUTPU DECLARE @return_value int, @po_sql_code int, @po_sql_msg nvarchar(2000) EXEC @return_value = [dbo].[PURGE_SP_NAME] @arch_mon_crit = 93, @batch_size = 3, @po_sql_code = @po_sql_code OUTPUT, @po_sql_msg = @po_sql_msg OUTPUT SELECT @po_sql_code as N'@po_sql_code', @po_sql_msg as N'@po_sql_msg' SELECT 'Return Value' = @return_value INSERT INTO TABLE_NAME_alt ( email_id ,activity_id ,text_content ) VALUES ( @p_email_id ,@p_activity_id ,@p_text_content EXEC @v_ret_value = APP_SERVER_NAME1 @p_email_id , @p_activity_id , @p_alias_id , @p_subject , @p_email_date , @p_email_size , @p_num_attachments , @p_charset , @p_message_id , @p_from_email_address , @p_recv_email_address , @p_delete_flag , @p_header , @p_content , @p_content_type , @p_text_content , @v_sql_code OUTPUT , @v_sql_message OUTPU EXEC @P0 = APP_SP_NAME @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , 'n' , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 , @P18 , @P19 , @P20 OUT , @P21 , @P22 , @P23 , @P24 , @P25 , @P26 , @P27 , @P28 , @P29 , @P30 , @P31 , @P32 , @P33 OUT , @P34 OU unknown (@P0 decimal(38,4) OUTPUT,@P1 bigint,@P2 bigint,@P3 nvarchar(4000),@P4 datetime2,@P5 int,@P6 int,@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(max),@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 decimal(38,4) OUTPUT,@P21 int,@P22 int,@P23 bigint,@P24 bigint,@P25 nvarchar(4000),@P26 bigint,@P27 datetime2,@P28 int,@P29 nvarchar(4000),@P30 nvarchar(4000),@P31 nvarchar(4000),@P32 bigint,@P33 decimal(38,4) OUTPUT,@P34 nvarchar(4000) OUTPUT)EXEC @P0 = APP_SP_NAME @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , 'n' , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 , @P18 , @P19 , @P20 OUT , @P21 , @P22 , @P23 , @P24 , @P25 , @P26 , @P27 , @P28 , @P29 , @P30 , @P31 , @P32 , @P33 OUT , @P34 OUT I have just started troubleshooting to fix the deadlock. I am not able to get a few things in about the deadlock graph: 1. Our Purging SP is a single SP which runs as one transaction, then why we are we seeing two processes for it. 2. What is exchange event? From where did it come? 3. At what level are locks are being taken? Row, Page or table? 4. How to proceed further? NOTE: 1. We are deleting less records batch wise in purging SP. 2. I have removed the actual SP and table Names with Aliases. **UPDATE 1:** After implementing Eric Darling's suggestion mentioned in the comment (i.e adding OPTION (MAXDOP 1) in the problematic statement of purging SP), it is still getting deadlocked, but this time the deadlock graph is different. This time there is only one process for purging SP instead of 2 and also exchange event has disappeared. Now error message is also the traditional one. I am still puzzled about why our purging SP is getting object level lock even though we are deleting very less number of rows compared to total number of rows in the table. Any help would be appreciated.

I have two tables: "machines" and "magictools". "magictools" refers to "machines" with a foreign key. I face a deadlock problem while executing many of these requests: //this will produce an "AccessExclusiveLock" of type "tuple" on machines SELECT FROM * machines FOR UPDATE where id = :id; //this will produce a "RowExclusiveLock" on magictools and a "RowShareLock" on machines UPDATE magictools SET collections = "large-json" where id = :id As far I understood running many of these request produces deadlocks. Maybe it's only the updates who are performing this, I don't know. How should I avoid the deadlocks in this case? I have many indexes on these tables, maybe I have too many indexes? Below is the report of pg_activity when the problem happened. I do not understand the different modes and lock types, and, just, what happens here? Is it possible that just an update without any transaction causes a deadlock? deadlock trace link

Our system has randomly started deadlocking in areas where it previously had not, and an analysis of the deadlock graph looks like it should not even be happening to begin with. Here is a screenshot of the deadlock graph. For reference: The left node is a basic insert into the sync_entities table, where the right node is:

update sync_entities 
set A=B, C=D 
from sync_entities e 
join sync_revisions r on e.id=r.entity_id 
where r.id in ()

Based on my understanding of lock compatibility, requesting an S lock when an IX lock exists would block only if **Read Committed Snapshot** is off, but it is turned on for this database, and has been for a very long time. Am I missing something? The deadlock XML says that both processes were using isolationlevel="read committed (2)".

Our application encounters a deadlock every now and again (about once a week). The main culprit seems to be a query with two selects. One of them is to fill a temp table for performance reasons, the other is a relatively complex select with many joins to return the list of all Appointments with many details. The only potentially special thing I see about the second select is that it includes a self-join. The two-select-query is always part of the deadlock event report by SQL Server. The other query is a simple DML query (insert or update) on the same table, though this is not always the same DML query. Both queries run with standard READ COMMITTED isolation and not within a explicit transaction. The two queries are roughly as follows (I've shortened them for clarification)

DECLARE @futureAppointments TABLE(clientId int, StartDate date)
INSERT INTO @futureAppointments SELECT clientId, StartDate FROM Appointments where StartDate >= @startDate

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate

UPDATE Appointments SET someField = @value WHERE id = @id

Example 2: deadlock2.xml , Example 3: deadlock3.xml , How would I try to prevent deadlocks from happening in this scenario? Also, does anyone know why the first statement with two selects would acquire a U lock on the selected table's PK as in Example 3? I don't think that it matters, but it seems strange.

I have spent a few days trying to debug a deadlock that occurs in production. This is one I didn't understand however. Below is pseudo code for the stored procedure. The stored procedure generates a deadlock with another instance of itself. I know that the code is not brilliant, this old code on the system. But I want to understand why this deadlocks. The graph always shows the second selects deadlocking. The first select performs a table scan, so I believe that places click on all rows in table. I'd have thought this would queue one stored procedure behind the other. I cannot recreate this after days if trying. Begin tran Begin tran Set isolation level repeatable read //This select does a table scan, so I believe does X lock on all rows Select a,b,c From queuetable with(xlock, rowlock) Where a = x Delete from queuetable Where a = X Commit tran Begin tran //Get oldest row - this is the deadlocking query Select top 1 a,b,c From queuetable Order by b //Mark row to be processed Update queuetable Set c=processing Where a = y Commit tran Commit tran