Pessimistic Locking for a distributed System (Part-1)

Motivation

There are a lot of use cases that require an exclusive access to a resource. In a distributed system, with several containers running the same piece of code in parallel, achieving consensus among all the containers gets quite convoluted. This entails intervention of a centralised locking mechanism controlled by a single entity deciding which processes uses the required piece of code exclusively at a time. Customisations as to who can access this piece of code in parallel and who cannot, with minimal starvation and without loss of consistency at any given point, is an added requirement.

Use Cases

1. synchronisation where we are using a combination of more than one databases. Eg:- Our system maintains entries in both mongo and MySQL and often updates are required both in mongo and MySQL for a transaction.
2. guaranteeing atomicity in database operations that are not atomic by design. Eg:-multiple document update operations in mongo.
3. avoiding race conditions in cases that entail in-memory manipulation of database records. Eg:- maintaining counters, payment infra that involves updates in multiple Databases, multiple users accessing same exhaustible resource in parallel (like in our system there are many leads that could be responded by only a single professional).

Concept

We wanted the implementation to be lucid, both in terms of implementation and technology used, yet capable of solving all the use cases that one might encounter.

MySQL solves this exact use case in a very nuanced way. We already have different ways a lock could be acquired in MySQL, any of which could be leveraged depending on our use case. This selection will dictate our implementation and could both limit or enhance the purpose MySQL could be leveraged for.

We decided to leverage INSERT….. ON DUPLICATE KEY UPDATE query wrapped in a transaction that ends in a commit.

Now, providing exclusive access to a selected set of processes and a shared access to the other, could be handled with what unique key you are trying to insert into mysql table. Please read through the implementation to have a clearer understanding of it.

Requirements

1. All you need is a centralised MySQL Database that is accessible from all the containers of the service.
2. In Case you require exclusivity across a set of services for a single or multiple code blocks, then containers of all those services should be able to access this Database (obviously).

Implementation And Execution

1. What we Essentially Do? We wrap execution of a piece of code (this is the resource you want to lock) within a MySQL Transaction that tries to insert an entry into a MySQL Database.
2. What we insert? We insert an identifier for that piece of code as primary Key in MySQL table.
3. How we end a transaction? We commit the transaction and run a delete query for that entry as we don’t want to fill up our MySQL table.
4. How it works? While the first Transaction is not complete no other transaction can insert/update row with the same key in the table and shall have to wait till the former transaction completes.

This simple implementation could be used at multiple places in the code base and could be modulated to solve all essential use cases. Not only simple and easy to implement, it also to much extent eliminates requirement of zookeeper and/or other technologies that you might otherwise use for synchronisation on a distributed system.

Do remember there are many unanswered questions. Like :-

1. How do we eliminate starvation? As clearly, this implementation will choke many processes that are trying to access the locked piece of code.
2. How do we ensure some processes access the code block while others do not when one or more processes already have acquired a lock on it? This is a very important use case.
3. how do we handle timeouts?
4. why we used `insert with update on duplicate` query in a transaction resulting in a commit and not a simple `insert` query in a transaction resulting in a rollback? This is quite important and interesting to know.

continued on Pessimistic Locking for a distributed System (Part-2) ……………..