none
"RowsAffectedParameter throws OptimisticConcurrencyException if zero is returned" Can't be serious, surely?

Answers

  • Hi Jamie,

    Sorry for the delay.   A very senior expert gave me some suggestions about this problem:

    "This basic design has been in place since ADO.NET.  When you update disconnected data, you might want to know if another user has updated the your data between the time you read it and the time you’re writing it back.   The mechanism we use for that is the OptimisticConcurrencyException.  If another user has changed the data, the update fails with an OptimisticConcurrencyException.  Typically in response you would reload the current data, inform the user that their update failed because it would have overwritten another user’s changes and allow them to modify the current data.

    Now the way we typically detect optimistic concurrency violations is that the update query will be modified so that it affects zero rows if another user has made a conflicting change to the row.  Commonly this will be a query like

    UPDATE T SET A=@A, B=@B
    WHERE ID=@ID and Timestamp=@TS

    So if another user has changed the Timestamp column since we read the row, the update will not affect any rows.  Then from the client you detect optimistic concurrency violations by checking the rows affected by the update statement.  If it affects 1 row, then the update succeeded.  If it affects 0 rows, then an optimistic concurrency violation occurred.  There’s no SqlException here, since the DML simply affected 0 rows, so EF throws an OptimisticConcurrencyException to inform the program that the update failed.

    For stored procedures the records affected mechanism is sometimes tricky, since you have to carefully turn NOCOUNT on and off in the body of the procedure to get the right rowcount.  As an alternative you can mark an output parameter as the “Rows Affected Parameter”, and EF will use this value instead of the row count reported by database to detect optimistic concurrency violations."

    Hope you have a nice day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 10, 2011 6:44 AM
    Moderator

All replies

  •  

    Hi Jamie,

    Thanks for your post.

    Let's see what happens in SaveChanges() through Reflector, The Update method will be called.

    internal static int Update(IEntityStateManager stateManager, IEntityAdapter adapter)
    {
      IntPtr ptr;
      int num3;
      EntityBid.ScopeEnter(out ptr, "<upd.Internal.UpdateTranslator.Update>");
      EntityConnection connection = (EntityConnection) adapter.Connection;
      MetadataWorkspace metadataWorkspace = connection.GetMetadataWorkspace();
      int? commandTimeout = adapter.CommandTimeout;
      try
      {
        UpdateTranslator translator = new UpdateTranslator(stateManager, metadataWorkspace, connection, commandTimeout);
        Dictionary<int, object> identifierValues = new Dictionary<int, object>();
        List<KeyValuePair<PropagatorResult, object>> generatedValues = new List<KeyValuePair<PropagatorResult, object>>();
        IEnumerable<UpdateCommand> enumerable = translator.ProduceCommands();
        UpdateCommand source = null;
        try
        {
          foreach (UpdateCommand command2 in enumerable)
          {
            source = command2;
            <strong><span style="text-decoration:underline">long rowsAffected = command2.Execute(translator, connection, identifierValues, generatedValues);
            translator.ValidateRowsAffected(rowsAffected, source);
    </span></strong>      }
        }
        catch (Exception exception)
        {
          if (RequiresContext(exception))
          {
            throw EntityUtil.Update(Strings.Update_GeneralExecutionException, exception, translator.DetermineStateEntriesFromSource(source));
          }
          throw;
        }
        translator.BackPropagateServerGen(generatedValues);
        num3 = translator.AcceptChanges(adapter);
      }
      finally
      {
        EntityBid.ScopeLeave(ref ptr);
      }
      return num3;
    }
    
    

    Now we look into ValidateRowsAffected method

    private void ValidateRowsAffected(long rowsAffected, UpdateCommand source)
    {
      if (0L == rowsAffected)
      {
        IEnumerable<IEntityStateEntry> stateEntries = this.DetermineStateEntriesFromSource(source);
        throw EntityUtil.UpdateConcurrency(rowsAffected, null, stateEntries);
      }
    }
    
    

    You can see here if rowsAffected==0, the exception will throw.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 26, 2011 6:12 AM
    Moderator
  • Hi Jamie,

     I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 28, 2011 1:45 AM
    Moderator
  • Hello Alan,

    What suggestion? You confirmed that the documentation is correct but that didn't answer my question which is "why does it behave this way?"

     

    Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Friday, January 28, 2011 6:58 AM
  • Hi Jamie,

    Sorry for the delay.   A very senior expert gave me some suggestions about this problem:

    "This basic design has been in place since ADO.NET.  When you update disconnected data, you might want to know if another user has updated the your data between the time you read it and the time you’re writing it back.   The mechanism we use for that is the OptimisticConcurrencyException.  If another user has changed the data, the update fails with an OptimisticConcurrencyException.  Typically in response you would reload the current data, inform the user that their update failed because it would have overwritten another user’s changes and allow them to modify the current data.

    Now the way we typically detect optimistic concurrency violations is that the update query will be modified so that it affects zero rows if another user has made a conflicting change to the row.  Commonly this will be a query like

    UPDATE T SET A=@A, B=@B
    WHERE ID=@ID and Timestamp=@TS

    So if another user has changed the Timestamp column since we read the row, the update will not affect any rows.  Then from the client you detect optimistic concurrency violations by checking the rows affected by the update statement.  If it affects 1 row, then the update succeeded.  If it affects 0 rows, then an optimistic concurrency violation occurred.  There’s no SqlException here, since the DML simply affected 0 rows, so EF throws an OptimisticConcurrencyException to inform the program that the update failed.

    For stored procedures the records affected mechanism is sometimes tricky, since you have to carefully turn NOCOUNT on and off in the body of the procedure to get the right rowcount.  As an alternative you can mark an output parameter as the “Rows Affected Parameter”, and EF will use this value instead of the row count reported by database to detect optimistic concurrency violations."

    Hope you have a nice day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 10, 2011 6:44 AM
    Moderator
  • Hello,

    Its taken me a long time to reply but I want to thank you for posting such a detailed response - clearly optimistic concurrency is something I need to learn more about.

    Thanks again - Microsoft have gone up in my estimation a tiny bit today.

     

    Regards
    Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, March 14, 2011 11:25 PM
  • Hello,

    Its taken me a long time to reply but I want to thank you for posting such a detailed response - clearly optimistic concurrency is something I need to learn more about.

    Thanks again - Microsoft have gone up in my estimation a tiny bit today.

     

    Regards
    Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    It's great to hear you say so, Jamie.  Even it's "a tiny bit". :)   Please believe we Microsoft are always doing the best! 

    Hope you have nice day!

    Thanks



    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Tuesday, March 15, 2011 1:13 AM
    Moderator