none
handle data concurrency in 1:n relation RRS feed

  • Question

  • Hi,
    I have two tables:
    Tbl_Categories
    {
    Cat_Name as nvarchar(50) (Pk)
    TimeSpam as datetime
    }

    Tbl_Products
    {
    Pro_Name as nvarchar(50) (PK)
    Cat_Name as nvarchar(50) (FK)
    TimeSpam as datetime
    }

    in the Tbl_Categories the Cat_Name is a primary key and the option on delete and on update is : cascade

    in the Tbl_Products the Cat_Name is a foreign key

    suppose that 2 users are connected to the database via Window application:
    user 1 is modifiying the Cat_Name in Tbl_Categories
    user 2 is modifiying Pro_Name (related to the Cat_name that user 1 attempt to modified)in the Tbl_Products

     

    when user 1 press update =>
    1- Cat_Name and  the time in Tbl_Categories are modified
    2- all the Cat_Name ( to the Cat_Name in question ) in the Tbl_Products are modified (due to on the update cascade options) without the time , so the question is, when user 2 press update how, to handle the data concurrency in this case ?



    Sunday, July 19, 2009 8:44 PM

Answers

  • I think you should use standard patterns for concurrency. Perhaps reading this article will help more. Again I don't think concurrency really occurs on the Client, it occurs on update to the server.
    http://msdn.microsoft.com/en-us/magazine/cc163924.aspx

    I don't think you would want to use a TimeSpan as well, perhaps you mean DateTime?

    Thanks
    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 22, 2009 7:05 PM
  • 10x a lot for the link it help me a lot
    and yes i meant DateTime

    beside the solution is

    to add where update tbl_products
    set quantity = @quantity
    where datetime = @datetime
    and Cat_Name = @Cat_prod
    and  Pro_Name = @Pro_Name

    so if @@rowcount =0 ==> data concurrency.

    10x in advance
    Thursday, July 23, 2009 8:17 PM

All replies

  • Strange !!!!

    no answer till now??
    if it is a studied question someone tell me...
    Monday, July 20, 2009 7:09 PM
  • There is an article on handling concurrency tokens here, take a look at it:
    http://msdn.microsoft.com/en-us/magazine/cc188748.aspx

    I'm not exactly sure what your question is asking. Typically concurrency is implemented on the server side. So when you send the updates from the second user typically this is when you would get a concurrency exception as outlined in the article above. 

    What you would need to do is fill in the changes in the DataSet or perhaps you can signal to the user that the data is not fresh so they need to hit a refresh, refresh would then get the latest data. There are options when you fill to preserve the changes in your DataSet or overwrite the changes. Based on your scenario you will need to determine the best way to fill the DataSet. Then the user would send the updates again and it would proceed. Let me know if this makes sense to you.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 21, 2009 4:18 PM
  • my point of view is
    i am trying to handle data concurrency by using the field TimeSpa so each time a user attempt to modify a row my query check if this row was not modified

    suppose that i have the quantity field in Tbl_Products

    the query to update Tbl_Products  is:

    declare @CurrentTime as datetime
    declare @Result as int
    begin trans
    set @CurrentTime = (select TimeSpam from Tbl_Products where Pro_Name = @Pro_Name)
    if @currentTime <> @myTime
       set @Result = -1
    else
      begin
      set @result = 0
      update Tbl_Products set TimeSpam = getdate(), Quantity = @Quantity where Pro_Name = @Pro_Name
      end
    commit
    return @result
      

    but if a user attempt to change the Cat_Name in Tbl_Categories so all the row in Tbl_Products will be updated TimeSpam ( on update CASCADE)  without the field

    is there is any way to warn the user that it is not a frech copy?

    hope that i was clear

    Wednesday, July 22, 2009 5:48 PM
  • I think you should use standard patterns for concurrency. Perhaps reading this article will help more. Again I don't think concurrency really occurs on the Client, it occurs on update to the server.
    http://msdn.microsoft.com/en-us/magazine/cc163924.aspx

    I don't think you would want to use a TimeSpan as well, perhaps you mean DateTime?

    Thanks
    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 22, 2009 7:05 PM
  • 10x a lot for the link it help me a lot
    and yes i meant DateTime

    beside the solution is

    to add where update tbl_products
    set quantity = @quantity
    where datetime = @datetime
    and Cat_Name = @Cat_prod
    and  Pro_Name = @Pro_Name

    so if @@rowcount =0 ==> data concurrency.

    10x in advance
    Thursday, July 23, 2009 8:17 PM