title: Can switching compatibility level from 100 to 130 lead to locking or deadlock problems?
body: <p>We are currently testing a switch to compatibility level (cl) 130, up from 100, in our development environment (sql server 2016). After the switch we noticed some errors:</p>
  
  <pre><code>could not execute batch command.[SQL: SQL not available] Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  </code></pre>
  
  <p>After some research, tracing and debugging I was able to confirm that we actually had a deadlock-issue. 
  We use a .net application which, using nhibernate, accesses the database. Some internal tasks (within the .net application) can be set up to us parallelism to finish quicker.
  These tasks usually split their workload in a way that a (row) deadlock should be impossible. I.e. Task 1 and Task 2 may access Table A and Table B at roughly the same time, but they will never access the same rows within each table. </p>
  
  <p>The tasks call some stored procedures which do something simple like:</p>
  
  <pre><code>UPDATE dbo.Tab1
  SET dbo.Tab1.Col1 = 'ValueY'
  FROM dbo.Tab2
  JOIN dbo.Tab3
  JOIN dbo.Tab4
  …
  WHERE Tab1.Col.2 = 'ValueX'
  </code></pre>
  
  <p>In essence this will run through Tab1, search for rows to be updated and update those.</p>
  
  <p>This was all running fine in compatibility level (cl) 100.
  After the switch to cl 130 we sometimes have deadlocks, we did not encounter before. </p>
  
  <p>The deadlock graph shows two Key locks on the same object id / hobt id, where two different server processes are holding a X-Lock and requesting a U.</p>
  
  <p>If I add irrelevant Rows to Table Tab1, for this particular Test, it will increase the number of pages to 23 and there are no more issues.</p>
  
  <p>I have read, that this whole issue might be caused by the small number of rows/pages. The optimizer/server behaves differently compared to a table with millions of rows which leads to a different lock behaviour and might result in deadlocks.</p>
  
  <p>But this does not answer my <strong>Question</strong>: Does the compatibility level, when switching from 100 to 130, directly affect locking and may even cause deadlock issues, where there were none before?</p>
  
  <p>PS: This is not a lock escalation issue as I have turned it off for Table Tab1.</p>