Recently I was asked how to solve this problem. An external service makes a call to the application, to create / update an entity. This call can arrive in one of few endpoints. The catch is that sometimes the external service send the call to create a new entity to all the end points at the same time. This obviously caused issue when trying to insert the same row twice.

I suggested using serializable isolation level to handle this scenario, but I wasn't sure what kind of guarantees is makes for rows that do not exists. I decided to write this simple test case (warning: test code / quick hack, don't write real code like this!).

private static void InsertToDatabase(object state)
{
    int value = 0;

    while (true)
    {
        value += 1;
        try
        {
            using (SqlConnection connection =
                    new SqlConnection("data source=localhost;integrated security=sspi;initial catalog=test"))
            {
                connection.Open();
                using (SqlTransaction transaction =
                        connection.BeginTransaction(IsolationLevel.Serializable))
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT Id FROM Items WHERE Id = @id";
                    command.Parameters.AddWithValue("id", value);
                    command.Transaction = transaction;
                    if (command.ExecuteScalar() != null)
                        continue;
                    command.CommandText = "INSERT INTO Items (Id) VALUES(@id)";
                    command.ExecuteNonQuery();
                    Console.WriteLine("{1}: Wrote {0}", value, Thread.CurrentThread.ManagedThreadId);

                    transaction.Commit();
                }
            }
        }
        catch (SqlException e)
        {
            if (e.Number == 1205) //transaction deadlock
            {
                Console.WriteLine("{0}: Deadlock recovery", Thread.CurrentThread.ManagedThreadId);
                continue;
            }
            Console.WriteLine(e);
        }
    }
}

As a note, performance for this really sucks if you have contention, and I got a lot of transaction deadlocks when running it with multiply threads.

What I have observed was that it indeed inserted only a single id. All other isolation levels (including snapshot) will produce duplicate rows in this scenario.

Assumption proven, now I only need to find what kind of a lock it takes on a row that doesn't exists.