I'd like to ask for suggestion, what would the recommended ways of addressing below problem, so I can do investigation and reading the right information. A user would be able to select an item from a drop down and press Edit button to change data for selected item. At this moment, I’d need to lock the record for other users (what would be preferred ways?). If another user tries to edit same item, show a message that it is locked by other user. Handling app / pc crashes to unlock the record (best solutions?)
Regards
Piotr
CodePudding user response：
When users are editing rows, you should use optimistic concurrency, instead of server-side locking. Under optimistic concurrency the client filters the update or delete using additional columns that act as a "concurrency token" to ensure that the row hasn't been updated by any other user. eg
update T set a = @a, b = @b
where Id = @id and RowVersionColumn = @rowversion;
select @@rowcount RowsAffected;
If the update or delete affects zero rows, then the client knows some other user has changed the row, and they can reload the current version and decide whether to re-submit the change.
In SQL Server a rowversion column is often used as the concurrency token.