how to handle concurrency in EF4(ORACLE)

Question

I would like to know how to safely update data when programming with Entity Framework 4(ODAC).

void DescreaseInventory(int id, int qty){
  var order = (from o in context.Orders where o.ID ==id select o).FirstOrDefault;
  if( order != null ){
    if( ((Order)order).Qty < qty )
      throw new ApplicationException( "Not Enough Inventory!!" );
    else
      ((Order)order).Qty -= qty;
  }
  else{
  //...some code...
  }
  //will content.savechange
}

This code will be dangerous(evade the qty checking) once race condition happens. Who knows how to do this correctly?

EDIT: Now I know EF4 provide a mechanism to make a column as a tracking token. But I'm not sure how can I create this kind of column in oracle DB(9i)? what's the proper column type?

score 1 · Accepted Answer · answered Jun 14 '12 at 08:31

1

By using optimistic concurrency = either rowversion or timestamp column in the database. Those columns are maintained by database and it automatically change their value when record is updated. If you correctly configures your EF model to use such column for concurrency checking you will avoid some problems.

When one process takes your order it will load its current timestamp and when it tries to save the record the timestamp will be part of the Where condition for update. If the order was changes by another process in the mean time it will not find a record to update and throws exception. You will have to handle such exception by refreshing data from database to get actual state and actual timestamp and for example by recomputing qty or passing it for resolution to user.

answered Jun 14 '12 at 08:31

Ladislav Mrnka

360,892
59
660
670

Thanks Ladislav.how about the following steps: define a trigger in DB, the trigger wiil update a timestamp column once any change happens in the row. set the **concurrency mode** property of the corresponding field of Entity class to **Fixed**. – user622851 Jun 14 '12 at 09:00
You don't need trigger if you use types I mentioned. DB will do that automatically. – Ladislav Mrnka Jun 14 '12 at 10:04
Hi, Now I know EF4 provide a mechanism to make a column as a trancking token(thanks Ladislav and google,also msdn). But i'm not sure how can i create this kind of column in **oracle DB(9i)**? what's the proper column type?Thanks. – user622851 Jun 15 '12 at 09:02
That is quite important detail you forgot to mention in your original question and in tags for your question. I have never used EF with Oracle database ... – Ladislav Mrnka Jun 15 '12 at 09:18
My fault. Now update the tag and title for accurracy. Thanks. – user622851 Jun 15 '12 at 09:49

how to handle concurrency in EF4(ORACLE)

1 Answers1