Best practice to check if the module is being used

Question

My question which perhaps - I hope not - can be considered as of opinion, is conceptual and even simple.

In most applications, we work with features that when used by multiple users at the same time can cause some problem in the database. In my application, I have events that trigger information for two, three and even more tables and if there are many users editing this information at the same time, some inconsistency may occur.

In general at these times, I create fields to store the id user who is entering the module and thereby validating the absence of a id stored to release - or not - the edit. If there is a user editing and a new one tries to access the module, I display a message and give it the option to proceed to the editing or wait for the finishing. If the user chooses to proceed, I change the id stored by such user and invalidate the first user’s changes, as before sending the changes, I check if the same has id corresponding to the stored in table.

How can there be session drop, power outage and other natural phenomena - or not - that prevent the user from finishing, clean daily the field that stores the id.

Is there any model that applies more security and/or practicality and/or organization to such tasks? Is there any way to work with this without needing to create fields that store the id of the current user?

Answer 1

Manually treating competition in bank changes trying to ensure data integrity takes a lot of work and usually doesn’t work right.

Your solution, for example, has this problem of keeping blocked records that are no longer being edited due to session crashes and user withdrawal.

Ensuring the integrity of data

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

In C# this can be done through Datasets or Data Tables and Data Adapters:

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

Through UPDATE, INSERT and DELETE commands informed by you or generated automatically, ADO.Net notices if the records were changed by another user between obtaining them and saving in the database, and, if so, the transaction is cancelled and the application informs the user. You don’t need to do any manual locking.

This becomes even simpler with the use of ORM frameworks like the Entity Framework or Nhibernate.

Lock for usability effect

You may also want to make a previous lock so that the user doesn’t waste time editing a record that he probably won’t 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 job: process each record, and the work should be divided between them (each processing a different set of records).

The solution we implemented was: when a user obtained a record to process it (processing consisted of making a few edits to a collection of records), a tag was registered in the database, informing that that set of records was already being processed by someone. So the application did not select for processing the records that were already marked.

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

So in fact, instead of selecting unblocked records, the app selected unblocked records and also those whose locks had already expired. This eliminates a routine to unlock records whose edition has been canceled.

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

Answer 2

If I were you, I wouldn’t block the bank cache distributed, such as the Redis.

Here’s a tutorial on how to use. Instead of storing user keys, I would store keys per module, or per 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 remove the right to edit if it has taken too long.

Answer 3

I wouldn’t leave/leave the tables allocated until the user thought to click somewhere to dislodge them, at least I never went through a situation where this case was the only alternative.

I do the following, I let the user do on screen the changes he wants, when he finishes on the screen I send everything p/ bunch of data, then I start making the appropriate locations, I make a LOCK TABLES in tables before starting to make all changes when these are key dependent in other tables