(my)SQL Row Lock while user enters data c#

Question

I'm about to create a application with the use of a database, where multiple users are allowed to edit information about an entity.

tldr: How to lock a row in a database while a user is editing it's information in C#.

I have done some research on Locking a record in a database while a query is executing. What I mostly see is locking with begin transaction and commit transaction. However this does not suit my needs because it needs to be locked longer then the time the query is executing.

If user1 is editing a record (in c#, with a form application, filling out some textboxes), then I need that row to be locked until he is finished with editing. Because at the same time user2 might try to edit the same record. By the time user1 is done, everything is fine, but when user2 finishes, he ether overwrites or gets errors back about the record (name) he was searching for does not exist anymore.

A simple solution would be to add a column with IsLocked, depending on that a user can edit a row. But when for some reason the application crashes or the user force stops the process in task manager, the row would be locked forever. (or is there still a way to execute code while this happends?)

Another method would be to execute a query without commit transaction. For that, I believe, you have to keep the connection open, otherwise it will terminated. Keeping an open connection doesn't seem to be a good idea to me while running an application, or is it?

side note: title says (my)sql because I'm not quite sure which one I will be using.

Thx for your time!

Answer 1

I don't think it is best solution to lock row while user is editing data in some kind of GUI form. Application may crash or user can edit row for a long time... This is deffinitely not dependent on SQL engine you will be using (MS SQL, MySQL...)

• Record should have some unique ID. So query like UPDATE table SET ..... WHERE entityID = someID will not be dependent on previous values (only be aware of possible row delete)

• You can remember values before editing (or some timestamp which is part of database record) and check it before updating row so you will know someone has changed same record and you can notify user.

Regarding "keeping connection open" - DBConnection implements IDisposable, read this post how to use it. You don't have to be aware of opening connection again - read about SQL Connection Pooling.