Multiple Users Read/Write to same SQL record

Byrd, Frank 0 Reputation points
2024-07-11T20:52:39.55+00:00

Hello,

I have a pretty simple database that is read and written to through an asp.net c# website. Users see a grid with data and they can open a record to make changes. More than one user can open that same record, and what I am finding out is that when user2 open the records and makes changes and hits saves the data saves however user1 makes changes after user2 and hits save the data does not save. Currently I have a "lock" to show its open by someone else and prevents them from opening the record, but I am trying to find out if there is a way to allow multiple users to open that same record and write to it no matter what position they have opened it in. I understand that user1,and user 2 wont see updates from user3 but I don't want user1 and 2 to lose what they attempted to save.

I have read about Optimistic concurrency which will require user1 to refresh before making changes, but if they refresh they would lose all information typed into the record. User's image

In the image someone has their data in there, if they hit refresh it removes the data they entered. I need a way for the data they entered to save and actually be written to the sql record despite someone else writing to it first.

Example

User1 Open John Doe record at 4:01pm

User2 Opens same John Doe record at 4:05pm

User2 saves changes to record at 4:07pm and those changes take

User1 saves record at 4:10pm and the website says it saves as no error is returned but it doesn't actually save.

Thanks,

Frank

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,627 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 109.6K Reputation points MVP
    2024-07-11T21:50:00.5966667+00:00

    To me this sounds like an application-side problem. For each editable field, you need to track three values:

    1. The value from the most recent read prior to the refresh.
    2. The current value the user has entered.
    3. The value after the refresh.

    If 1 and 3 are the same 2 is something else, just show the value the user entered.

    Likewise if 1 and 2 are the same (i.e. user did not edit the field) and 3 is different, show the value after the refresh.

    But if 1 and 3 are different, you need inform the user that he or she is making a change based on an old value. You should make the new value somehow available to the user, but exactly how that UI experience would be, I don't know on the top of my head.

    I don't know how much application frameworks will help you with this, but it sounds ambitious to me. Anyway, further questions about this idea, is not really in the scope for the SQL Server tag.

    0 comments
  2. MikeyQiaoMSFT-0444 2,550 Reputation points
    2024-07-15T09:13:19.74+00:00

    Byrd, Frank

    What you are concerned with is, in the case of optimistic concurrency, completely writing the content input by User 1 to the database when a conflict occurs.

    Directly writing the user's data to the database after detecting a concurrency conflict can still be considered a variant of optimistic concurrency control, but this approach is more like a forced overwrite.

    It differs from traditional optimistic concurrency control, which rolls back and prompts the user to re-enter data when a conflict is detected.

    1. Read Data and Version Number: When the user reads data, the current version number is also fetched.
    2. User Input Data: The user modifies the data on the client side.
    3. Detect Conflict When Submitting Data: When submitting data, the server checks the version number to detect conflicts.
    4. Handle Conflicts: If there is no conflict, save the data normally. If there is a conflict, forcefully overwrite with the new data and update the version number.

    However, you can also consider temporarily storing the user's input on the client or server side before submission, even in case of concurrent modifications. You can use the browser's local storage or server-side storage to temporarily save the user's input. When a concurrent conflict is detected, you can prompt the user to reload the data and refill the form with the previously entered data.

    The following is a conflict resolution method refered to the official documentation：

    https://video2.skills-academy.com/en-us/aspnet/web-forms/overview/data-access/editing-inserting-and-deleting-data/implementing-optimistic-concurrency-cs?source=recommendations#handling-concurrency-violations-when-updating

    Best regards,

    Mikey Qiao

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.