Dealing with Database Concurrency Conflicts in the Real World : Page 4

Solving the Problems
You really need the concept of read-only fields and to group the remaining fields into separate SQL UPDATES. To do this you need a CommandBuilder-like class that takes a data definition as input and produces a collection of SQL UPDATE command objects to update the data. In the SQL UPDATE command collection you would have one command for the address and a second for the credit info, presuming the current user has the rights to update both. The code then goes through the collection and populates each command with parameters containing old and new values for each field that your user wants to update. If all the old and new values for a specific command match there is clearly no change in that part of the data and the command doesn't need to execute. Once the code has loaded all SQL UPDATE commands the code can execute those commands. In this case you do not want to wrap these updates as part of a single transaction. If all SQL UPDATE commands execute successfully there was no conflict and the user can go on with their job. If a conflict occurs it is limited to a specific subset of the data and your application can update the other parts of the data without any delay. Because of the group wise nature of the updates, you cannot use a SQL row version. Instead you need to include the complete list of fields being updated together with the primary key in the SQL WHERE clause of each command.

An Example
The Main() method code below demonstrates a simple example of how an update could work. In this example I use the ExtendedProperties column to store the field grouping information. Columns with the same group name are bundled together into a single SQL UPDATE command.

   ''' <summary>
   ''' Test application to check database updates.
   ''' </summary>
   ''' <remarks></remarks>
   Sub Main()
      Dim dta As New pubsDataSetTableAdapters.titlesTableAdapter
      Dim table As pubsDataSet.titlesDataTable
      Dim cb As New CommandBuilder
   
      ' Load the data
      table = dta.GetData()
   
      ' Configure the table
      cb.ConfigureDataTable(table)
   
      ' Make a change to the data
      table.Item(0).price *= 1.1
   
      'table.Item(0).title = "New title"
      ' Update the database
      cb.UpdateTable(table)
      Console.WriteLine( _
         "Press any key to terminate the application.")
      Console.ReadKey()
   End Sub

The main program uses a typed table adapter to load the titles table from the Pubs database. All remaining functions are part of the CommandBuilder class created in the main function.

In the ConfigureDataTable() function shown below, each column receives an UpdateGroup extended property. You'll use this during the update to determine which fields are grouped together. This information isn't determined at update time because you might use the same table for multiple purposes in different business objects with different field groupings. Additionally some fields might be read-only-something that my sample code does not take into account.

   ''' <summary>
   ''' Configure the columns into update groups.
   ''' </summary>
   ''' <param name="table">The table with columns.</param>
   ''' <remarks></remarks>
   Public Sub ConfigureDataTable( _
      ByVal table As pubsDataSet.titlesDataTable)
      ' Basic data about the book
      table.title_idColumn.ExtendedProperties("UpdateGroup") = "Book"
      table.titleColumn.ExtendedProperties("UpdateGroup") = "Book"
      table.typeColumn.ExtendedProperties("UpdateGroup") = "Book"
      table.notesColumn.ExtendedProperties("UpdateGroup") = "Book"
      table.pubdateColumn.ExtendedProperties("UpdateGroup") = "Book"
   
      ' Financial data about the book
      table.pub_idColumn.ExtendedProperties("UpdateGroup") = _
         "Financial"
      table.priceColumn.ExtendedProperties("UpdateGroup") = _
         "Financial"
      table.advanceColumn.ExtendedProperties("UpdateGroup") = _
         "Financial"
      table.royaltyColumn.ExtendedProperties("UpdateGroup") = _
         "Financial"
   
      ' Sales information about the book
      table.ytd_salesColumn.ExtendedProperties("UpdateGroup") = _
         "Sales"
   End Sub

The UpdateTable() function (see Listing 1) first retrieves a collection of SQL UPDATE commands. Note that for this example I have skipped the SQL INSERT and SQL DELETE commands as you handle these in the same way as normal.

The following code shows how the GetUpdateCommands() function loops through all the field groups and creates a different SQL UPDATE command for each one. It bundles all separate commands together into a collection and returns the collection.

   ''' <summary>
   ''' Build a collection of update commands for the table.
   ''' </summary>
   ''' <param name="table">
   ''' The table that needs to be updated.</param>
   ''' <returns>
   ''' A collection of SQLCommands for the update.</returns>
   ''' <remarks></remarks>
   Private Function GetUpdateCommands(ByVal table As DataTable) _
      As List(Of SqlClient.SqlCommand)
      Dim groups As IDictionary(Of String, List(Of DataColumn))
      Dim cmds As List(Of SqlClient.SqlCommand)
   
      cmds = New List(Of SqlClient.SqlCommand)
      Console.WriteLine("Building update commands.")
      Console.WriteLine()
   
      ' Split all columns into groups based upon the
      ' UpdateGroup extended property.
      groups = SplitColumnIntoGroups(table)
      For Each group As List(Of DataColumn) In groups.Values
         Dim cmd As SqlCommand
         cmd = CreateUpdateCommand(table, group)
         cmds.Add(cmd)
         Console.WriteLine("Update command {0}:", cmds.Count)
         Console.WriteLine(cmd.CommandText)
         Console.WriteLine()
      Next
      Return cmds
   End Function

The SplitColumnIntoGroups() function (see Listing 2) takes all the columns in a table and splits them into separate update groups. This would be a good point to exclude read-only columns and possibly the primary key columns as these are typically not updatable.

In Listing 3, the CreateUpdateCommand() function creates a single SQL UPDATE command for each group of fields. The SQL WHERE clause consists of the primary key of the row as well as the fields that need to be updated. The CreateUpdateCommand() function compares each field both against the old value as well as its new value in order to avoid seeing the same change by two users as a conflict where it really doesn't matter that a field is overwritten with the same value.

While my technique isn't a complete solution for all update concurrency issues, I believe it is a step in the right direction. It is a work in progress and I feel that others will discover the best way to handle the separate cases. In the mean time, I hope that my solution helps you create applications that are friendlier and less technology-oriented in their nature.

Previous Page