4
\$\begingroup\$

I'm trying to establish a concurrency violation verification in my SQL updates using C# and raw SQL.

What I'm doing now is storing the TimeStamp value in a byte[] upon selection and before updating I'm checking if the stored value is equal to the current value. here's the code:

static void update()
{
    using (SqlConnection connection = new SqlConnection(""))
    {
        try
        {
            //open connection
            connection.Open();

            //select row for update - for testing purposes it's in the same block 
            SqlCommand select = connection.CreateCommand();
            select.CommandText = "SELECT timeStamp FROM Person WHERE ID = 1";
            //store the time stamp in a byte[]
            byte[] ts = (byte[])select.ExecuteScalar();

            //check if the time stamp hasn't changed
            if (ByteArraysEqual(ts, (byte[])select.ExecuteScalar()))
            {
                //Update
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "Update Person SET Age=1111 WHERE ID = 1";
                command.ExecuteNonQuery();
            }
            else
            {
                //cocurrency violation occured
                Console.WriteLine("concurrency error!");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Update error:\n" + ex.Message);
        }
        finally
        {
            connection.Close();
        }
    }
}
static bool ByteArraysEqual(byte[] b1, byte[] b2)
{
    if (b1 == b2) return true;
    if (b1 == null || b2 == null) return false;
    if (b1.Length != b2.Length) return false;
    for (int i = 0; i < b1.Length; i++)
    {
        if (b1[i] != b2[i]) return false;
    }
    return true;
}

I wonder if this is the right approach to verify the Person has not been updated by another user between the selection and the actual update?

\$\endgroup\$
3
\$\begingroup\$

What optimistic concurrency usually does is update the row specifying the old value in the where clause:

var command = connection.CreateCommand();
command.CommandText = "Update Person SET Age = @age timeStamp = @newTimeStamp WHERE ID = @id AND timeStamp = @originalTimeStamp";
command.Parameters.Add("@age", 1111);
command.Parameters.Add("@newTimeStamp", DateTime.Now);
command.Parameters.Add("@id", 1);
command.Parameters.Add("@originalTimeStamp", originalTimeStamp);
int affected = command.ExecuteNonQuery();

if (affected == 0)
{
    throw new OptimisticConcurrencyException("Data was changed by someone else, please refresh and try again");
}

Also, please use parameters for your SQL queries!

\$\endgroup\$
2
  • \$\begingroup\$ thanks for clarifying things for me. and i am using parameters, i just removed them to in order to improve readability :) \$\endgroup\$ – Yoav Mar 13 '13 at 17:55
  • 1
    \$\begingroup\$ @Yoav in future don't, the point of code review is to ask for peer reviews of the actual code, it makes it easier for people to make relevant suggestions if it matches. \$\endgroup\$ – Trevor Pilley Mar 13 '13 at 22:11
1
\$\begingroup\$

Use optimistic locking when the logic requires separating a read and update of the same row into two transactions, usually because you're showing the original data to a user and letting them edit it. The example in your question doesn't need it because you're simply overwriting the existing age with a new value.

Trevor is correct about how to implement the optimistic locking check (3 and 4 below). The only part missing is how to get the original timestamp.

  1. Read the existing values along with the current timestamp.

    name, age, ts = select name, age, update_ts from person where id = 5;
    
  2. Allow the user to update the values on a form.

    Here another user may come along and do the same thing. Because there are two values, this could cause a problem. For example, user A fixes the person's name while user B fixes their age (both were incorrect). Without some form of locking, one of the updates would be overwritten.

  3. Store the new values and timestamp while checking the old timestamp.

    rows = update person 
           set    name = @name, 
                  age = @age, 
                  update_ts = now() 
           where  id = 5 
                  and update_ts = @ts;
    
  4. If the update failed, start over.

    if (rows == 0) {
        throw new OptimisticLockException();
    }
    
\$\endgroup\$
0
\$\begingroup\$
    using (SqlConnection connection = new SqlConnection(""))
    {
        try
        {
            //open connection
            if (connection.State != System.Data.ConnectionState.Open)

Why check connection state for just created connection? It will always be Closed :)

I suggest you to do update in stored procedure. This will allow you to do update in one DB call and using transactions will ensure that Person record is not modified between selection of timestamp and actual update.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.