Question

Record locking in a multi node environment

1

Has anyone ever implemented a record locking mechanism in Mendix using only the database a the place where locks are stored? I have a feeling that given that Autonumber uses a database sequence there should be a way to leverage this into a working solution, but am having a hard time actually coming up with one. We are going to implement some type of locking mechanism to prevent users from editing the same records in the future and would like to get it right the first time. I know another solution would be to set up our own way of sharing state between nodes, but that is very hard to do. And no matter what solution would be chosen to do this, adds maintenance and monitoring overhead.

asked 2017-02-23

Nikel Kruizinga

3 answers

Rom van Arendonk · Answer 1 · 2017-02-23

We've had this discussion with Mendix about a year ago for a customer. Together, we concluded that there is no solution in Mendix 6. In Mendix 7, we were promised a solution: with the implementation of state in the client, the entire client-server-database interaction model would be revised and and option for optimistic locking would be implemented. Since I am no longer on the project, I haven't received updates about this.

David Sanders · Answer 2 · 2017-02-23

I currently use the record locking mechanism from the Community Commons module, but like you I suspect this will not work in a multi-node environment (as I think the records locked is stored in server memory). It would be great of servers in an application group could share this info between them, but I'm not sure this would be easy to implement.

In the past I had developed my own record locking mechanism based on creating and managing records in a RecordLock database table. I switched to the CommunityCommons as we did not currently use multi-nodes and I was worried about the level of specializations used.

All of my entities had to be specializations of a RecordLock entity which contained an association to the user and the date/time the record was locked
Every form had 2 versions - one normal read/write, and the other read-only
Every action that opened a form for modification had to check the RecordLock table, and based on whether it found a matching object or not, either opened the form read-only with a warning message, or opened the form in read/write version and created a lock record linked to the user.
Every window close, save or cancel action has to delete the matching record lock record.
A scheduled event was added to check how long records had been locked for, and released locks after a certain elapsed time.
Admin-type users were allowed to see lists of the locked records and delete the lock manually if needed.

This worked OK, and would work in amulti-node environment as the locked records data is stored in the database, but every entity in the application was a specialization of the same RecordLock entity, so I was worried about performance impact.

I'm not sure if this helps much. The ideal solution would be for servers in a node group to know about each other and be able to share the record lock info in memory.

Edit: The lock record being created is quite small so should be quick to save to the DB:

Chris de Gelder · Answer 3 · 2017-02-23

My experience is that a database a locking mechanism is impossible to get perfect in a heavy used environment (I agree with Rom), Community commons lock is only for single node, maybe someone made an implementation with Redis?