- Multiuser Concurrency Problems
- Possible Solutions
- DHO Code Changes for Timestamping
- UI Code Changes for Timestamping
- Testing
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
DHO Code Changes for Timestamping
The first change that needs to be made is to the database table itself. I modified the Publishers table in the accompanying Biblio.mdb database file to include a LastModDateTime column. Although not really required, I then initialized this column of all existing records to the current system date/time by executing a bulk update SQL statement like this:
UPDATE Publishers SET LastModDateTime = Now;
In my previous article, I moved all code relating to data handling to clsPublisherList and clsPublisher, and encapsulated both into an ActiveX DLL component. For short, I called this component the DHO because it contains the Data Handling Objects. Timestamping logic is definitely not a function of the user interface, and so the changes to add timestamping to the sample application should be coded in the DHO.
Begin the code changes by modifying clsPublisher to support this new timestamp property. This involves the following:
Adding a LastModDateTime property to clsPublisher by declaring a Public variable in the General Declarations section
Changing the RSGet method to retrieve this new value from the ADO recordset object
Changing the DBInsert method to populate the LastModDateTime property with the current system date/time just before a Publisher is inserted into the database
Changing the DBUpdate method to update the LastModDateTime property with the current system date/time just before the Publisher is updated in the database
See the sample source code for the details on these changes.
The bigger change to the DHO is to compare the value of the timestamps while a Publisher is being updated by the clsPublisher.DBUpdate method. The basic logic for comparing timestamps during an update goes like this:
Execute a query to attempt to retrieve the LastModDateTime field only for the Publisher being updated.
If the query returns EOF, the Publisher is no longer in the database, so raise a "not found" error.
If the Publisher was found, store the timestamp value in a local variable.
Compare the timestamp in the local variable with the LastModDateTime property. If they are different, raise a "record updated by another user" error.
Otherwise, go ahead and update the Publisher as normal.
If the DBMS you are using does support pessimistic locking, still another improvement you can make to the algorithm is to pessimistically lock the record when you retrieve the timestamp value, and release the lock after the SQL Update statement has been executed. This addition will prevent the even more remote chance that another user or process will "sneak" in an update after you have retrieved the timestamp but before you execute the SQL Update statement. I don't include this in the sample application because for simplicity's sake, it uses a Microsoft Access database (and the MS Access database engine (Jet) doesn't support this kind of locking).
When coded, the timestamping logic in the clsPublisher.DBUpdate method looks like this:
Friend Sub DBUpdate(ByVal lDB As ADODB.Connection)
Dim lSQL As String
Dim lLastModDateTime As Date
Dim lRS As ADODB.Recordset
' See if this Publisher has been changed since it was last fetched.
lSQL = "SELECT LastModDateTime FROM Publishers"
lSQL = lSQL & " WHERE PubID = " & CStr(PubID)
Set lRS = lDB.Execute(lSQL)
If lRS.EOF = True Then
Call lRS.Close
Call Err.Raise(peNotFound, "clsPublisher", "PubID " _
& CStr(PubID) & " not found.")
End If
lLastModDateTime = lRS!LastModDateTime
Call lRS.Close
If lLastModDateTime <> LastModDateTime Then
Call Err.Raise(peUpdatedByAnotherUser, "clsStudent", _
"Publisher ID = " & PubID & _
" was updated by another user. Update cannot be completed.")
End If
' Update this record with the new property values.
LastModDateTime = Now
lSQL = "UPDATE Publishers SET "
lSQL = lSQL & " Name = " & DBStr(Name) & ","
lSQL = lSQL & " [Company Name] = " & DBStr(CompanyName) & ","
lSQL = lSQL & " Address = " & DBStr(Address) & ","
lSQL = lSQL & " City = " & DBStr(City) & ","
lSQL = lSQL & " State = " & DBStr(State) & ","
lSQL = lSQL & " Zip = " & DBStr(Zip) & ","
lSQL = lSQL & " Telephone = " & DBStr(Telephone) & ","
lSQL = lSQL & " Fax = " & DBStr(Fax) & ","
lSQL = lSQL & " LastModDateTime = " & DBDateTime(LastModDateTime)
lSQL = lSQL & " WHERE PubID = " & CStr(PubID)
Call lDB.Execute(lSQL)
End Sub
Note that if the timestamps don't match, a new error is raised, so we should also define that as an enumerated type as was done for the peNotFound error:
Public Enum PublisherErrors peNotFound = vbObjectError + 1 peUpdatedByAnotherUser End Enum
Also note that DBUpdate calls a function named DBDateTime. I use DBDateTime in a fashion similar to DBStr, in that it is declared globally, and its purpose is to convert a date/time field to a string suitable for sending to a DBMS as part of an SQL statement. My experience with working with different DBMS vendors shows that they each like dates and times to be formatted differently. So, by using DBDateTime, I can switch the backend DBMS that my application uses; if the new DBMS wants dates and times formatted differently from the previous DBMS, I have to make the change in only one place.
I also think it's a good idea to check timestamps when a record is being deleted, and so the code for clsPublisher.DBDelete is changed in the same manner as DBUpdate. Requerying for the Publisher during an update or delete request has the added benefit of checking to make sure that a correct PubID property value is set, and that the Publisher still exists in the database.