[Solved] DB first db concurrency handling

[Tomaž]

Hi,

I use OpenAccess in my asp mvc application. I decided for db first approach.

I need some help at handling db concurrency. I use MS SQL as DB server and all tables where concurrency has to be maintained have TimeStamp column.

I have produced a sample service named FundService with Update method:

 

01.public int Update(FundModel p_Fund)
02.{
03.  using (EntitiesModel db = new EntitiesModel())
04.  {
05.    var fund = db.Funds.First(f => f.FundID == p_Fund.ID);
06.
07.    fund.Name = p_Fund.Name;
08.    fund.TimeStamp = p_Fund.TimeStamp;
09.
10.    try
11.    {
12.      db.SaveChanges();
13.      return p_Fund.ParentID;
14.    }
15.    catch (Telerik.OpenAccess.Exceptions.OptimisticVerificationException ex)
16.    {
17.      throw ex;
18.    }
19.  }
20.}

 

The problem is that OptimisticVerificationException never occures.

I open two sessions and edit the same record(fund) in the Fund form (The record in both sessions are loaded first). On Save(Fund form is posted) the Update method is called.

I expect the second update fails because the view model has not been refreshed and second record is posted with old value of TimeStamp.

The problem is that before update the record has to be refreshed from DB (line 05). The existing TimeStamp value is overriden with the value from view model (line 08) but in where clause SQL update original value of TimeStamp is sent to DB.

I would ask you for proper pattern of db concurrency handling in code first approach.

01.public int Update(FundModel p_Fund)

02.{

03.  using (EntitiesModel db = new EntitiesModel())

04.  {

05.    var fund = db.Funds.First(f => f.FundID == p_Fund.ID);

06. 

07.    fund.Name = p_Fund.Name;

08.    fund.TimeStamp = p_Fund.TimeStamp;

09.     

10.    try

11.    {

12.      db.SaveChanges();

13.      return p_Fund.ParentID;

14.    }

15.    catch (Telerik.OpenAccess.Exceptions.OptimisticVerificationException ex)

16.    {

17.      throw ex;

18.    }

19.  }

20.}

[Boyan]

Hello Tomaž,

As you already noted, on line 05. in the Update method, a Fund is retrieved from the database with a brand new context. This means the object that was just received has in fact the latest versions of all fields. So never mind that in the grid the Fund has a name with value of A, when you retrieve it (at line 05) its name will have a value of B(if in fact an update has changed it after the grid has been populated).

Is the FundModel p_Fund (the Update method parameter) a persistent object (a class generated by Telerik Data Access) or is it easily convertible to one? If it is, given that you have chosen to use short living context, you could use the attach/detach object functionality that Telerik Data Access provides. You could find about more about it from this documentation article. If this is not a persistent object but more like a DTO and you could construct an persistent object (if enough information is contained in the DTO object) the attach/detach approach is easily applicable as well. In all cases the very least piece of information you need in the Update method is the Timestamp value corresponding to the object that is to be updated (the original value at the time it was retrieved).

Let me summarize the general approach. The Update method from the code snipped you provided receives a Fund object as a parameter. Lets say that this object is a persistent object (or was converted to one). It could be retrieved from a database context or one could be just instantiated and initialized before the method call. This object will have all its original values as they were at the time it was received like its Name, Timestamp properties and so on. Once this object is passed to the Update method (I expect some time has passed after the object was retrieved for the first time) a new context should be initialized. Then you should attach that object to the context. At this point you could apply the property updates.

Please note that an object could be attached to just one context. If the p_Fund is attached to another context, you need to detach it from that context before re-attaching it to the Update method context.

Following that approach, when the .SaveChanges is finally called in the Update method, it will result in an OptimisticVerificationException.

The reason for that is that Telerik Data Access uses a concurrency mode called Changed. Basically, when an object is updated through a context, the values in the updated row in the database will be compared with the original values of the properties of the updated object. If there is any mismatch, an OptimisticVerificationException will be thrown. In your case, the Timestamp property will be different and so an exception will be thrown as well.
Telerik Data Access also provides a concurrency mode based on a Timestamp column. Using this mode you will not need to set the time stamp property manually with each update, but you could let Telerik Data Access to handle that you. For more information on how to use such concurrency mode please refer to this documentation article.

Once an OptimisticVerificationException is thrown you will need to handle it in some fashion dictated by your implementation requirements. From this documentation article you could learn a bit how Telerik Data Access can help you handle that particular situation as well.

If FundModel is not a persistent object but a DTO and not enough information is contained in that DTO to re-create the original Telerik Data Access object you have to implement that logic manually. At this point at least the timestamp of the object to be updates is required. Once you retrieve that object by its ID you will need to compare the original timestamp value with the one coming from the database. In case of inconsistency, this may indicate that someone else has updated that row in the meanwhile so the corresponding measures has to be taken.

I hope this is helpful. Should you have any more question or need any further assistance do not hesitate to get back us.

 

Regards,
Boyan
Telerik