What is an efficient way to handle inserts of unique "immutable" entities by multiple producers with optimistic concurrency approach?
<p>Assume a system with multiple concurrent producers that each strives to persist some graph of objects with the following common entities uniquely identifiable by their names:</p>

<pre><code>CREATE TABLE CommonEntityGroup(
    Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL
);
GO

CREATE UNIQUE INDEX IX_CommonEntityGroup_Name 
    ON CommonEntityGroup(Name)
GO


CREATE TABLE CommonEntity(
    Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    CommonEntityGroupId INT NOT NULL,
    CONSTRAINT FK_CommonEntity_CommonEntityGroup FOREIGN KEY(CommonEntityGroupId) 
        REFERENCES CommonEntityGroup(Id)
);
GO

CREATE UNIQUE INDEX IX_CommonEntity_CommonEntityGroupId_Name 
    ON CommonEntity(CommonEntityGroupId, Name)
GO
</code></pre>

<p>For example, producer A saves some <code>CommonEntityMeeting</code>s, while producer B saves <code>CommonEntitySet</code>s. Either of them has to persist <code>CommonEntity</code>s related to their particular items.</p>

<p>Basically, the key points are:</p>

<ul>
<li>There are independent producers. </li>
<li>They operate concurrently. </li>
<li>Theoretically(though that <em>may</em> change and is not yet exactly true now) they will operate through the same Web Service (ASP.Net Web API), just with their respective endpoints/"resources". So ideally proposed solution should not rely on that.</li>
<li>They strive to persist different graphs of objects that contain possibly not yet existing CommonEntity/CommonEntityGroup objects.</li>
<li>CommonEntity/CommonEntityGroup are <strong>immutable once created and will never be modified or removed thereafter</strong>.</li>
<li>CommonEntity/CommonEntityGroup are unique according to some of their properties (<code>Name</code> and related common entity if any(e.g. <code>CommonEntity</code> is unique by <code>CommonEntity.Name</code>+<code>CommonEntityGroup.Name</code>)).</li>
<li>Producers do not know/care about IDs of those <code>CommonEntities</code> - they usually just pass DTOs with <code>Names</code>(unique) of those <code>CommonEntities</code> and related information. So any <code>Common(Group)Entity</code> has to be found/created by <code>Name</code>.</li>
<li>There is a
certain possibility that producers will try to create the same
CommonEntity/CommonEntityGroup at the same time.</li>
<li>Though it is much
more likely that such CommonEntity/CommonEntityGroup objects will already
exist in db.</li>
</ul>

<p>So, with Entity Framework(database first, though it probably doesn't matter) as <a href="https://en.wikipedia.org/wiki/Data_access_layer" rel="nofollow noreferrer">DAL</a> and SQL Server as storage what is an efficient and reliable way to ensure that all those producers will successfully persist their intersecting object graphs at the same time?</p>

<p>Taking into account that <code>UNIQUE INDEX</code> already ensures that there won't be duplicate <code>CommonEntities</code> (Name, GroupName pair is unique) I can see the following solutions:</p>

<ol>
<li><b>Ensure that each CommonEntity/CommonGroupEntity is found/created+<a href="https://msdn.microsoft.com/en-us/library/bb336792(v=vs.110).aspx" rel="nofollow noreferrer">SaveChanged()</a> before building the rest of the object's graph.</b></li>
</ol>

<p>In such a case when <code>SaveChanges</code> is called for related entities there won't be any index violations due to other producers creating the same entities a moment before.</p>

<p>To achieve it I will have some </p>

<pre><code>public class CommonEntityGroupRepository // sort of
{
    public CommonEntityGroupRepository(EntitiesDbContext db) ...

    // CommonEntityRepository will use this class/method internally to create parent CommonEntityGroup.
    public CommonEntityGroup FindOrCreateAndSave(String groupName)
    {
        return
            this.TryFind(groupName) ?? // db.FirstOrDefault(...)
            this.CreateAndSave(groupName);
    }

    private CommonEntityGroup CreateAndSave(String groupName)
    {
        var group = this.Db.CommonEntityGroups.Create();
        group.Name = groupName;
        this.Db.CommonGroups.Add(group)

        try
        {
            this.Db.SaveChanges();
            return group;
        }
        catch (DbUpdateException dbExc)
        {
            // Check that it was Name Index violation (perhaps make indices IGNORE_DUP_KEY)
            return this.Find(groupName); // TryFind that throws exception.
        }
    }
}
</code></pre>

<p>With this approach there will be multiple calls to SaveChanges and each CommonEntity will have its own sort of a Repository, though it seems to be the most reliable solution.</p>

<ol start="2">
<li><b>Just create the entire graph and rebuild it from scratch if Index violations occur</b></li>
</ol>

<p>A bit ugly and inefficient (with 10 <code>CommonEntities</code> we may have to retry it 10 times), but simple and more or less reliable.</p>

<ol start="3">
<li><b>Just create the entire graph and replace duplicate entries if Index violations occur</b></li>
</ol>

<p>Not sure that there is an easy and reliable way to replace duplicate entries in more or less complex object graphs, though both case specific and more generic reflection-based solution can be implemented.</p>

<p>Still, like a previous solution it may require multiple retries.</p>

<ol start="4">
<li><b>Try to move this logic to database (SP)</b></li>
</ol>

<p>Doubt that it will be any easier to handle inside stored procedure. It will be the same optimistic or pessimistic approaches just implemented on database side. </p>

<p>Though it may provide better performance(not an issue in this case) and put the insertion logic into one common place.</p>

<ol start="5">
<li><strong>Using SERIALIZABLE isolation level/TABLOCKX+SERIALIZABLE table hint in Stored Procedure</strong> - it should definitely work, but I'd prefer not to lock the tables exclusively more than it is actually necessary, because actual race is quite rare. And as it is already mentioned in the title, I'd like to find some optimistic concurrency approach.</li>
</ol>

<hr/>

<p>I would probably try the first solution, but perhaps there are better alternatives or some potential pitfalls.</p>