Implementing DB Record Locking Mechanism - non-optimistic

christiankatict

Hi all,

We have identified that we need the ability to mark certain records in arbitrary DB tables of the OpenPetra Database as 'locked' or 'checked out', indicating to the whole application that some user needs to work exclusively on this record and that therefore no other user must be able to edit it. For that ability we not only need to be able to 'lock' a certain DB record in a certain DB table, but we also need to be able to check whether a certain DB record in a certain DB table is currently 'locked' (and preferable also find out who locked it, and when).

Examples of OpenPetra DB Tables where such a 'locking' / 'checking out' would be needed in the : a_batch, a_gift_batch, a_ledger.

OpenPetra has a well working system for 'non-optimistic' record locking (which is the default locking mechanism we use with OpenPetra for speed and concurrency reasons). It is also possible to acquire 'pessimistic' record locks using a DB Transaction with the desired IsolationLevel, so those aspects of usual DB record locking are already catered for within OpenPetra.

The problem comes in when a need arises to check whether a certain record in a certain DB table is 'locked' or 'checked out' by a user. Also, a provision needs to exist to 'unlock' a single or all 'locked' DB record(s) in case (1) a user does no longer need the 'lock', (2) in case the user's client crashes, (3) after a server restart [all locks need to be removed then], (4) an administrator wants to manually remove lock(s).

There are various approaches to solve this problem:

One approach is described in a feature request, sourceforge.net/apps/mantisbt/o ... php?id=109, which suggests adding a special field to DB tables where such a feature is needed.[/list:u]
- Another approach is the established pattern 'Pessimistic Offline Lock' (see forums.asp.net/t/880691.aspx/1. Instead of the TIMESTAMP column I would use a varchar column in which the s_modification_id_c of any 'locked' record would be put (s_modification_id_c's are unique across all records in all DB tables) and I would extend that DB table with a regular DateTime field so we can display information to users (who try to acquire the lock) when the lock was taken out.[/list:u]
  - Another possibility would be to keep track of 'locks' like in the second approach, but keep the table of the locks in server memory (e.g. using a Typed DataTable), and not in a DB table. The advantages would be that the DB would not need to be accessed for lookups of potential locks and that there would not be a need to remove all locks after a server restart. The object that holds the Typed DataTable in server memory would need to be able to be accessed from all Client Sessions(=Client AppDomains).[/list:u]
    (Can you come up with another approach?)
    
    I would suggest the third approach as it keeps track of all locks in one Typed DataTable in memory and that means that (1) DB tables don't need to be altered just because application logic (or user requirements) suddenly require a lock feature on records of a DB table (as is the case with the first approach), (2) checking for and maintaining of locks (esp. the 'unlock' scenarios 2, 3 and 4 mentioned above) is easier, (3) speed as lookups for potential 'locks' are done in memory and not in the DB.
    
    Further suggestions and ideas, or proven implementation scenarios, perhaps even using .NET?

ctopenpetra

Hi Christian.

Thanks for your responses to the issues of locking etc. and your suggestion for approaches to locking make good sense. A few questions:

What transaction isolation level would you suggest on the locking table itself?
What is the projected number of concurrent users on OpenPetra (based on current Petra server use), which may mean that any locking strategy has to be scalable?
Do any centres run processes whereby they upload a large batch of, e.g., financial transactions into Petra at once resulting in many new batches being created which may mean repeated writes to the locking control table?

Being in memory is, of course, a major advantage for an OpenPetra locking control table, but we still need to consider the hits this table is likely to experience and how far any approach could be scaled up for larger implementations.

Thanks

pokorra

I also think that the idea of the inmemory mechanism is the best solution. It does not need to be a DataTable, it can be any structure that holds the given information, who is locking which batch.

For the numbers that you are asking for, Chris:
In the German office, we usually have only 20 people logged in maximum at the same time.
And of those, only 4 or 5 are doing finance.
I guess, for a normal office, competing for batches is a very rare occasion, we just need to make sure that it cannot happen.

Although, I have heard of India, they are doing lots of finance processing centrallized, but they have now another tool for entering the data anyway, and then just one person imports it into Petra and posts the batches.

So I guess, we can do quite a simple solution. The server just needs to keep track of who is locking what.

christiankatict

This will be implemented as an in-memory solution - see Mantis Bug https://tracker.openpetra.org/view.php?id=448 (which is a Feature and not a Bug).