title: SQL Concurrent delete locking
body: <p>Some Background Information:<br>
  The data is regularly updated by deleting the old data and inserting the new.
  The data is clustered into profiles which I use as part of the primary key and for deleting the old data.<br>
  Only a single Process writes data so don't have to worry about update conflicts by someone else.<br>
  Other process only read the data which i planned to solve via Snapshot-Isolation.<br>
  I access the data via entity framework 6, code first modeling.</p>
  
  <p>The problem:
  I start the gathering of data for several profiles parallel. Works fine for all tables with simple foreign key relation but for one with a self reference.</p>
  
  <pre><code>public class Web
  {
      public Web()
      {
          CacheDate = DateTime.Now;
      }
  
      [Key, Column(Order = 0)]
      public Guid ProfileGuid { get; set; }
      [Key, Column(Order = 2)]
      public Guid WebId { get; set; }
  
      public string Data { get; set; }
  
      public virtual List&lt;List&gt; Lists { get; set; }
      public virtual List&lt;Web&gt; SubWebs { get; set; }
  
      public Guid? ParentProfileGuid { get; set; }
      public Guid? ParentWebId { get; set; }
  
      public virtual Web ParentWeb { get; set;}
  }
  
   modelBuilder.Entity&lt;CacheWeb&gt;()
       .HasMany(e =&gt; e.SubWebs)
       .WithOptional(e =&gt; e.ParentWeb)
       .HasForeignKey(e =&gt; new { e.ParentProfileGuid, e.ParentWebId })
       .WillCascadeOnDelete(false);
  </code></pre>
  
  <p>Due to performance issues i don't use the EF to delete the data but custom sql commands: <code>InsertContext.Database.ExecuteSqlCommand(String.Format("DELETE FROM Webs WHERE ProfileGuid = '{0}'", profile.Guid));</code></p>
  
  <p>If i call two of these deletes in concurrent transactions, the first call creates a lock which prevents the second transaction to continue. </p>
  
  <p>As far as i can tell, the lock is somehow connected to the Index of the self-reference.</p>
  
  <p>I just found another strange behavior, it depends on which transaction deletes first.
  The only difference in the data i can find is the depth of the parent-child relation.<br>
  If i run transaction A first (has one parent and a few direct children) i can't run transaction B (has some parent->child->childchild relations) afterwards but if i run B first i can run A afterwards without getting into trouble with existing locks.</p>
  
  <p>Is there a way to solve this problem? Or any idea what could be the problem with the lock? 
  If you need more information please comment on what exactly you need, as I am not really sure what's important.</p>
  
  <p>EDIT: for clarification</p>
  
  <pre><code>BEGIN Transaction t1
  DELETE FROM Webs WHERE ProfileGuid = 'b35dbba4-54fc-4df7-b1c8-e559d81dfee3'
  BEGIN Transaction t2
  DELETE FROM Webs WHERE ProfileGuid = 'b35dbba4-54fc-4df7-b1c8-e559d81dfee4'
  </code></pre>
  
  <p>works. Swap the order and t1 has to wait for t2 to finish.</p>
  
  <p>EDIT2: Execution Plans 
  <a href="https://i.stack.imgur.com/wN3bH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wN3bH.png" alt="Seek"></a>
  <a href="https://i.stack.imgur.com/UtaLv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UtaLv.png" alt="Scan"></a></p>
  
  <p>EDIT3: Model and full queries
  <a href="https://i.stack.imgur.com/g6Wqr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/g6Wqr.png" alt="model_s"></a>
  The Execution plan is from the 3rd query (deleting of CacheList)</p>
  
  <pre><code>BEGIN Transaction t1
  DELETE FROM CacheItems WHERE ProfileGuid = 'B35DBBA4-54FC-4DF7-B1C8-E559D81DFEE3'
  DELETE FROM CacheFolders WHERE ProfileGuid = 'B35DBBA4-54FC-4DF7-B1C8-E559D81DFEE3'
  DELETE FROM CacheLists WHERE ProfileGuid = 'B35DBBA4-54FC-4DF7-B1C8-E559D81DFEE3'
  DELETE FROM CacheWebs WHERE ProfileGuid = 'B35DBBA4-54FC-4DF7-B1C8-E559D81DFEE3'
  </code></pre>