Resolve Concurrency Violations

Purpose

This tutorial is in addition to my tutorial on using the MyODBC Connector, and uses the MyStore database. It is assumed you have completed the first tutorial and have a working version of the MyStore project.

The original MyStore database does not account for Concurrency Violations, which can have detrimental affects on the integrity of any database. Concurrency Violations can be caused in multi-user environments when different users attempt to change the same record at the same time, or even in a single-user application when the DataSet looses synch with the underlying database.

For an introduction to Data Concurrency, visit this article and this article.
For a description of how a DataSet and the database can become out of synch, and alternative solutions, visit this article. (Thanks, Reg!)

As written, the MyStore database uses the “last in wins” approach. This means that when different users make changes to the same record at the same time, the last to save will overwrite all other changes.

While there are several ways of handling Concurrency Violations, and the above approach is one of them, I want the MyStore application to follow these business rules:

1. If User B saves a record before User A, merge the two records and save it to the database
2. If User B deletes a record before User A can save it, give User A the option to re-write the record back to the database or delete it
3. If the DataSet becomes out of sync with the database, halt all processing and re-synchronize the Table Adapters

Why?

I am writing this tutorial because I do not fully understand all of the issues involved with Concurrency Violations. I have developed a subroutine that provides Optimistic Concurrency Control for any TableAdapter.Update method that creates a DBConcurrencyException, and I am sharing it with the world – knowing there are probably better solutions. I am hoping for feedback.

Initial Steps

Before inserting the new subroutine, the MyStore database application needs to be modified, beginning with the database itself.

1. In order to detect Concurrency Violations, the tables in the database need a TIMESTAMP column.

   The following code creates the database and tables with a TIMESTAMP column, as well as putting in a few stores and employees for practice. You can copy and paste the code into the MySQL Query Browser and execute it (assuming the Query Browser is properly setup):

   use mysql;
   drop database if exists mystore;
   create database mystore;
   use mystore;

   create table store
   (
   storeID int unsigned auto_increment primary key,
   city char(50),
   state char(2),
   manager char(50),
   concurrency timestamp
   );

   create table employee
   (
   employeeID int unsigned auto_increment primary key,
   storeID int unsigned not null references store(storeID),
   lastname char(50),
   firstname char(50),
   concurrency timestamp
   );

   insert into store values
   (default,'San Diego','CA','Russell Smith', default),
   (default, 'St. Louis', 'MO','Jim Marks', default);
   
   insert into employee values
   (default,1,'Jones','Bill', default),
   (default,1,'Collins','Howard', default),
   (default,2,'Johnson','Cindy', default),
   (default,2,'Gonzales','Hector', default),
   (default,2,'Hanson','Danny', default);

   

Update the DataSet

The DataSet in the MyStore solution needs to be updated with this new column information.

1. Open the MyStore solution, right-click the dataset in the Solution Explorer, and select View Designer.

   

2. Right-click the employee Table and select Configure…

   

3. There is actually nothing to change here. Select Next > twice then Finish.

   

4. The new concurrency row is added to the employee DataTable.

   

5. Perform the same steps for the store DataTable.

   

Update the TableAdapters

The DataTables have been redefined, so the TableAdpaters’ UPDTADE, INSERT, and DELETE methods need to be redefined.
The procedure for doing this is similar to the one in the MyODBC Databinding Tutorial, however, there are a few things to point out:

1. In the Properties window for the storeTableAdpater, select (New) from the InsertCommand combo box.

   

2. Expand the InsertCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).

   

3. Add the store table to the Query Builder by selecting it and selecting the Add button.

   

4. Select the columns to insert by placing a check next to the column names in the store table. You do not need to insert the storeID, as it is the Primary Key and auto-increments. Also, you do not need to insert the concurrency, as it is auto-generated by the database.

   

5. Place question marks into the SQL Statement as place holders for parameters you will pass to this command.

   

6. Select the OK button.
7. In the Properties window for the storeTableAdpater, select (New) from the DeleteCommand combo box.

   

8. Expand the DeleteCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).

   

9. Add the store table to the Query Builder by selecting it and selecting the Add button.

   

10. Add the following line to the end of the SQL Statement:

    WHERE storeID = ? AND concurrency = ?

    

    This allows you to determine which record requires deleting within the database. If it was modified by User B before User A could delete it, a Concurrency Violation is called.

11. Select the OK button.
12. In the Properties window for the storeTableAdpater, select (New) from the UpdateCommand combo box.

    

13. Expand the UpdateCommand selections by selecting the + next to it. Click in the CommandText box and click on the ellipses (…).

    

14. Add the store table to the Query Builder by selecting it and selecting the Add button.

    

15. Select the columns to update from the store table by selecting the check boxes next to them. You do not need to update the storeID, as it is the Primary Key and you do not select concurrency as it is auto-generated by the database.

    

16. In the SQL command, insert question marks to act as placeholders for parameters passed to the update command. In addition, add the following code:

    WHERE storeID = ? AND concurrency = ?

    

    With concurrency in the WHERE clause, a Concurrency Violation is called if the row was updated by User B before User A could save it.

17. Select the OK button.
18. Perform all of the previous steps for the employeeTableAdapter.
    1. The only difference is that employeeID is the Primary Key for the employee table, and needs to be used in the WHERE clause instead of storeID.
    2. The storeID column is a Foriegn Key in the employee table.
    3. The three screenshots below show the final SQL Statements for the employee table.

       

Modify Column Properties

Within the new subroutine I will write, I will be using MySQL statements that directly affect the database. The danger with this is in modifying columns whose values are generated by the database (such as the PRIMARYKEY and TIMESTAMP). In order to avoid errors, we need to declare these columns as Read-Only in the DataSet. We also need to set the AllowDBNull property to True to allow updates to the DataSet with null values.

1. Select the storeID column within the store DataTable.

   

2. Within the Properties window, set the ReadOnly property to True.

   

3. Within the Properties window, set the AllowDBNull property to True.

   

4. Using the above procedure, set the ReadOnly and AllowDBNull properties to True for the store.concurrency, employee.employeeID, and employee.concurrency columns.

   In an effort to avoid the DataSet from loosing sync with the database, we need to make sure the DataTables are not generating identical Primary Keys. We can do this by setting the AutoIncrmentSeed and AutoIncrmentStep properties to -1.

5. In the Properties window for the storeID column, set AutoIncrmentSeed and AutoIncrmentStep values to -1.

   

6. Using the above procedure, set the AutoIncrmentSeed and AutoIncrementStep to -1 for the employeeID column in the employee Data Table.

Modify the Code

Before adding the new subroutine, minor modifications to the code are required.

1. Right-click Form1 in the Solution Explorer and select View Code.

   

   The new subroutine requires a subroutine called FillTableAdapters that uses the TableAdapter.Fill methods to re-synchronize the DataSet with the database.

2. Add the FillTableAdapters subroutine under the Form1_Load subroutine.

   

3. Move the TableAdapter.Fill methods from the Form1_Load subroutine to the FillTableAdapters subroutine.

   

4. Insert a call to the FillTableAdapters subroutine to the Form1_Load subroutine.

   

   The btnSave_Click subroutine needs to catch DBConcurrencyExceptions and call the new subroutine we will add (HandleDBx).

5. In the btnSave_Click event, add a new Catch statement within the Try…Catch block to catch DBConcurrencyExceptions.

   Catch dx As Data.DBConcurrencyException

   

6. Within the new Catch statement, add the following subroutine call:

   Call HandleDBx(dx, My.Settings.mystoreConnectionString)

   

   1. You can ignore the blue line under the HandleDBx call – we haven’t added the subroutine yet.
   2. The dx parameter passes the DBConcurrencyException to the HandleDBx subroutine.
   3. The My.Settings.mystoreConnectionString parameter passes the database connection string to the HandleDBx subroutine.

   We want the TableAdapters to continue updating until all changes have been processed. As it is, one error will stop the procedure.

7. Put the entire Try…Catch block within a Do While…Loop that continues until the DataSet no longer has changes.

   Do While Me.MystoreDataSet.HasChanges

   

8. Finally, move the MsgBox(“Update Successful”) command outside of the Do While…Loop, so it can notify the user when the process is complete.

   

Add the HandleDBx Subroutine

The solution is now ready to accept the HandleDBx subroutine. The subroutine is quite lengthy, and the code (with extensive comments) can be viewed, copied, and pasted from this page.

1. Copy and paste the HandleDBx subroutine into the MyStore solution.

   

2. Since I filled the code with my comments, I won’t go into a description of the subroutine here.
3. Press F5 to Build and Run the application.
4. Concurrency Violations can be tested by opening the MySQL Query Browser to simulate User B. Perform Edits and Apply Changes to the back-end database, then change records within the MyStore application and try to Save.
5. Try to break it. Hope you don’t.

Summary

With this Tutorial, I have tried to design a one-size-fits-all Concurrency Violation solution I can throw into any of my VB.NET-MySQL-MyODBC applications without modification. I think I have succeeded, and I hope others find this helpful.