Follow me on Twitter

NOTE: the concepts presented in this article are now considered obsolete possibly because better alternatives are available.

Update!! - I have written a new series on N-Layer design targeting ASP.NET 4.5 and Entity Framework 5. You can check out the new series here.

Note: this is part five in a series of six. If you rather read this entire series off-line, you can buy the series as a convenient PDF document that comes with the full source. Besides the convenience, buying the PDF will also make you feel good as it shows your appreciation for the articles and helps me pay the bills for my server and hosting so I can keep running imar.spaanjaars.com and continue to provide you with great content. For more details, check out this post that shows you how you can buy the entire series right now.

This is part five of a six-part series of articles on N-Layer design. This article series builds on top of my three part series on N-Layer design that I released in early 2007. If you haven’t already done so, be sure to check out these articles first, as a lot of code and concepts used in this new series is explained in detail in the older series.

In this installment, I’ll discuss techniques for dealing with concurrency — I'll show you how to handle data conflicts caused by updates from other users.

After you’ve read the previous series, be sure to check out part one as well as it describes the new application’s architecture. Additionally, you may want to read the earlier parts that deal with Validation, Sorting, Paging and Filtering.

Many web sites that I have seen (and built) don’t do much to handle concurrency issues. That is, they don’t provide support for situations where two users try to update the same data at the same time. Depending on the use of the application and number of users your site has, this may or may not be a problem. In case you’re not aware of concurrency issues, consider the following example:

User Tom gets a list of addresses for a specific Contact Person. He clicks Edit on one of the addresses to put it in Edit mode.

User Jacqueline gets the same address record, and puts it in Edit mode as well.

Tom changes the Zip code of the address and saves the changes.

Jacqueline changes the city and saves her changes as well.

Because in step two Jacqueline got a local copy of the address record, stored in the form’s edit fields, she effectively overwrites the changes made by Tom as soon as she updates the record in step 4. That is, the Edit form still contained the old zip code she got from the database. When she saved the form, she sent the old zip code, completely ignoring the change made by Tom.

So how do you handle these situations? In some cases, it’s acceptable to simply ignore it. On a low-traffic site, or in an application where it’s unlikely that two users try to modify the same record, the risk of failure and the costs associated with it are lower than the efforts it takes to implement concurrency checks. However in other cases, it’s important to have a good concurrency strategy.

In the following sections, I’ll detail the changes you need to make to implement concurrency checks at different levels of the application, including:

The database – including changes to tables and stored procedures

The BusinessEntities, Bll and Dal namespaces – including changes to the BusinessBase and the various *DB classes.

The User Interface – which must now know how to deal with concurrency exceptions.

Handling Concurrency at the Database Level

To implement concurrency handling in your database, you have a few options. One common technique is to keep track of the date a row was last modified. When you update a row, you pass in the date the row was last modified and use it in the WHERE clause. If the datetime field matches, you know the row hasn’t been modified. But if no rows were updated, you know your WHERE clause didn’t match as someone else has updated the row in the mean time. Take a look at the following T-SQL statement to see how this would work:

While easy to implement, this solution has one big drawback: it only works for updates you explicitly make through T-SQL code. So if you make a change using something like SQL Server Management Studio or through SQL statements that don’t take this mechanism into account, the UpdateDateTime won’t be updated and your concurrency check might fail. (Note: you could work around this using triggers that update the UpdateDateTime after a row has been updated)

If you’re using SQL Server, there’s a much easier solution available: using a timestamp column (other database systems have similar functionality). Unlike its name seems to suggest, a timestamp doesn’t really have anything to do with the time. It’s a unique version number for a row that is updated automatically by SQL Server every time you make a change to a row. To avoid the confusion, the timestamp column is now called rowversion in SQL Server 2005 and later so you’re advised to use that instead in your T-SQL scripts. Whenever you need to update a row from within the Data Access Layer, you pass in the value of this timestamp column and then use it in the WHERE clause. In the sample application, I gave each table in the database a timestamp column and called it ConcurrencyId. Stored procedures that update a row in the database then take this column into account as follows:

This procedure has a parameter called @concurrencyId which contains the rowversion which was retrieved when the row was initially loaded (using GetList or GetItem methods). This value is then used in the WHERE clause for the UPDATE statement. In the end, the @concurrencyId parameter is filled with the rowversion for the row. When the update was applied successfully, this means that @concurrencyId now contains a new version number. When the update didn’t succeed, nothing was updated and @concurrency will contain the row version that the row had before the update attempt.

Handling Concurrency in the Bll and Dal Namespaces

Now that you have seen a brief overview of handling concurrency at the database level, it’s time to take a look at the implementation of it in the Contact Manager Application used in this article series.

The implementation of the concurrency model in the Contact Manager Application affects a number of classes in the BusinessEntities and Dal namespaces, including:

BusinessBase in the BusinessEntities namespace.

The individual data access classes in the Dal namespace.

The following sections look at each of these changes in detail.

Changing the BusinessBase Class

The BusinessBase class has been extended with a ConcurrencyId property of type byte[] (the .NET counterpart of the rowversion in SQL Server):

Since all main business entities in the BusinessEnities namespace inherit from BusinessBase, they all get this new property for free.

Changing the Individual DAL Classes

The next step is loading an existing concurrency ID in a business entity when it’s constructed from the database data. Each SELECT stored procedure (the SelectSingleItem and SelectList versions) now includes the ConcurrencyId column in the SELECT statement which is then loaded in FillDataRecord in the corresponding DAL classes. For example, FillDataRecord in EmailAddressDB now looks like this:

Now that the data is loaded, the next important step is to pass it to the database when the business entity is updated. This is done in the new SetSaveParameters in the database Helpers class that sets up parameters shared by all business entities:

This method is called by all business entities that implement concurrency checks. Therefore, the method accepts a BusinessBase instance so it can be reused across all entities.

Another change I made in the Save methods of the business entities is the way I check the return value of ExecuteNonQuery. Previously, it simply ignored this value, but it’s now used to determine whether the update succeeded or not:

When numberOfRecordsAffected is zero, it means the stored procedure didn’t update any rows because the rowversion or the Id passed in didn’t match the existing one stored in the database. In that case, the code throws a DBConcurrencyException which can be handled in the UI.

Now that you’ve seen the require changes in the various business and Data Access Layers, the next thing you need to look at is the way concurrency is dealt with in the Presentation Layer.

Handling Concurrency in the UI of the Contact Manager Application

The first step in handling the concurrency problem in the UI is catching the exception that might be thrown by the DAL classes. The AddEditContactPerson.aspx page has the following code that gets executed when you try to save a contact person:

All you need to do is get the latest version of the contact person from the database and store its ConcurrencyId in ViewState so it’s available in the SaveContactPerson method. SaveContactPerson carries out the necessary validation as usual and then calls a helper method that populates a ContactPerson from control- and ViewState like this:

When the user clicks the Overwrite button, the ConcurrencyId of the *new* record in the database is stored in ViewState. Consequently, this ID is then used to reconstruct the ContactPerson before it’s saved. The database then sees that the record it must save matches the latest version in the ContactPerson table and happily applies the update.

This code gets a fresh ContactPerson from the database and then repopulates the form controls with the latest data. After that, the user can make any changes she sees fit and then update the record in the database by clicking the standard Save button.

To simulate a concurrency problem and see how this works in a real-world example page, carry out the following steps:

Browse to the Contact Manager Application, If you have the solution open in Visual Studio / Visual Web Developer, make sure the web site is the startup project and hit F5

In the list of contacts, click Edit to edit an existing contact person. Don’t make any changes to the contact yet, and don’t save the record.

Switch back to Visual Studio, open the ContactPerson table in the database using the Database Explorer / Server Explorer window and then make a change to the person you’re editing in the web environment. For example, change the person’s first name.

Go back to the web page and click the Save link. You’ll now see the two options appear; allowing you chose what to do with the data.

Because the AddEditContactPerson.aspx page is all hand coded, this concurrency behavior is pretty easy to implement. You can, however, apply the same principles to the lists of contact data like addresses and phone numbers. The Default.aspx page demonstrates this behavior for the Addresses collections. Note that I didn’t implement it for the other two collections; instead you’ll get a Yellow Screen of Death error when a concurrency conflict occurs. I won’t go into the code in full details, but this is globally how the page works:

Right below the FormView that allows you to enter new addresses I added a PlaceHolder control similar to the one in the AddEditContactPerson.aspx page.

In gvAddresses_RowUpdating (that is fired before the actual item in the GridView is updated) I check if ViewState contains a value for the AddressConcurrenceId key. If it does, it means a concurrency occurred earlier and the user decided to overwrite the changes in the database. In that case, I assign e.NewValues["ConcurrencyId"] the value of the concurrency key in ViewState.

In gvAddresses_RowUpdated, that is fired after the record has been updated, I check for a concurrency error with the following code:

This code gets the latest address from the database, stores the value of the ConcurrencyId in ViewState (which is used later in the RowUpdating event explained in step 2) and then manually calls UpdateRow to force an update of the data held in the editable row of the GridView. When the update is done, the code sets EditIndex to -1 to take the GridView out of edit mode. UpdateRow in turn causes RowUpdating to fire which retrieves the concurrency ID from ViewState, assigns it to the Address instance which then uses it to successfully update the data in the database.

With the example I have shown here, it’s pretty easy to implement concurrency in your web application. Yes, it requires some work in the different layers of the application, but in most cases, it will be well worth the cost. Once you get the hang of it, implementing concurrency in your own business entities may be done in just a couple of minutes. Time well spent if it helps you to prevent users from overwriting each other’s changes.

Summary

In this part of the article series on N-Layer design you saw how to deal with concurrency, in order to prevent two users from overwriting each other’s changes.

Dealing with concurrency is often skipped in many real-world applications because many developers either don’t give it enough thought or think it’s too complicated to implement and not worth the cost.

However, with the concepts I have demonstrated in this article, it’s easy to implement concurrency behavior at a low cost and with the smallest impact on the code and its performance.

In the final part of this long running article series I’ll deal with another multi-user issue: security. How can you stop unauthenticated users from entering the system and how can you make sure only authorized users can make changes to your system and its underlying database. Stay tuned.

Downloads

Where to Next?

Wonder where to go next?
You can read existing comments below
or you can post a comment yourself on this article
.

Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.

Feedback by Other Visitors of Imar.Spaanjaars.Com

On Tuesday, February 10, 2009 10:18:29 PM Jeff said:

So if I understand you, in Sql Server they don't call the timestamp filed timestamp any more they call it rowversion correct? And the field still contains the date and time to the milisecond when a change was made right?

On Tuesday, February 10, 2009 10:26:32 PM Imar Spaanjaars said:

Hi jeff,

No, not correct. A RowVersion (or a timestamp) does NOT contain the date and time atall; it simply contains an internal row version number. Quote from the article:

Unlike its name seems to suggest, a timestamp doesn’t really have anything to do with the time.

And yes, it's now called RowVersion (to avoid the name confusion I suppose). But in the table designer it's still called timestamp.

Cheers,

Imar

On Thursday, February 12, 2009 2:52:39 PM Fawwaz Masihuddin said:

Hi Imar,

I have read everything in this series so far and I must say it is very well written. Very nice concepts some of which we were already using in our projects. Thank you for all the hard work and dedication.

Another thing that I was reading somewhere else about was Exception Handling or Exception Management. It will be good if you can include that into this series.

This will give us an idea on what based on your experience is the best and current way of Exception Management and Handling in a N Layered Web Application. What do you say?

Thanks once again.

Fawwaz

On Thursday, February 12, 2009 8:40:09 PM Imar Spaanjaars said:

Hi Fawwaz,

The reason I haven't written a lot on exception handling is because there is already a lot of information about it available on the internet. The common best practices won't be any different in my architecture.

Cheers,

Imar

On Wednesday, March 04, 2009 10:48:56 AM Raj said:

hi,Great articles. Just waiting for the next one in the series(Security). Was expected on 1st Mar and not available yet so hope everything is alright.Regards.

On Wednesday, March 04, 2009 5:37:57 PM Imar Spaanjaars said:

Hi Raj,

Yes, I know. Haven't had the time to post it yet. Hopefully this weekend.

Of course you can always consider buying the PDF version now.

Cheers,

Imar

On Wednesday, March 04, 2009 9:56:26 PM Raj said:

Dear Imar,

I seriously thought of that. I also have sent you email from contact me form to ask if you have any other series coming which I can buy.Oh and by the way by saying "Everything is alright", meant to ask if you are fine and in good health.

Regards.

On Wednesday, March 04, 2009 9:59:13 PM Imar Spaanjaars said:

>> seriously thought of that

And did you do it? ;-)

I replied to your private message a few hours ago....

Cheers,

Imar

On Thursday, March 19, 2009 4:46:18 PM Jeff said:

Imar, In your database code you use the "using(SqlConnection my Connection...)" construct fairly liberally, but I don't think I ever see you use a "MyConnection.Close()" or "MyConnection.Dispose()" way of closing and disposing of the connection and command objects which would in turn trigger the garbage collection for these objects. Do you not need to do this if you create your connections the way you are?

On Thursday, March 19, 2009 5:27:57 PM Imar Spaanjaars said:

Hi Jeff,

Nope, that's not necessary. Calling .Close or Dispose is indeed a good thing, but there's no need (in my case) to do it manually. All my data access is wrapped in a using block like this:

Here you can see the connection gets closed when the connection is disposed of.

So, a simple using block takes care of all this automatically.

Cheers,

Imar

On Thursday, March 19, 2009 5:37:57 PM Jeff said:

Wow. That is just at once totally mind-blowing, and at the same time just too totally cool !! Yeah!

On Tuesday, April 14, 2009 3:48:34 PM daVC said:

Great Article, u are simply the best. But your idea of concurrency management seems too complex. What about just checking in the Stored procedure that updates if the ConcurrencyId = @ConcurrencyId for the row to be update before updating, otherwise return a value to indicate that there is a concurrency conflict .

On Tuesday, April 14, 2009 7:47:22 PM Imar Spaanjaars said:

Hi daVC,

Yes, isn't that exactly what I am doing?

What part is too complex in your opinion and what would you recommend to simplify it?

dear Imar, i'd link to implement concurrency in this way you've explaned, but my database is very complex and i have a lot of tables (that corresponds to a lot of businessobjects), every one has a specific id (as primary key) whith a different name (es. chapterid, bookid, authorid...) the tables have many relations so it would be very complicate to have always the same idname.In every businessObject i have created a corrispondent id property that has the same name of the database field (es tablenameid), now how can i extend the single property Public MustOverride Property Id() As Integer in businessbase to all my businessobjects?must i change every class (tables, stored procedure, methods, controls and so on) or there is a sintax (that i ignore) that can leave me adapt your concepts (using a sigle id properties) to my webapplicationthanks in advance if you can help me and excuse me for newbie questionluisa

On Wednesday, September 02, 2009 1:31:47 PM Imar Spaanjaars said:

Hi luisa,

Concurrency is not related to the name of the ID column in the database.

Anyway, I can see at least two ways to fix the problem:

1. Leave the Id property in the base class and change FillDataRecord to get the correct column name. That way, you have TableNameId in the database and just Id in the object model.

2. Remove Id from the base class and give each entity an ID property that matches the primary key column name in the database.

Hope this helps,

Imar

On Thursday, September 03, 2009 8:23:49 PM luisa said:

Hi Imar, what a speed!!!

thank youbyeluisa

On Thursday, January 28, 2010 12:32:05 AM Mike said:

Love this series of articles. I've used a lot of these concepts in my programming. One thing I'm noticing: the concurrency concept as explained above works brilliantly for me when I edit a record on a custom form and do a LoadFormToObject type of method. But now I'm trying to edit an object directly within a GridView using an objectDataSource and for some reason it doesn't catch concurrent changes. The Helpers class updates the ConcurrencyId every time, but it never throws a concurrency exception. Is there anything in particular to watch out for when using this with a GridView? Any idea what might be causing this?

On Sunday, January 31, 2010 8:03:54 PM Imar Spaanjaars said:

Hi Mike,

What happens when you debug and step through your code? Does the right stuff get called?

Also, try inspecting the Exception properties from event args classes such as GridViewUpdatedEventArgs which are accessible in events such as RowUpdated: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowupdated.aspx

Hope this helps,

Imar

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.

Please correct the following errors:

Your name

Your e-mail address

Comment
(Plain text only; no HTML or code that looks like HTML or XML. In other words, don't use < and >.

Notify me of replies

3
+
8
=

Thank you for your contribution.

Whoooops!!

Unfortunately, something went wrong and your message or comments have not been submitted successfully.

There's a fair chance things broke down because you tried to post something that looks like HTML. Things that look like HTML include (X)HTML, obviously, XML, ASP.NET markup and c# generics syntax as all of them use the < and > characters.

If that's the case, try altering your message and remove anything that looks like an angled bracket. You can replace them with [ and ] for example so you can still make it look like HTML to some extend.

If, on the other hand, you were trying to spam this web site, I am pretty glad I caught you in the act and stopped you from doing so ;-)

The number you entered is not correct. Please enter the sum of the two numbers again.