Manually handling competition in bank changes trying to ensure the integrity of the data gives a lot of work and usually does not work right.

Your solution, for example, has this problem of holding back records that are no longer being edited due to session crash and user drop.

Ensuring Data Integrity

  

The most common way to treat competitors is by editing offline the entire data set and sending all changes to the database at once, in a single atomic operation, using the optimistic competition , where the first user to submit your changes wins.

In C # this can be done through Datasets or Data tables and Data adapters :

User changes that need to be atomic are all made in memory, in datatables . When the user saves the job, a database transaction is started, all changes made to the datatables are submitted to the database at once and the transaction is committed.

By means of UPDATE, INSERT and DELETE commands that you have been informed or generated automatically, ADO.Net understands that the records have been changed by another user between obtaining them and saving the database, and if so, the transaction is canceled and the application informs the user. You do not need to do any manual blocking.

This becomes even simpler with the use of ORM frameworks such as Entity Framework or NHibernate .

Usability blocking

You may also want to pre-block so that the user does not waste time editing a record that he probably will not be able to save because he already has another user editing.

This type of lock should not be used to try to ensure data integrity, but only as a usability feature.

For example, I worked on a project where there was a large amount of data to be processed by a group of users. They all had the same work: to process each record, and the work should be divided between them (each processed a different set of records).

The solution we implemented was: when a user obtained a record to process it (the processing consisted of doing some edits to a collection of records), a markup was recorded in the database, stating that that recordset was already being processed by someone. So the application did not select to process the records that were already marked.

The tag in turn was not the user id but a timestamp , and the lock expired after a time (which was the estimated time it took for the user to process the record).

So, instead of selecting unlocked records, the application selected unlocked records as well as those whose lock had already expired. This will dispense with a routine to unlock records whose edit has been canceled.

Note that this lock was intended only to improve usability, the optimistic competition technique described above is what guaranteed data integrity.

In its place, it would not block the bank, but using a distributed cache , such as Redis >.

Here is a how-to tutorial . Rather than storing user keys, I would store keys by module, or by screen, or even by Action in the case of an ASP.NET MVC application.

The advantages are many. You can renew the keys if a user is still editing the screen, or you can still remove the screen's right to perform the edit if it has taken too long.

I do not leave / leave the tables allocated until the user thinks about clicking somewhere to deallocate them, at least I never went through a situation where this case was the only alternative.

I do the following, I let the user to make changes to the screen he wants, when he finishes on the screen I send everything to the data base, so I start to make the necessary leases, I make a LOCK TABLES in the tables before you start making any changes when they are dependent of keys in other tables