locked
how to handle multiple users changing same data? RRS feed

  • Question

  • User-30237494 posted

    hi,
    I  have a form which allows users to modify data, what's the best way to handle that so if two or more users try to make an update i get the data in there properly without overriding the new stuff. for example if name and bday are in the form, user1 changes name but not bday, same time user2 accesses the form, the original name and bday are there, user1 saves, but when user2 saves, since he had the original name value the original is saved over the first.

    is there anything build into .net to help with this? is there some property of maybe sqltransaction i use to stop this from happening?


    Wednesday, March 24, 2010 10:32 AM

Answers

  • User-925904572 posted

    You need to write SQL that prevents that.

    There are several methods out there. Google Optimistic Concurrency

     

    You can go fancy and create a que that syncs data or you can use concurrency that prevents record updating on records that have been modified by someone else in the same time that you were changing the same record.

    You have write SQL that catches that then you have to catch the Select event on your DataSource and deal with the failed update there. This is usually done by letting the user know that this has happened and asking them to either reload the form and let them alter it again or to just go ahead and overwrite with your values.

     

    The secret is in the WHERE clause. See below.

     

    Here is what your SQL will look like to prevent this behavior:

        <asp:SqlDataSource ID="ClassRegistrationDS" runat="server" 
            ConnectionString="<%$ ConnectionStrings:myConnectionString %>" 
            SelectCommand="SELECT [classScheduleID], [UserId], [contactTypeID], [note] FROM [exg_ClassContactsLink]"      
            DeleteCommand="DELETE FROM [exg_ClassContactsLink] WHERE [classScheduleID] = @original_classScheduleID AND [UserId] = @original_UserId AND [contactTypeID] = @original_contactTypeID AND (([note] = @original_note) OR ([note] IS NULL AND @original_note IS NULL))" 
            ConflictDetection="CompareAllValues" 
            InsertCommand="INSERT INTO [exg_ClassContactsLink] ([classScheduleID], [UserId], [contactTypeID], [note]) VALUES (@classScheduleID, @UserId, @contactTypeID, @note)" 
            OldValuesParameterFormatString="original_{0}" 
            UpdateCommand="UPDATE [exg_ClassContactsLink] SET [note] = @note WHERE [classScheduleID] = @original_classScheduleID AND [UserId] = @original_UserId AND [contactTypeID] = @original_contactTypeID AND (([note] = @original_note) OR ([note] IS NULL AND @original_note IS NULL))">
        <DeleteParameters>
            <asp:Parameter Name="original_classScheduleID" Type="Object" />
            <asp:Parameter Name="original_UserId" Type="Object" />
            <asp:Parameter Name="original_contactTypeID" Type="Object" />
            <asp:Parameter Name="original_note" Type="String" />
        </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="note" Type="String" />
                <asp:Parameter Name="original_classScheduleID" Type="Object" />
                <asp:Parameter Name="original_UserId" Type="Object" />
                <asp:Parameter Name="original_contactTypeID" Type="Object" />
                <asp:Parameter Name="original_note" Type="String" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="classScheduleID" Type="Object" />
                <asp:Parameter Name="UserId" Type="Object" />
                <asp:Parameter Name="contactTypeID" Type="Object" />
                <asp:Parameter Name="note" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>


     

     Now, depending on the operation (INERT, DELETE, UPDATE) you hadle the DataSource event (Inserted, Deleted, Updated). If the nuber of affected row is not what you expect, you handle the error here.

     

    Let me know if the helped.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 24, 2010 11:05 AM

All replies

  • User-925904572 posted

    You need to write SQL that prevents that.

    There are several methods out there. Google Optimistic Concurrency

     

    You can go fancy and create a que that syncs data or you can use concurrency that prevents record updating on records that have been modified by someone else in the same time that you were changing the same record.

    You have write SQL that catches that then you have to catch the Select event on your DataSource and deal with the failed update there. This is usually done by letting the user know that this has happened and asking them to either reload the form and let them alter it again or to just go ahead and overwrite with your values.

     

    The secret is in the WHERE clause. See below.

     

    Here is what your SQL will look like to prevent this behavior:

        <asp:SqlDataSource ID="ClassRegistrationDS" runat="server" 
            ConnectionString="<%$ ConnectionStrings:myConnectionString %>" 
            SelectCommand="SELECT [classScheduleID], [UserId], [contactTypeID], [note] FROM [exg_ClassContactsLink]"      
            DeleteCommand="DELETE FROM [exg_ClassContactsLink] WHERE [classScheduleID] = @original_classScheduleID AND [UserId] = @original_UserId AND [contactTypeID] = @original_contactTypeID AND (([note] = @original_note) OR ([note] IS NULL AND @original_note IS NULL))" 
            ConflictDetection="CompareAllValues" 
            InsertCommand="INSERT INTO [exg_ClassContactsLink] ([classScheduleID], [UserId], [contactTypeID], [note]) VALUES (@classScheduleID, @UserId, @contactTypeID, @note)" 
            OldValuesParameterFormatString="original_{0}" 
            UpdateCommand="UPDATE [exg_ClassContactsLink] SET [note] = @note WHERE [classScheduleID] = @original_classScheduleID AND [UserId] = @original_UserId AND [contactTypeID] = @original_contactTypeID AND (([note] = @original_note) OR ([note] IS NULL AND @original_note IS NULL))">
        <DeleteParameters>
            <asp:Parameter Name="original_classScheduleID" Type="Object" />
            <asp:Parameter Name="original_UserId" Type="Object" />
            <asp:Parameter Name="original_contactTypeID" Type="Object" />
            <asp:Parameter Name="original_note" Type="String" />
        </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="note" Type="String" />
                <asp:Parameter Name="original_classScheduleID" Type="Object" />
                <asp:Parameter Name="original_UserId" Type="Object" />
                <asp:Parameter Name="original_contactTypeID" Type="Object" />
                <asp:Parameter Name="original_note" Type="String" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="classScheduleID" Type="Object" />
                <asp:Parameter Name="UserId" Type="Object" />
                <asp:Parameter Name="contactTypeID" Type="Object" />
                <asp:Parameter Name="note" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>


     

     Now, depending on the operation (INERT, DELETE, UPDATE) you hadle the DataSource event (Inserted, Deleted, Updated). If the nuber of affected row is not what you expect, you handle the error here.

     

    Let me know if the helped.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 24, 2010 11:05 AM
  • User2130758966 posted

    I'm sure google will fix it but its optimistic concurrency.

    Depending on the data access technology this is implemented in various ways.

    This tutorial explains more about the concept:


    With some technologies like linq to sql or entity framework you get the functionality almost for free - just add an extra column to your database and the orm will handle it all for you. With other technologies such as Strongly Typed Datasets it can be a considerable amount of work writing all the code to double check every variable.

    Wednesday, March 24, 2010 11:12 AM
  • User-55773023 posted

    There seems to be a way, when using data adapters.

    http://www.eggheadcafe.com/articles/20050719.asp

    But I believe the general approach is to use optimistic locking when doing the database transaction, using a check for all columns, modified columns, timestamp or version.


    Wednesday, March 24, 2010 11:16 AM
  • User-37275327 posted

    Hope you are using SQL server. You better think of ROWLOCK,

    try this link

    http://www.sql-server-performance.com/articles/per/lock_contention_nolock_rowlock_p1.aspx

    Wednesday, March 24, 2010 11:17 AM
  • User-1199946673 posted

    it can be a considerable amount of work writing all the code to double check every variable.
     

    When using SQL Server, you could just add a TimeStamp Column, and instead of checking every variable, you just need to check whether the TimeStamp is still the same

    Wednesday, March 24, 2010 11:23 AM
  • User-1199946673 posted

    Hope you are using SQL server. You better think of ROWLOCK,

    try this link

    http://www.sql-server-performance.com/articles/per/lock_contention_nolock_rowlock_p1.aspx

     

    Please explain how you want to implement this in a web environment?

    Wednesday, March 24, 2010 11:29 AM
  • User-1298418992 posted
    We can make use of exclusive lock on the table, this way we can deny change on table at a time
    Or we can use transaction levels
     
    Thanks
    Wednesday, March 24, 2010 12:13 PM
  • User-1199946673 posted

    We can make use of exclusive lock on the table, this way we can deny change on table at a time
     

    And how do you want to implement this, in the scenario presented by pdassnyc?

    Wednesday, March 24, 2010 12:25 PM
  • User308623103 posted

    Hi,

    Can we make use of locks at the data by ourselves? It will be a custom implementation wherein, we will add a new column of UserIdWithLock at the table level. When the user fetches any information, a lock will be applied at the table record. When the user has finished working with the record, the lock will be removed by setting the value at the field to null. 

    When there is no value found at the table row, the record is not locked and can be edited/ updated.

    If a lock exists and any user tries to update the data, a message can be given to indicate that the item can't be modified at this point of time.

    When the user logs out of the system, we can remove the lock manually by setting such flag to null.

    Hope it helps.

    Regards


    Tuesday, April 13, 2010 6:29 AM