Today, we will discuss a common scenario faced by many applications – handling simultaneous updates to a single row in a database table. This situation often arises in bidding systems or any application where multiple users are trying to update the same data at the same time.

Let’s consider a scenario where multiple parties are bidding on an item in a bidding system. During the last few minutes of bidding, many parties try to submit their bids with the same price. In order to ensure fairness, the application needs to check if the original data they retrieved is still the same before accepting their new proposed price.

From a technical perspective, the challenge is to allow only the very first user to update the row, while the remaining users need to re-fetch the row and update it again. Locking the record is not an option as it can create other problems.

One possible solution to this problem is to use the TIMESTAMP datatype in SQL Server. Let’s take a look at a simple example:

USE tempdb
GO

CREATE TABLE SampleTable (
    ID INT,
    Col1 VARCHAR(100),
    TimeStampCol TIMESTAMP
)
GO

INSERT INTO SampleTable (ID, Col1)
VALUES (1, 'FirstVal')
GO

SELECT ID, Col1, TimeStampCol
FROM SampleTable

UPDATE SampleTable
SET Col1 = 'NextValue'

SELECT ID, Col1, TimeStampCol
FROM SampleTable

DROP TABLE SampleTable
GO

In this example, we create a table with a TIMESTAMP column. When we insert the first value, a timestamp is generated. Whenever we update any value in that row, the timestamp is updated with the new value. This means that every time we update a value in the row, a new timestamp is generated.

Now, let’s apply this concept to the original scenario. In this case, multiple users are retrieving the same row and they all have the same timestamp. Before any user updates the row, they should retrieve the timestamp from the table and compare it with the timestamp they have. If both timestamps have the same value, it means that the original row has not been updated and they can safely update the row with the new value.

After the initial update, the row will contain a new timestamp. Any subsequent updates to the same row should also go through the process of checking the timestamp value. If the timestamp in memory is different from the timestamp in the row, it indicates that the row has changed and new updates should not be allowed.

The TIMESTAMP datatype can be very useful in this kind of scenario. It provides a simple and efficient way to handle concurrent updates without the need for record locking.

Do you have any other suggestions or alternatives? Please leave a comment below and I will be happy to discuss them in a future blog post.