Concurrency Sql Transaction Processing

Question

Suppose I'm going to start a project using ASP.NET and SQL Server 2005. I need to develop a concurrency requirement for this application. I plan to add a TimeStamp column in each table. When updating the tables, I will verify that the TimeStamp column is the same as the one selected.

Would this approach suffice? Or are there any flaws to this approach under any circumstances?

Please advice.

thanks

Famously

Answer 1

First of all, the method that you describe in your question is, in my opinion, the best way for an ASP.NET application with MS SQL as a database. There is no lock in the database. It is ideal for permanently disconnected clients , such as web clients.

As you can read some answers, there is a misunderstanding in terminology. We all mean using Microsoft SQL Server 2008 or higher to store the database. If you open the topic "rowversion (Transact-SQL)" in the MS SQL Server 2008 documentation, you will find the following:

" timestamp is a synonym for rowversion and is subject to the behavior of a synonym for a data type." ... The syntax of timestamp is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new developments and plan to modify applications that are currently using this feature. "

The timestamp is a synonym for rowversion data type for MS SQL. It contains a 64-bit counter, which exists inside each database and can be considered as @@ DBTS . After changing one row in one database table, the counter will be increased.

When I read your question, I read "TimeStamp" as the name of a column of type rowversion . I personally prefer the name RowUpdateTimeStamp. In AzManDB (see Microsoft Authorization Manager with repository as DB), I could see such a name. Sometimes, the ChildUpdateTimeStamp function was also used to track the hierarchical structures of RowUpdateTimeStamp (in relation to triggers).

I implemented this approach in my last project and am very pleased. Usually you do the following:

• Add a RowUpdateTimeStamp column to each table in your database with rowversion type (this will be seen in Microsoft SQL Management Studio as a timestamp , which is the same).
• You must build all SQL SELECT queries to send the results to the client, so that you send an additional RowVersion value along with the master data. If you have SELECT with JOINT, you should send RowVersion the maximum RowUpdateTimeStamp value from both tables, e.g.

SELECT s.Id AS Id ,s.Name AS SoftwareName ,m.Name AS ManufacturerName ,CASE WHEN s.RowUpdateTimeStamp > m.RowUpdateTimeStamp THEN s.RowUpdateTimeStamp ELSE m.RowUpdateTimeStamp END AS RowUpdateTimeStamp FROM dbo.Software AS s INNER JOIN dbo.Manufacturer AS m ON s.Manufacturer_Id=m.Id 

Or cast data as shown below.

 SELECT s.Id AS Id ,s.Name AS SoftwareName ,m.Name AS ManufacturerName ,CASE WHEN s.RowUpdateTimeStamp > m.RowUpdateTimeStamp THEN CAST(s.RowUpdateTimeStamp AS bigint) ELSE CAST(m.RowUpdateTimeStamp AS bigint) END AS RowUpdateTimeStamp FROM dbo.Software AS s INNER JOIN dbo.Manufacturer AS m ON s.Manufacturer_Id=m.Id 

to hold RowUpdateTimeStamp as bigint , which corresponds to the ulong data type for C #. If you are doing OUTER JOINT or JOINTs from many tables, the MAX(RowUpdateTimeStamp) construct from all the tables will look a little more complicated. Since MS SQL does not support a function such as MAX (a, b, c, d, e), the corresponding construct may look like this:

 (SELECT MAX(rv) FROM (SELECT table1.RowUpdateTimeStamp AS rv UNION ALL SELECT table2.RowUpdateTimeStamp UNION ALL SELECT table3.RowUpdateTimeStamp UNION ALL SELECT table4.RowUpdateTimeStamp UNION ALL SELECT table5.RowUpdateTimeStamp) AS maxrv) AS RowUpdateTimeStamp 

3. All disconnected clients (web clients) receive and hold not only several rows of data, but also a row of RowVersion (type ulong ) of the data row.
4. In an attempt to change data from a disconnected client, the client must send RowVersion corresponding to the source data to the server. The spSoftwareUpdate stored procedure might look like

 CREATE PROCEDURE dbo.spSoftwareUpdate @Id int, @SoftwareName varchar(100), @originalRowUpdateTimeStamp bigint, -- used for optimistic concurrency mechanism @NewRowUpdateTimeStamp bigint OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- ExecuteNonQuery() returns -1, but it is not an error -- one should test @NewRowUpdateTimeStamp for DBNull SET NOCOUNT ON; UPDATE dbo.Software SET Name = @SoftwareName WHERE Id = @Id AND RowUpdateTimeStamp <= @originalRowUpdateTimeStamp SET @NewRowUpdateTimeStamp = (SELECT RowUpdateTimeStamp FROM dbo.Software WHERE (@@ROWCOUNT > 0) AND (Id = @Id)); END 

The dbo.spSoftwareDelete stored procedure dbo.spSoftwareDelete looks the same. If you do not enable NOCOUNT , you can create a DBConcurrencyException automatically generated in scripts. Visual Studio gives you the option to use optimistic concurrency like "Use optimistic concurrency" in the advanced options of the TableAdapter or DataAdapter .

If you look at the dbo.spSoftwareUpdate stored procedure, you will find that I use RowUpdateTimeStamp <= @originalRowUpdateTimeStamp in WHERE instead of RowUpdateTimeStamp = @originalRowUpdateTimeStamp . I do this because the @originalRowUpdateTimeStamp value that the client has is usually built as MAX(RowUpdateTimeStamp) from more than one table. So it may be that RowUpdateTimeStamp < @originalRowUpdateTimeStamp . Either you must use strict equality = and reproduce the same complex JOIN operator here as you used in the SELECT statement, or use the <= construct like I did, and stick to the same safe before.

By the way, you can build a very good value for ETag based on RowUpdateTimeStamp, which can be sent to the HTTP header to the client along with the data. With ETag, you can implement smart client-side data caching.

I cannot write all the code here, but you can find many examples on the Internet. I just want to reiterate once again that, in my opinion, rowversion -based concurrency optimization is the best way for most ASP.NET scripts .

Answer 2

In SQL Server, the recommended approach for a situation type is to create a column of type 'rowversion' and use it to check if the fields in this row have changed.

SQL Server ensures that if any value in a row changes (or a new row is inserted), the rowversion column will automatically update to another value. Providing this help database for you is much more reliable than trying to do it yourself.

In your update operations, you just need to add the where clause to verify that the rowversion value matches what it was when you first retrieved the row. If not, someone else changed the line (i.e. it's dirty)

Also from this page:

The timestamp syntax is out of date. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new developments and plan to modify applications that are currently using this feature.

Answer 3

I am not sure that concurrency should be handled in the database as follows. The database itself should be able to control isolation and transactional behavior, but the behavior of the threads must be executed in code.

Answer 4

The rowversion suggestion is correct, I would say, but its disappointment to see that the timestamp will be deprecated soon. Some of my OLD applications use this for various reasons, and then check concurrency.