Lock all tables for editing for all other users when one user is editing data (in SQL Server) in asp.net

Question

I am new to ASP.NET. I have a MVC web application accessing data from three tables in SQL Server. I have a requirement where if one user is editing data from one table then other users should not be allowed to edit any data. I have tried using a table in SQL Server with a flag for editing which set and reset based on user activity. But this doesn't seem to be reliable. Can someone suggest a better option to achieve this.

Answer 1

I believe there might be a better way to handle the whole situation you are in, but a suggestion would be that instead of a flag, you would have a datetime column that holds a lock expiration time. When reading the record, if the current time is prior to the expiration, the form wouldn't load. If the current time is after the expiration; read the data, load the form, and update the expiration in a serializable transaction. Finally, when a user completes there update set the expiration to the current time. This way, an error in updating the flag wouldn't hold a lock on the records indefinitely.