Concurrent User Updates in ASP.NET and SQL Server

Introduction

Concurrency is one of the key issues that should be addressed in distributed applications. When multiple users attempt to update the same set of data at the same time, updates will be made on a first come first serve basis without knowing the changes made by the other users, e.g.:

1. "User A" reads a data row to edit.
2. While User A still works on editing the data, User B reads the same data, modifies a field, and updates it.
3. User A finally updates the data without noticing the changes made by User B and User B's changes are lost.

Among several techniques available to address the concurrency issue, timestamp is one of the best options in terms of performance, reliability, and ease of implementation. A timestamp is a sequence of SQL Server activity on a row, represented as an increasing number in a binary format. The timestamp value is automatically updated every time a row containing a timestamp column is inserted or updated.

Implementation

The strategy here is whenever data is fetched from a database to update, get the timestamp value along with the other data and store it in a view state or hidden variable in the front end. When an update is attempted, compare the timestamp value in the database to the original timestamp value that is stored temporarily in the front-end. If they match, the record has not been modified by any other user, so perform the update. If they do not match, the record had been modified by some other user and a concurrency violation has occurred. Notify the user that the data has been modified by another user. At this point, we can provide the user an option to either override his changes or to revise the changes made by the other user. Let's now dive right into the code..

Step 1: Add a timestamp column to the targeted table(s) that you want to handle concurrent updates

In this step, we can add the user name column as well for tracking who has updated the data.

USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Contact')
DROP TABLE Contact
GO
CREATE TABLE [dbo].[Contact](
ContactID int IDENTITY(1,1),
ContactName nvarchar (100) NOT NULL , 
ChgUserID nvarchar (50) NOT NULL,
ChgTimeStamp timestamp) 

Step 2: Modify the SELECT statements to get the timestamps along with other data

Since the timestamp is a binary data field, in order to save it in an ASP.NET viewstate, we need to marshal the timestamp to a string. We have couple of choices on how to handle this marshalling. One option is, we can handle this in the .NET side by converting the timestamp to a string and vice versa, to store and retrieve in the viewstate or a hidden field (see 'Points of Interest'). The other option is to convert the timestamp to a bigint data type before it is returned to the front end to handle it easily in the .NET side (no binary to string conversion required in this case). I'm using the second option in this illustration.

SELECT ContactID, ContactName, 
CONVERT CONVERT(bigint, ChgTimeStamp) as 'TimeStamp'  
FROM Conact Where ContactID = @inContactID

Step 3: Modify the Save procedure accordingly

Add an additional timestamp parameter to the update procedure parameter list. Convert back the integer timestamp value to Timestamp type. Update the data if the record's current timestamp and the timestamp passed to the procedure are the same. In the case of a modified timestamp, the row will not be updated, i.e., the row count will be 0 and an error is raised.

CREATE PROC USP_UpdateContact(
     @inContactID nchar(10),
     @inContactName nvarchar(100),
     @inChgUserID nvarchar(50),
     @inChgTimeStamp bigint
)       
AS 
BEGIN   
    
    BEGIN TRANSACTION    
   
    --Declare Temporary variables
    DECLARE @ChgTimeStamp TIMESTAMP
    DECLARE @dbUserID NVARCHAR(50)          
    DECLARE @ErrorMsg VARCHAR(2000) --error strings 
    DECLARE @ERR VARCHAR    
            
    SET @ChgTimeStamp = _
        CONVERT(Timestamp,@inChgTimeStamp) --Convert Back
    SELECT @dbUserID = ChgUserID FROM Contact 
    WHERE ContactID = @inContactID 

    --INSERT/UPDATE 
    IF EXISTS (SELECT * FROM Contact  where ContactID = @inContactID)
    BEGIN
        UPDATE [dbo].[Contact] 
        SET              
            [ContactName] = @inContactName,
            [ChgUserID] = @inChgUserID
            WHERE ContactID = @inContactID 
            AND ChgTimeStamp = @ChgTimeStamp
    
        IF @@ROWCOUNT = 0 
        BEGIN 
            SET @ErrorMsg = _
            'The data you are about to save is modified by ' _
            + @dbUserID + _
            '. Please review the new data and save again.' 
            RAISERROR(@ErrorMsg,16,1, -999) 
            GOTO ERR_HANDLER 
        END 
        IF(@@ERROR <> 0) GOTO ERR_HANDLER 
    END 
    ELSE   
    BEGIN 
    INSERT INTO [dbo].[Contact] 
    (            
        [ContactName],   
        [ChgUserID] 
    )             
    VALUES 
    (
        @inContactName,    
        @inChgUserID            
    )             
    END    
    IF(@@ERROR <> 0) GOTO ERR_HANDLER  
    IF @@TRANCOUNT > 0 COMMIT TRANSACTION 
    RETURN 0  
    ERR_HANDLER:  
        IF @@TRANCOUNT > 0   
        ROLLBACK TRANSACTION     
        SELECT @ERR = @@error  
        RETURN @ERR  
END

Step 4: .NET code to accommodate the timestamp fetched from the database

Get the timestamp into a view state variable. Treat this variable as a regular web control in ASP.NET, i.e., fill it whenever the data is fetched from the database to display along with other web controls. Pass this value back to the database when the data is saved (Step 3).

//View state declaration 
private string TimeStamp
{
    get
    { 
        return (ViewState["TimeStamp"] != 
        null ? ViewState["TimeStamp"].ToString() : "");
    }
    set{ ViewState["TimeStamp"] = value; }
}

//Fill Time stamp
void DisplayContactUI()
{
    //Contact Display code here
    TimeStamp = ds.Tables[0].Rows[0]["TimeStamp"].ToString();
}

void SaveContactDB(..)
{
try
{
    //Open Connection, Add parameters
    ...

    pm = cm.Parameters.Add("@inChgTimeStamp", SqlDbType.BigInt);
    pm.Value = decimal.Parse(TimeStamp); 
    //TO DO: check for empty string 
    cn.Open();
    int i = cm.ExecuteNonQuery();
    cn.Close();
}
catch (SqlException sqlex)
{
    throw;
}
finally
{
}

Points of Interest

Alternative: To handle timestamp marshalling in the .NET side, use the following viewstate property. Here, we can get the timestamp column value from the database without converting it to a bigint.

public object TimeStamp
{
    get
    {
        byte[] bt = new byte[8];
        for(int i = 0; i < 8; i++)
        {
            bt[i] = 
                Convert.ToByte(
                ViewState["TimeStamp"].ToString().Substring(i * 3,2),16);
        }
        return bt;
    }
    set
    {
        ViewState["TimeStamp"] = BitConverter.ToString((byte[])value);
    }
}

The above code uses the BitConverter class to convert the array of bytes received from the database into a string. The Convert.ToByte method converts the string back to an array of bytes to send back the data to the database.

I would like to Thank Mr. Bruce J Mack and Luigi for their valuable suggestions and feedback. Please feel free to post your questions/ideas/suggestions. Thanks for stopping by. Mahalo!!!