title: Deadlock In Sql Server 2012 With Merge
body: <p>We have a complicated publishing model in our application that allows different users to see different objects.  We generate which users can see which objects as the view which determines this is quite slow.  We have a background service that runs every 10 minutes which uses a merge statement to ensure the stored table is up to date, we also have merge statement that runs per user at the beginning of a request.  Very rarely we are getting a deadlock situation when the merge runs for a user at the same time as the merge runs for the whole database and an update exists.  Is there a way to stop this happening as I would expect one to complete and then release the locks and then the next complete, find no data to update and continue.  Any help would be appreciated.</p>
  
  <p><img src="https://i.stack.imgur.com/KKJ75.png" alt="enter image description here"></p>
  
  <pre><code>&lt;deadlock&gt;
    &lt;victim-list&gt;
      &lt;victimProcess id="processe7058f0c8" /&gt;
    &lt;/victim-list&gt;
    &lt;process-list&gt;
      &lt;process id="processe7058f0c8" taskpriority="0" logused="344" waitresource="KEY: 7:72057600589430784 (dc897b8904f5)" waittime="1341" ownerId="62095831" transactionname="user_transaction" lasttranstarted="2015-02-06T16:29:17.037" XDES="0xe789ce3a8" lockMode="U" schedulerid="6" kpid="4072" status="suspended" spid="59" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-02-06T16:29:17.037" lastbatchcompleted="2015-02-06T16:29:17.033" lastattention="1900-01-01T00:00:00.033" clientapp=".Net SqlClient Data Provider" hostname="ZNPRODWEB1" hostpid="22512" loginname="DATABASE" isolationlevel="read committed (2)" xactid="62095831" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt;
        &lt;executionStack&gt;
          &lt;frame procname="DATABASE.Store.UpdateStoredPublishingSegmentUsersForUsers" line="20" stmtstart="794" stmtend="3468" sqlhandle="0x03000700d8b7073ae34ef00037a4000001000000000000000000000000000000000000000000000000000000"&gt;
  MERGE [Store].StoredPublishingSegmentUsers AS TARGET
        USING 
        (
            SELECT realTimePublishings.PublishingSegmentId, 
                   realTimePublishings.UserId, 
                   realTimePublishings.IsParticipating,
                   realTimePublishings.IsVisibleWithFilter
              FROM Publishing.PublishingSegmentUsersRealTimeData realTimePublishings
             INNER JOIN @UserIds u ON u.Id = realTimePublishings.UserId
        ) AS SOURCE
        ON TARGET.PublishingSegments_PublishingSegmentId = SOURCE.PublishingSegmentId 
        AND TARGET.Users_UserId = SOURCE.UserId 
        WHEN MATCHED AND (TARGET.IsParticipating != SOURCE.IsParticipating) 
                      OR (TARGET.IsVisibleWithFilter != SOURCE.IsVisibleWithFilter) THEN
          UPDATE SET TARGET.IsParticipating = SOURCE.IsParticipating,
                     TARGET.IsVisibleWithFilter = SOURCE.IsVisibleWithFilter
        WHEN NOT MATCHED BY TARGET THEN
          INSERT ([PublishingSegments_PublishingSegmentId], [Users_UserId    &lt;/frame&gt;
          &lt;frame procname="DATABASE.Store.ProcessStoreUpdatesForCustomer" line="51" stmtstart="2844" stmtend="3082" sqlhandle="0x030007008e48614a974ff00037a4000001000000000000000000000000000000000000000000000000000000"&gt;
  EXEC Store.UpdateStoredPublishingSegmentUsersForUsers @UserIds
  
           -- Update the stored incentive program leagues    &lt;/frame&gt;
        &lt;/executionStack&gt;
        &lt;inputbuf&gt;
  Proc [Database Id = 7 Object Id = 1247889550]   &lt;/inputbuf&gt;
      &lt;/process&gt;
      &lt;process id="processe7f0450c8" taskpriority="0" logused="352" waitresource="PAGE: 7:1:42475 " waittime="1272" ownerId="62094952" transactionname="user_transaction" lasttranstarted="2015-02-06T16:29:15.450" XDES="0xe6e6c63a8" lockMode="U" schedulerid="7" kpid="1260" status="suspended" spid="67" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-02-06T16:29:15.450" lastbatchcompleted="2015-02-06T16:29:10.670" lastattention="1900-01-01T00:00:00.670" clientapp=".Net SqlClient Data Provider" hostname="ZNPRODWEB1" hostpid="19952" loginname="DATABASE" isolationlevel="read committed (2)" xactid="62094952" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt;
        &lt;executionStack&gt;
          &lt;frame procname="DATABASE.Store.UpdateStoredPublishingSegmentUsersForUsers" line="20" stmtstart="794" stmtend="3468" sqlhandle="0x03000700d8b7073ae34ef00037a4000001000000000000000000000000000000000000000000000000000000"&gt;
  MERGE [Store].StoredPublishingSegmentUsers AS TARGET
        USING 
        (
            SELECT realTimePublishings.PublishingSegmentId, 
                   realTimePublishings.UserId, 
                   realTimePublishings.IsParticipating,
                   realTimePublishings.IsVisibleWithFilter
              FROM Publishing.PublishingSegmentUsersRealTimeData realTimePublishings
             INNER JOIN @UserIds u ON u.Id = realTimePublishings.UserId
        ) AS SOURCE
        ON TARGET.PublishingSegments_PublishingSegmentId = SOURCE.PublishingSegmentId 
        AND TARGET.Users_UserId = SOURCE.UserId 
        WHEN MATCHED AND (TARGET.IsParticipating != SOURCE.IsParticipating) 
                      OR (TARGET.IsVisibleWithFilter != SOURCE.IsVisibleWithFilter) THEN
          UPDATE SET TARGET.IsParticipating = SOURCE.IsParticipating,
                     TARGET.IsVisibleWithFilter = SOURCE.IsVisibleWithFilter
        WHEN NOT MATCHED BY TARGET THEN
          INSERT ([PublishingSegments_PublishingSegmentId], [Users_UserId    &lt;/frame&gt;
          &lt;frame procname="DATABASE.Store.UpdateStoredPublishingSegmentUsersForUser" line="9" stmtstart="352" stmtend="484" sqlhandle="0x0300070011dcfb3af84ef00037a4000001000000000000000000000000000000000000000000000000000000"&gt;
  EXEC Store.UpdateStoredPublishingSegmentUsersForUsers @UserIds;    &lt;/frame&gt;
          &lt;frame procname="DATABASE.Store.ProcessStoreUpdatesForUser" line="35" stmtstart="1814" stmtend="2048" sqlhandle="0x03000700832bce510e50f00037a4000001000000000000000000000000000000000000000000000000000000"&gt;
  EXEC Store.UpdateStoredPublishingSegmentUsersForUser @UserId
  
           -- Update the stored incentive program leagues    &lt;/frame&gt;
        &lt;/executionStack&gt;
        &lt;inputbuf&gt;
  Proc [Database Id = 7 Object Id = 1372466051]   &lt;/inputbuf&gt;
      &lt;/process&gt;
    &lt;/process-list&gt;
    &lt;resource-list&gt;
      &lt;keylock hobtid="72057600589430784" dbid="7" objectname="DATABASE.Store.StoredPublishingSegmentUsers" indexname="PK_StoredPublishingSegmentUsers" id="locke68752b80" mode="U" associatedObjectId="72057600589430784"&gt;
        &lt;owner-list&gt;
          &lt;owner id="processe7f0450c8" mode="U" /&gt;
        &lt;/owner-list&gt;
        &lt;waiter-list&gt;
          &lt;waiter id="processe7058f0c8" mode="U" requestType="wait" /&gt;
        &lt;/waiter-list&gt;
      &lt;/keylock&gt;
      &lt;pagelock fileid="1" pageid="42475" dbid="7" subresource="FULL" objectname="DATABASE.Store.StoredPublishingSegmentUsers" id="locke64335280" mode="IU" associatedObjectId="72057600589430784"&gt;
        &lt;owner-list&gt;
          &lt;owner id="processe7058f0c8" mode="IU" /&gt;
        &lt;/owner-list&gt;
        &lt;waiter-list&gt;
          &lt;waiter id="processe7f0450c8" mode="U" requestType="convert" /&gt;
        &lt;/waiter-list&gt;
      &lt;/pagelock&gt;
    &lt;/resource-list&gt;
  &lt;/deadlock&gt;
  </code></pre>