C# locking records multiuser

Question

I am looking for suggestions to handle multi-user accessing a C#-Sql server application. C# program selects Top 5 rows from a table where date is null and then updates those records based on input from user. If more than one person is using the app, how can I make sure, data is saved consistently? I am using a grid control to show the data & a button which calls the SaveToDataBase procedure. Here's the part code

protected void Page_Load(object sender, EventArgs e)
{

    string sqlSel = @" SELECT   TOP 5 r.[keyid], name
                        FROM   db1.Table1 r where date is null  
                        GROUP BY r.keyid, name; ";

    if (!IsPostBack)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString))
        {
            connection.Open();
            SqlCommand cmdSel = new SqlCommand(sqlSel, connection);
            SqlDataReader reader1 = cmdSel.ExecuteReader();

            while (reader1.Read())
            {

                DataSet ds = GetData(sqlSel);
                if (ds.Tables.Count > 0)
                {
                    GridView1.DataSource = ds;
                    GridView1.DataBind();
                }
                else
                {
                    Response.Write("Unable to connect to the database.");
                }

            }
            connection.Close();
        }
    }


protected void SaveToDatabase()
{

    string datenow = DateTime.Now.ToString(@"MM\/dd\/yyyy h\:mm tt");
    string sqlUpd = @"UPDATE [db1].[Table1] set DateVerified=@datenow where KeyID=@keyID and name=@name";
    try
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString))
        {
            connection.Open();

            SqlCommand cmdUpd = new SqlCommand(sqlUpd, connection);
            cmdUpd.Parameters.Add("@datenow", SqlDbType.DateTime);
            cmdUpd.Parameters["@datenow"].Value = datenow;

            Int32 rowsAffected = 0;
            rowsAffected = cmdUpd.ExecuteNonQuery();

            connection.Close();

}
private DataSet GetData(string cmdSel)
{

    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString;
    DataSet ds = new DataSet();
    try
    {
        SqlConnection con = new SqlConnection(strConnString);
        SqlDataAdapter sda = new SqlDataAdapter(cmdSel, con);
        sda.Fill(ds);

Thanks Rashmi

Answer 1

To protect from updating a value with date assigned

update table set value = 1, date = getdate() 
where id = 1 and date is null 

I think you will get 0 rows updated if date is null
So you could provide user feedback

But 5 at a time is going to be a problem as that is going to increase the chance of collision (date is not null)

I would have some type of locking

Maybe assign a date of 1/1/1900 prior to update

update table set value = 1, date = getdate() 
where id = 1 and (date is null or date = '1/1/1900')

Then have some mechanism to set 1/1/1900 to null periodically for orphaned

Answer 2

You could use something like optimistic concurrency with a Version Id that should be updated every time someone changes the row, for example:

//  Table User
create table User(
    Id int primary key, 
    Name varchar(300) not null, 
    Version long not null default 0
);

// the select code
select Id, Name, Version
from User
where Id = ?

// the update code
update User
set
    Name = ?
    Version = Version + 1
where
    Id = ?
    and Version = ?

Imagine two users go to a screen where you can update the name of the user. Imagine the following order of your code:

UserA:  select Id, Name, Version from User where Id = 1;        // (1, John Doe, 0)
UserB:  select Id, Name, Version from User where Id = 1;        // (1, John Doe, 0)
UserA:  update User set Name = 'Jane Doe' Version = Version + 1 where Id = 1 and Version = 0;   // 1 row updated
UserA:  commit;
UserB:  update User set Name = 'Mr John Doe' Version = Version + 1 where Id = 1 and Version = 0;    // 0 row updated which means someone updated the row
UserB:  rollback;   // you should rollback and send an info to the user that someone changed the information he was seeing (refresh the screen)

With this approach you prevent the need for locking the rows. Every time you update o delete something and the number of affected rows are different than the ones you expected, optimistic concurrency should be applied. Most ORM frameworks already implement this approach by using a Version or a Timestamp, but the logic is the same. Keep in mind that the update of the Version field should always be performed.

You should get an idea how to implement with this pratical example about how an ORM (Entity Framework in this case) implement this logic here: http://www.asp.net/mvc/tutorials/getting-started-with-ef-5-using-mvc-4/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application

Answer 3

There are several different approaches:

You can add the WHERE-condition to your update statement. First user updating the records will get affected-rows = 5, the next one affect-rows=0 so you know his data cannot be save but must be reloaded (and eventually merged).

Or you can set the records as assigned to the user when fetching the data so the next one gets different records (WHERE (AssignedUser <> @MYUSER)).

If you really want to lock - which means another app cannot read the top 5 records until the first user saved - you could do reading and writing within one transaction with a very restrictive IsolationLevel.