Here is the question received just this morning.
“Pinal,
Our application is much different than other application you might have come across. In simple words, I would like to call it Asynchronous Updated Application. We need your quick opinion about one of the situation which we are facing.
From business side: We have bidding system (similar to eBay but not exactly) and where multiple parties bid on one item, during the last few minutes of bidding many parties try to bid at the same time with the same price. When they hit submit, we would like to check if the original data which they retrieved is changed or not. If the original data which they have retrieved is the same, we will accept their new proposed price. If original data are changed, they will have to resubmit the data with new price.
From technical side: We have a row which we retrieve in our application. Multiple users are retrieving the same row. Some of the users will update the value of the row and submit. However, only the very first user should be allowed to update the row and remaining all the users will have to re-fetch the row and updated it once again. We do not want to lock any record as that will create other problems.
Do you have any solution for this kind of situation?”
Fantastic Question.
I believe there is good chance that we can use timestamp datatype in this kind of application. Before we continue let us see following 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 st
GO
UPDATE SampleTable
SET Col1 = 'NextValue'
GO
SELECT ID, Col1, TimeStampCol
FROM SampleTable st
GO
DROP TABLE SampleTable
GO
Now let us see the resultset.
Here is the simple explanation of the scenario. We created a table with simple column with TIMESTAMP datatype. When we inserted a very first value the timestamp was generated. When we updated any value in that row, the timestamp was updated with the new value. Every single time when we update any value in the row, it will generate new timestamp value.
Now let us apply this in an original question’s scenario. In that case multiple users are retrieving the same row. Everybody will have the same now same TimeStamp with them. Before any user update any value they should once again retrieve the timestamp from the table and compare with the timestamp they have with them. If both of the timestamp have the same value – the original row has not been updated and we can safely update the row with the new value. After initial update, now the row will contain a new timestamp. Any subsequent update to the same row should also go to the same process of checking the value of the timestamp they have in their memory. In this case, the timestamp from memory will be different from the timestamp in the row. This indicates that row in the table has changed and new updates should not be allowed.
I believe timestamp can be very very useful in this kind of scenario. Is there any better alternative? Please leave a comment with the suggestion and I will post on the blog with due credit.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Hi Pinal,
the question is, why not using an usual datetime for this case? and is timestamp will be different according to their own timezone?
thanks
An alternative could be using built in change tracking functionality if supported in the sql version they are running.
Arroniry – Datetime would require explicit modification to keep it up to date and woukd be dependant upon any row creator or modifier to so. Timestamp is not actually date related as it appears to be, but just an incrementing number, automatically updated.
In fact “rowversion” should be used in place of timestamp to save confusion with the same named ANSI standard data type, plus timestamp is deprecated.
Totally agree with Parody,
rowversion should be used as timestamp is decpecated,
However, https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017 suggests some differences,
If you do not specify a column name, the SQL Server Database Engine generates the timestamp column name; however, the rowversion synonym does not follow this behavior. When you use rowversion, you must specify a column name.
“Before any user update any value they should once again retrieve the timestamp from the table and compare with the timestamp they have with them.”
But what about ‘thread safety’?
how about doing: update table1 set bid=123 where id=456 AND timestamp=
And save timestamp somewhere safe where users can’t modify it.
I think DATETIME type could be dangerous in this case, because of various timezones. Timestamp is safe here because it is defined only by SQL Server and only at time of execution.
The row can still change in between when you check the timestamp and when you do the update. Your logic should be in the update itself, only updating the row WHERE Timestamp=@firstTimestamp. Then after you do the update, check to see if you’re the one who successfully updated the record.
as it clearly states…
“timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms.”
and
The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
nice post..
+1 in agreement with birch’s comment above from December. Although you could still make this work as the article suggests but would need to wrap in a serializable transaction to lock the table in between your reading and writing. Or, at the very least, add a WITH (UPDLOCK) locking hint to your select statement—without such a hint, this is an awful recipe for deadlocks and I would recommend to avoid.
If multiple rows are updated,how to find out the last updated row timestamp in table? I don’t have timestamp column in table. Can you please give solution of this.
@Sanchayeeta Gaine, you can use “SELECT @@DBTS” to get the latest rowversion value for a database.
https://docs.microsoft.com/en-us/sql/t-sql/functions/dbts-transact-sql?view=sql-server-2017
But note that: “Duplicate rowversion values can be generated by using the SELECT INTO statement in which a rowversion column is in the SELECT list. We do not recommend using rowversion in this manner.”
https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017
Is timestamp indexed out of the box or should I index it if I plan on doing queries against a large table. Queries like SELECT max(timestamp) FROM TableA