When working on ASP.Net systems where multiple users could potentially change the same information in a database such as SQL Server, there is a real concern that one user could make a change and then another user could erase that change by updating the record with a slightly older copy that they had been working on for a while.
Handling this issue efficiently is essential to writing larger systems in .Net so I have done some research and my findings for at least a partial solution appear below.
While there are many reasons why ADO.Net is a huge improvement, it does introduce one very major problem that we must solve before we can use .Net to write larger database systems. The issue is in managing concurrency, i.e., preventing multiple users from overwriting each others' changes when sharing a table in the database. The email explains how the problem can occur and how I have arrived at what I believe is the best possible solution based on extensive reading and testing of many suggested solutions.
The Problem
ADO.Net uses a disconnected data access model, i.e, a connection is made to the database only long enough to perform a specific action and then the connection is immediately closed. This approach is the opposite of how Visual Basic 6 typically worked, which opened a connection to the database and kept it open as long as the data would be needed.
ADO.Net's disconnected data model provides superior performance and scalability since "expensive" database connections are used very sparingly and can be easily shared among applications. From an application developer's perspective, the biggest disadvantage is the inability to lock a record for updates. This means that ADO.Net developers must explicitly manage concurrency issues. Reading a record, allowing the user to change it, then updating the record without any further checking can easily overwrite someone else's changes.
Possible SolutionsTwo possible solutions are generally discussed on managing database concurrency; Pessimistic Locking vs. Optimistic Locking. Pessimistic locking means locking the record from the time that the data is read until the time that the update is completed. Optimistic Locking means re-establishing the connection to the database to perform the update and checking to make sure that the record has not changed since it was read.
While Pessimistic Locking is possible in ADO.Net, it is not recommended. It prevents any other access to the record while it is locked and maintains lengthy database connections, resulting in reduced performance in the database.
Optimistic Locking is the generally recommended solution but a reliable method must be determined for ensuring that the record has not changed prior to applying the update or deleting the record.
Implementing Optimistic Locking
The challenge in Optimistic Locking is to have a reliable method to ensure that the table row has not changed before being updated or deleted, even when there is heavy usage of the table. This challenge is even greater when there is a Master-Detail relationship that must ensure integrity across multiple tables.
The first requirement is to have a field in the table row that automatically changes when the record is created or updated.
If you need to know when the change was made and by whom, then you must add a varchar field for the username, a datetime field for the update time, and write a database trigger for the table that automatically updates these fields at each SQL Insert or Update. This introduces the possibility of application errors due to an incorrect or missing trigger. This is not enough reason to avoid this triggers, but care must be taken to make sure the triggers are properly implemented and maintained.
If it is not critical that you know when the change was made or by whom, but are just interested in managing the concurrency issues, then using a Timestamp column on the table is the preferred method. The Timestamp data type is a bit mis-named since it does not contain any date or time, but instead is just a 64-bit integer that is incremented each time that any change is made to any table in the database. Thus it serves as a reliable unique indicator that a record has not changed, but the number itself has no meaning.
The Timestamp column can appear in a Select query like any other column but cannot be directly updated. When displayed by SQL Query Analyzer, it may show a value like Ox000023 where the "23" is actually a hex number, i.e., decimal value 35. A row can be selected by its Timestamp value if you use the decimal value, for example, "Select * from MyTable where MyTimestampField = 35" is a valid query.
To implement Optimistic Locking, the Timestamp value must be returned with the data (as data type UInt64) for each row in the query. That Timestamp can then be used as the single condition in the WHERE condition of the UPDATE statement to ensure that the unchanged row is being updated. To use a UInt64 value as a string in a WHERE condition, it must be converted with a custom function such as GetSQLTimestamp function (shown at the end of this posting).
If the UPDATE command returns 0 rows affected, then the record was changed by someone else during the time that the application had it and the update is invalid. Typically you would notify the user that the data had changed, re-query and re-display the data, and ask the user to make the change again with the new data and re-attempt the update. This process is repeated until an update succeeds or the user declines to try again.
For single table updates, using the GetSQLTimestamp function is the easiest way to manage optimistic concurrency as long as you ensure that your UPDATE SQL command always uses a "WHERE tsTimestamp =
" condition and you verify that one and only one row changed.
To enforce optimistic concurrency on updates against multiple tables, use of a stored procedure is required so that a transaction can span the updates against all the tables, and succeed in making any change only if ALL the timestamp values are a match. I'm still working on the easiest solution for that, I'll post more info when I finish. Comments/Suggestions are welcome.
Here is the function that gives you the string value of a timestamp field that can used in the WHERE condition. Note that you can pass the datarow field in directly, no CAST or CTYPE is needed first.
Public Function GetSQLTimestamp(ByVal tsTimestamp As Object) As String
Dim oTimestamp() As Byte = CType(tsTimestamp, System.Byte())
Dim strTimestamp As String = "&H"
For i As Integer = 0 To oTimestamp.GetUpperBound(0)
Dim strHex2Dig As String = Hex(oTimestamp(i)).PadLeft(2, "0"c)
strTimestamp += strHex2Dig
Next i
Return Val(strTimestamp).ToString
End Function
I hope you find this helpful.
Joe
