How to prevent covering previous data when multi users update the same record in mysql?

Question

User A and User B are modifying the same column of the same record,while A finishes,the value of the column changes.

How to prevent B from covering the data that A inputs or just show some prompt?

The method I used before is to add a column that marks the 'updating count(increase by 1 on each update)'.Each time before someone updates a column ,he/she will get the mark,so when he/she commits the update,the mark will compare with the database,when different,someone has changed it.

Any other ideas?

Answer 1

I assume you want User B to fail his commit if the data has changed.

You can use this pattern:

1. Keep two copies of your data, Before data and after data.
2. When a user make a change of a column you only update the after data.
3. When you commit your data you do this:
4. Start a transaction towards the database
5. Read the currect value in database
6. Compare current value with your saved Before value
7. If the values are the same then you can write your new value.
8. If they are different you report error to user.

An alternative pattern is this:

1. Add a Timestamp column to your table.
2. Save the Timestamp of the data you read.
3. When you commit your data you do this:
4. Start a transaction towards the database
5. Read the currect timestamp in database
6. Compare with your saved timestamp
7. If the values are the same then you can write your new value and update the timestamp column to indicate that you have made a change to the row.
8. If they are different you report error to user.

This is a programmatic representation of the what databases do themselves when using optimistic concurrency transaction Control (https://en.wikipedia.org/wiki/Optimistic_concurrency_control).