Why do we need it?

Sometimes, you want to ensure that your piece of code is the only one modifying the data in the table. This is especially the case when working with parallel programming or multiple instances of the same application when they are used for load balancing or redundancy.

The last thing you want is to have one instance of your code overwrite the data that another instance has created and saved in the database. There is also a risk of wrong calculations when you have to take action depending on the current state of the system. If your application tries to use the current system state as input, it is less than ideal for other applications to modify the data until the calculation is completed.

How to implement it?

Let’s start very simple. We will need a table that will hold the locks to our database. What are locks? Locks will represent access permission. So, if one application “has” a lock, it can proceed with execution. Locks have to be unique, meaning that it should be impossible to have two copies of the same lock. Let’s try to create a table that will hold our locks. We will use Postgres, but the general principle can be applied to other relational database systems.

CREATE TABLE public.locks
(
    id SERIAL PRIMARY KEY,
    name text NOT NULL,
    UNIQUE (name)
);

This is the bare minimum that we need to store our locks. We will use the name column for storing the names of our locks. Notice how the column is UNIQUE, meaning we can’t have duplicate locks.

Now, whenever your application wants to acquire permission to do something, it has to try to acquire a lock by inserting it into the table.

INSERT INTO locks (NAME) VALUES ('VEHICLE_TABLE_READ');

If the query succeeds,that lock is acquired and stored in the database. If multiple instances try to acquire the same lock named “VEHICLE_TABLE_READ”, only one instance will succeed, and the rest will fail.

When an instance is finished with the task that requires a lock, the instance can release the lock by removing the lock from the table.

DELETE FROM locks WHERE name='VEHICLE_TABLE_READ';

After the lock is deleted, other instances can acquire it by re-inserting it into the database.

Possible improvements

So far, we have described a very simple solution. This solution can be improved and adjusted to specific use cases. For example, we can have additional columns to make the locks have better scope. Let’s imagine that we have a table called vehicles. We can make a lock table with the following columns: id, name, type. The type column can describe what type of vehicle the lock is applied to.

INSERT INTO locks (name,type) VALUES ('VEHICLE_TABLE_READ', 'cars');

This query can represent acquiring a lock that will lock only the data related to cars inside the vehicle table.

From the reliability perspective, it is a good idea to introduce a timestamp when the lock is created. This will help detect locks that were acquired but haven’t been released. If a lock has been acquired for a much longer period of time than it is usually expected for the operation to complete, we can consider it as stale. One example of this use case is when a process acquires a lock and then crashes before being able to release it. When the lock is stale, other processes can forcefully remove or overwrite it.

Conclusion

We have seen an example of a locking mechanism in relational databases and some use cases around it. Although simple, this example gives a good idea about how this mechanism works and may spark some ideas on adjusting it to specific use cases.


“Creating Locking Mechanisms in Relational Databases” Tech Bite was brought to you by Ismar Turnić, Senior Software Engineer at Atlantbh.

Tech Bites are tips, tricks, snippets or explanations about various programming technologies and paradigms, which can help engineers with their everyday job.

oban
Software DevelopmentTech Bites
February 23, 2024

Background Jobs in Elixir – Oban

When and why do we need background jobs? Nowadays, background job processing is indispensable in the world of web development. The need for background jobs stems from the fact that synchronous execution of time-consuming and resource-intensive tasks would heavily impact an application's  performance and user experience.  Even though Elixir is…

Want to discuss this in relation to your project? Get in touch:

Leave a Reply