CODEWITHSUNDEEP
c#sql
Zach Allen
Zach Allen

Reputation: 192

How do I ensure that users don't update over each other's data?

Assume we have a form where there are several text boxes and a button that saves the data to a database record when we click it.

Let's say that User 1 opens the form, and around the same time, User 2 opens up the same form. User 1 changes the values in the email text box and saves the form while User 2 still has the form with--what is now--the old email. User 2 changes the values in the phone number text box and saves.

Since the submit button's functionality is driven by a big update statement that updates all fields in the form to the record in the database, when User 1 goes back to the record, from their experience, there email was not saved to the database since User 2's submission updated over all fields in the record with whatever was in User 2's form at the time.

At the company that I work for, users have reported instances where their saved data magically disappears when multiple users are updating fields in the same records, and I am starting to suspect that this is the cause.

Can someone point me in the right direction in terms of dealing with this issue? I have had difficulty finding any articles/resources that discuss these issues.

Thanks

Upvotes: 0

Views: 530

Answers (2)

Matty2
Matty2

Reputation: 55

You can make it in a way that there is a form for each user. When the user clicks on Save. The data is sent all at once. To handle the problem of data overwriting from the collision, you can make it so that the the form has to tell the back-end to be ready while making it unavailable for other users. Until the data is sent, the back-end will then be ready for new data.

For example when user1 is sending form data to the backend, the backend will ignore all ready messages from user2 until data is sent and user2 attempts to send the ready message again. If the backend receives 2 or more ready messages at the same time, it will choose to reply the ok message to one user randomly and begin the data transfer with that user.

Upvotes: 1

Aasmund Eldhuset
Aasmund Eldhuset

Reputation: 37950

A common way to solve your problem is called optimistic concurrency. The basic idea is that each table contains an extra column that is either a timestamp or a version number. When you read from the database, you keep track of the version number, and when updating the database, you check if the version number in the database is the same as when you read that row. If it isn't, it means that someone else has changed the row in the meantime, and you should now abort the update and inform the user (and if possible, elegantly present both the changed database data and the user's data).

It is important that the "read current row version and update row only if it hasn't changed" operation is a part of the same transaction; otherwise, you'll run into race conditions. Some database servers contain built-in ways to handle this, such as SQL Server's SNAPSHOT isolation level.

Upvotes: 4

Related Questions