GetOrCreate method for a database operation

Question

I have read this thread: Is it bad coding practice to create something in a get if it does not exist?

But, my question involve a method which gets a record from a database or creates it if it doesn't exist. I have two separate methods for checking if a record exists and the second to insert if it doesn't exists. But consequently, it sometimes creates many the same records if I call my api very fast. So, I created one method GetOrCreate and used the transaction. Do you think in that case the GetOrCreate method is a good approach?

public async Task<int> GetOrCreateRegionIdAsync(int companyId)
{
    using (SqlConnection connection = new SqlConnection("....."))
    {
        await connection.OpenAsync();
        using (var transaction = connection.BeginTransaction())
        {
            var regionId = await GetRegionIdAsync(connection, transaction, companyId); // GET
            if (regionId.HasValue)
            {
                return regionId.Value;
            }

            var region = new Region()
            {
                CompanyID = companyId
            };
            var newRegionid = await connection.InsertAsync<int>(region, transaction); // CREATE

            return newRegionid;
        }
    }
}

---

I need to get the region id (or create it if it doesn't exist) because then I need to save a record in a table called FormLinks which has a column 'regionId'.

-- the Regions table
SELECT [id]
      ,[name]
      ,[companyId]
      ,[available]
      ,[displayOrder]      
      ,[createDate]      
FROM [Regions]

-- the FormLinks table:
SELECT [id]
       [regionId]
      ,[available]
      ,[createDate]
      ,[url]
FROM [FormLinks]

Answer 1

No matter how you structure this, a race condition between processes will exist. The best thing you can do is attempt the INSERT, and if it fails, query again.

First, you will need a unique constraint on the table to eliminate duplicate entries for each company. The C# code will need to query for that region, and attempt an insert if the region was not found (the emphasis here is "attempt"). If an exception gets thrown, assume another process inserted that record in between your initial SELECT. Then SELECT the region again:

var regionId = await GetRegionIdAsync(connection, transaction, companyId); // GET

if (regionId.HasValue)
{
    return regionId.Value;
}

var region = new Region()
{
    CompanyID = companyId
};

try
{
    var newRegionid = await connection.InsertAsync<int>(region, transaction); // CREATE

    return newRegionid;
}
catch (SqlException)
{
    regionId = await GetRegionIdAsync(connection, transaction, companyId); // GET again

    return regionId.Value;
}

Answer 2

Problem statement: Given a companyId, either it's represented by an existing row in the Regions table or we need to INSERT a new row. The row lets us map companyId to regionId.

Racing callers who collide on some companyId must each obtain the same identical regionId.

---

relational integrity

The "get or create" issue is closely related to the ever popular "upsert" issue.

We have a one-to-one relationship between company and region. Region is PK and hence uniqueness is already enforced. Put a UNIQUE index on companyId and be done with it.

INSERT attempts which lose the race will encounter a uniqueness error. They should simply re-query to obtain the row newly created by another caller.

---

API design

it sometimes creates many of the same records if I call my api very fast.

A system that needs to preserve some invariant, like uniqueness, may need to hold a lock while it first checks and then optionally inserts. So the sequence is

• lock
• check
• optional insert
• unlock

Neglecting to hold a mutex would open us up to TOC/TOU races. Imposing an RDBMS backend UNIQUE index constraint asks the backend to perform those four steps.

We could perform those four steps at app level, with a mutex that is visible to all participants. On a busy system, multiple requests might arrive while we wait to acquire a lock. We could process just one of them, unlock, and repeat all steps for each remaining request. Or in the interest of greater throughput we might acquire and then process a whole batch of requests at once before releasing the lock. Many of the requests will be satisfied by a SELECT with no need of INSERT. Databases can usually deliver N rows of a single query quicker than delivering N single-row query responses. Which raises the topic of API design.

If somewhere in your system you have a loop iterating over a bunch of company IDs, leading to race collisions, consider adding an endpoint which can process the whole batch of IDs as a unit. In other words, you know you're going to hit each ID, so push it down and expose that knowledge to the RDBMS backend, allowing its planner to produce better plans, increasing throughput.

Rule of thumb: If you can, give the database a "big" transaction rather than N "small" transactions.