<< Rhino ETL 2.0 | Home | Donjon - Hammett's Teasing >>

Serialazble isolation level on rows that does not exists

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.

Print | posted on Friday, January 18, 2008 3:38 AM

Feedback

Gravatar

# re: Serialazble isolation level on rows that does not exists 1/18/2008 9:48 AM Ken Egozi

What about the client putting a guid on the entity to be entered, the db having a unique index on the said guid, and then simply the first insert would work, the others would either get a unique violation error (the poor ones that would come in the milliseconds after the first) and the others would just see that the line exists and won't insert.

this should work with a non restrictive lock.

and having a guid on entities in distributed scenarios is a good practice anyway.

Gravatar

# re: Serialazble isolation level on rows that does not exists 1/18/2008 11:16 AM Sasha Goldshtein

The serializable isolation level was constructed to prevent phantoms. Therefore, it will not allow a transaction to insert a phantom row which would violate the semantics of another transaction with serializable isolation. (The lock in this case might be on table granularity, which is really bad contention-wise.)

Gravatar

# re: Serialazble isolation level on rows that does not exists 1/18/2008 11:49 AM Stefan Wenig

SQL Server usually locks only the rows that are affected by an operation (although lock escalation can lead to table locks when holding a lot of individual locks would seem less efficient). On serializable level, ranges of non-existing records need to be locked too. Whenever possible, SQL Server attempts to lock index pages to achieve this. When operations on isolation level serializable lead to table locks even with low numbers of records, you should review your indexing strategy in relation to the affected queries.

Gravatar

# re: Serialazble isolation level on rows that does not exists 1/19/2008 1:32 AM Evan

Key-Range Locks:
http://msdn2.microsoft.com/en-us/library/ms191272.aspx

Gravatar

# re: Serialazble isolation level on rows that does not exists 1/19/2008 6:06 PM Mik

Not sure why you need transaction here. Anyway, this code works with no exceptions in multythreaded environment:

private static void InsertToDatabase(object state)
{
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("IF NOT EXISTS(SELECT Id FROM Items WHERE Id = @id)");
sqlQuery.Append("BEGIN");
sqlQuery.Append(" INSERT INTO Items (Id) VALUES(@id)");
sqlQuery.Append("END");

int value = 0;
while (value < 12000)
{
value += 1;
try
{
using (SqlConnection connection =
new SqlConnection("data source=MARGARITKA\\SQLEXPRESS;integrated security=sspi;initial catalog=test"))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = sqlQuery.ToString();
command.Parameters.AddWithValue("id", value);
command.ExecuteNonQuery();
}
}
}
catch (SqlException e)
{
if (e.Number == 1205) //transaction deadlock
{
Console.WriteLine("{0}: Exception!!!", Thread.CurrentThread.Name);
Console.WriteLine(" - {0}", e.Message);

continue;
}
Console.WriteLine(e);
}
}
}


and function for running threads is here:

static void Main(string[] args)
{
string[] threadNames = new string[] {"1 - ibm", "2 - dec", "3 - intel", "4 - whatever", "5 - microsoft"};

List<Thread> threads = new List<Thread>();

foreach (string name in threadNames)
{
Thread thread = new Thread(InsertToDatabase);
thread.Name = name;
thread.Start("go " + name);

threads.Add(thread);
}

foreach (Thread thread in threads)
{
thread.Join();
}
}

Comments have been closed on this topic.