Geeks With Blogs

News


Dylan Smith ALM / Architecture / TFS

A while ago I had to deal with the issue of concurrency in our application.  We are developing an in-house ERP solution here (well not all parts of an ERP, but specifically the shop floor control and production scheduling portions right now).  We have alot of different pieces of data and associated maintenance forms.  We wanted to address the problem where if person A starts editing a record, then person B starts editing the same record, person B saves their changes, then person A saves their changes.  In this scenario person B's changes will get overwritten, and nobody will even realize.

If we were using Datasets (which we're not) to do our data access/transport, the dataset has concurrency mechanisms built in.  It stores the old values and the new values.  Then when it comes time to persist the new values to the database it does a check to ensure that the value in the database match the old values in the dataset (ie. they haven't been changed since the dataset was populated), if so the dataset will successfully persist the new values.  If the database values do not match the old values in the dataset an error will be thrown.

We don't use Datasets for a number of reasons, which I won't go into here.  We could use a similar approach to datasets and store old value and new value in our business objects, but that seems like alot of complexity and overhead that could be avoided.  Instead what we did is added a RowID column to all our tables of type uniqueidentier (GUID).  In the future we may actually refactor our database schema and use this RowID as the primary keys for all tables, this would allow us to write alot of generic helper functions if we could guarantee every table had a primary key with the same # of fields, field type, and field name. Right now this RowID is in addition to the natural primary key of the tables.  We then have a table called RowVersions which has a RowID field as it's primary key.  The RowVersions table is linked to all the other tables, with the RowID in the other tables being a foreign key linking them to the RowVersions table.  The RowVersions table contains the fields RowID (primary key), Version, TableName, and CreationDate.  The CreationDate and Version fields are datestamps, and represent the date the record associated with this RowID was created and last modified respectively.  The TableName is just a varchar field indicating what table the related record resides in.

In our code we created a RowVersion class that contains properties for the RowID (GUID) and RowVersion (DateTime).  Every Business Object we define inherits from BusinessObjectBase superclass.  The BusinessObjectBase class has a property of type RowVersion.  When our business tier hydrates the business objects from the database it populates this RowVersion object with the appropriate data.  We have a helper class that encapsulates the concurrency logic into 2 methods: DeleteWithConcurrency(), UpdateWithConcurrency().  These functions accept the sproc name, an array of SqlParameters, and a RowVersion object. They look up the the RowID given in the RowVersions table and check if the Version value in the database matches the RowVersion property on the object.  If they match then the update or delete is executed as normal, if not an exception is raised.  The RowVersion table lookup and the update/delete are both contained in the same transaction with isolation level set to RepeatableRead to avoid a rare race condition.

There is also a wrapper function for InsertWithConcurrency() that encapsulates the logic of creating the record in the RowVersion table then inserting your record in the appropriate table using the same RowID.

The advantage of this approach is you aren't stuck with the overhead of having to track all the old values and new values for each of the properties in the objects.  This functionality can easily be factored out into a base class, but there is still the extra bloat in the network traffic required to pass around twice the amount of data.  In this approach only a GUID and a timestamp are required to be passed around in addition to the regular data.  There is also the flexibility to perform updates without causing other in-progress edits to fail (perform an update without updating the RowVersion); although I'm not sure how useful that would be.

Some of the issues I can see with our approach, is if our business object model doesn't match our database schema we may run into issues. What if a single business object is persisted into multiple database tables, it may need to keep track of multiple row versions.  Or if multiple business objects are all persisted to the same record, then which one should be responsible for tracking the RowVersion?  We have some cases now where our business object model differs from our database schema.  In most of these cases its a single business object that is persisted / built from multiple database tables.  In all the scenario's we've had to deal with so far we haven't required concurrency checks in the additional tables, just the primary table associated with that business object, so it hasn't been a problem to date.

 

What do you guys think?  Is this an awkward solution?  Is there a better way to achieve my goals?  My gut tells me that there is a better way to handle this problem.  Let me know what you think in the comments.

 

 

Posted on Thursday, June 29, 2006 12:36 PM | Back to top


Comments on this post: Concurrency Solution

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Dylan Smith | Powered by: GeeksWithBlogs.net