Skip to content

Multi-Source DB Locking: Concurrency & Data Integrity in Databases

In the realm of database technology, even the most basic actions, such as checking your balance or receiving a deposit, invoke some serious considerations for data consistency. This article aims to underline the vital importance of concurrency and transactions in databases, and how they are the backbone of data integrity and consistency. 

As a starter, let’s explore database transactions.

What is a Database Transaction?

In database management, a transaction is a single, logical unit of work that may consist of multiple operations. It encompasses any logical computation performed consistently within a database. Every action or operation is meticulously orchestrated in transactions to ensure data integrity and consistency. Whether it’s updating a balance, recording a deposit, or transferring funds – each transaction must be executed flawlessly or rolled back entirely if even a single step fails.

A transaction ideally has four properties commonly referred to as ACID properties:

  1. A: Atomicity is either complete an action or revert it fully
  2. C: Consistency is that the transaction will only complete if the new state/copy of DB is valid; otherwise, it will fail
  3. I: Isolation means no one (source) can see the change until the transaction has successfully been completed.
  4. D: Durability refers to, when the transaction is completed successfully, it will be present in the database properly.

However, in PostgreSQL, every single query is a transaction.

Assume your bank account has $100, and these two actions take place concurrently:

  • You check your balance
  • Your friend deposits $100 into your account

Will your balance be $100 or $200? This depends on what is called “concurrency” in database technology. 

Understanding Transactions & Concurrency

A transaction is a unit of work that must be performed completely or undone when it is partially completed or fails. In database terminology, the above two actions are called transactions.

BEGIN;

INSERT INTO example_table VALUES(‘ABC’ , 20);

COMMIT;

So, if the deposit transaction is completed before checking the balance, the balance will be $200. Otherwise, the balance will be $100, as the deposit must still be completed. But neither transaction can be completed at the same time because of a phenomenon called the locking phenomenon. This phenomenon, used in database technology, ensures that only one transaction can access a particular data item at a time, preventing conflicts and maintaining data integrity.

Traditional Locking Mechanisms

There are many types of locks. If your bank was using a traditional locking mechanism, you would be unable to check the balance, or your friend might not be able to deposit at the same time. These are blocking locks because updating a record prevents you from reading it and reading a record stops you from updating it. Some examples of blocking locks are:

  1. Shared locks
  2. Exclusive locks

Multi-Version Concurrency Control (MVCC)

In modern systems, databases have moved beyond traditional blocking locks. They now employ a revolutionary concept known as MVCC (Multi-Version Concurrency Control). This cutting-edge technology allows for simultaneous reading and writing of records, a feat that was once unimaginable. It’s these advancements that make it possible for us to check balances, even while someone is depositing or withdrawing from an account.

How does MVCC work? Let’s explore.

As the name suggests, MVCC creates multiple versions of the database—one at the time of the start of the transaction and another at the end of the transaction. So, the first version would have a balance value of $100, and the second version would have a balance value of $200. These copies of a database are called snapshots. In this case, we don’t need to make snapshots of the whole database; instead, we take a copy of a single row. Thus, this type of locking is called row-level lock.

Before the transaction is committed or completed, the system will see the older version of the database (first snapshot), so in this case, the balance will still be $100, as the deposit transaction has not been completed yet. If the deposit transaction is completed, then the system will see the second snapshot, and hence, the balance will be read as $200.

Other Locking Mechanisms

In table-level locks, the whole table is locked until a transaction is committed. In advisory locks, we can advise the database with custom rules or instructions on which resources to lock and when to release the locks.

Preventing Deadlocks

A deadlock is a situation where transaction A depends on transaction B to complete, and vice versa. It can create an indefinite loop, potentially leading to system crashes. The gravity of this situation underscores the importance of preventing deadlocks, a responsibility that falls on the shoulders of database administrators and developers. PostgreSQL, as an enterprise-level database engine, takes this task seriously and implements robust deadlock prevention mechanisms. 

Wrapping it up

To wrap up, understanding how databases handle multiple tasks at once is key to keeping your data safe and your systems running smoothly. While older methods like locking are important, newer techniques like MVCC and row-level locking have made things way more efficient. PostgreSQL’s built-in tools for preventing deadlocks are another big plus. By getting a handle on these concepts, you’ll be well on your way to building reliable, high-performance systems.

For more insights into modern database management and technologies, contact us. You can also explore our training courses.

More like this:

How to Set Up Google OAuth 2.0 Using Passport.js in MERN?

How to Set Up Google OAuth 2.0 Using Passport.js in MERN?

Welcome to this comprehensive tutorial, where we’ll embark on an exciting journey of integrating Gmail authentication seamlessly into…
Empowering Microservice Architecture with Ambassador and Kafka

Empowering Microservice Architecture with Ambassador and Kafka

In recent years, microservice architecture has emerged as a popular approach for building scalable, flexible, and resilient software…
How to Debug NodeJS App Running Inside Docker Container?

How to Debug NodeJS App Running Inside Docker Container?

The goal of this example is to debug a Node.js application/server running inside a docker container. The guide…