We're creating a VB.NET program that will be used on several computers with one central database that is on XAMPP/MySQL. Here's my question:
- User1 who's in computer1 wants to edit a row in table 1 to input data. So, it proceeded and he's currently editing row 1, table 1.
- User2 in computer2 logged in and wants to edit the same row 1 in table 1 supposed to edit the data as well, but user1 is still not done editing the data in row 1.
What should I use to make it so that user2 will have to wait first before user1's transaction is done? I believed that there would be an error/issue if both user1 and user2 edited the same row at the same time and then input different datas, either an error or the first one that edited it won't be save, I guess. If I'm wrong with this, though, feel free to clear it.
From what I've researched, transaction can be used for this but I'm not very sure yet and I want to have more explanation on this. How can I used this for the XAMPP/MySQL database and VB.NET? Someone also told me to use 'session' to achieve this. Which do you think is the better way between this and if you could please explain it in simpler terms.
However, if there are better ways to achieve this, please let me know. Thanks in advance for your help!
One option (and there are many different options) is to have an automatic-updating TIMESTAMP field. This should be updated whenever a record is inserted or updated.
(See https://www.mysqltutorial.org/mysql-basics/understanding-mysql-timestamp/)
You load this timestamp value when the user loads the record. Then when the user clicks save, your code checks this timestamp value in the UPDATE statement's WHERE clause. That way if the record has changed since you loaded it, the UPDATE statement will not affect any rows. You then tell the user that their update has failed because someone else edited the record in the meantime.