As we embark on this series of articles that will cover many exciting topics like concurrency control, speed of light, high availability (HA) vs disaster recovery (DR), consensus protocol/algorithm, and which would culminate with a deep discussion regarding a hot topic at the moment, Distributed Databases, I invite you to join me on a quest for knowledge and enlightenment. The purpose of this intellectual exploration is not to impose my opinions upon you but rather to equip you with the necessary tools to formulate your conclusions. By delving into a myriad of thought-provoking topics and presenting diverse perspectives, I aim to empower you as a critical thinker and allow you to navigate the labyrinthine maze of ideas that shape our world. So fasten your seatbelts, dear reader, for an exhilarating journey awaits us in unravelling the mysteries that lie ahead.

In the vast and complex world of database management, transactions play a central role in ensuring data integrity and consistency. Concurrent execution of multiple transactions poses a significant challenge in maintaining these crucial aspects. With the ever-growing demands for efficient and reliable data processing, understanding the intricacies of concurrency control mechanisms becomes paramount. This article delves into transactions and concurrency control in the database world, exploring the fundamental concepts and techniques that enable smooth and synchronized access to shared resources.

Transactions and beyond

So, let’s get started by discussing a fundamental concept: what is a transaction?  As we know, a transaction is nothing more than a collection of DML (Data Manipulation Language) operations grouped in one unit of work that is entirely independent of other units of work (transactions).  For example, let’s use a hypothetical instance of using an ATM (Automated Teller Machine) to withdraw some money.  You start your transaction when you enter your card into the machine, and it verifies your PIN.  Next, the transaction continues with your request to withdraw 100 dollars (for this, the transaction would check your current balance, and if there is enough money, update your account data by subtracting 100 dollars).  Note that the transaction is still running and unconfirmed up to this point.  Before the ATM gives you the money, it asks you to confirm the whole transaction (meaning all operations in it); if “Yes” is selected, the transaction is authorized (committed), and your balance is now saved in a reliable and consistent state – and of course, you get your money.

On the other hand, if the cancel option is selected, all operations on this transaction will be cancelled (rollback) as if they never happened.  In the end, a transaction always ends with a commit (success) or rollback (cancelled by the user, a condition, or even due to a failure).  So why are transactions so important?  Transactions are essential for several reasons, including:

  • Transactions represent changes to data;
  • Transactions are responsible for achieving data consistency even in the case of an incident (disaster), rolling back transactions that were not committed;
  • Provides total isolation for consistency, ensuring that all data read and changed is always consistent.

Traditional relational databases like MySQL, Oracle, and PostgreSQL use a set of properties called ACID that guarantees data validity against possible errors, incidents like power failure, and other misfortunes. All data edits within the transaction would be committed when ending a transaction.  If one action within the transaction fails or is cancelled, the whole transaction is repealed, and changes aren’t applied.  If every step succeeds, then the transaction persists.  This way, inconsistency would never be an issue.  It aligns perfectly with the atomicity requirements within ACID, and a sequence of operations that satisfies ACID is called a Transaction.

Understanding ACID

ACID, an acronym for Atomicity, Consistency, Isolation, and Durability, is a set of properties that ensure the reliability and integrity of data in databases. These principles guarantee data validity against any potential errors or incidents.


Firstly, Atomicity refers to the idea that a database transaction should be treated as an indivisible unit of work. It mandates that either all the changes made within a transaction are committed successfully or none at all. This principle ensures that if an error occurs midway through a transaction, all the changes are rolled back to maintain data consistency.

Secondly, Consistency ensures that data remains valid before and after any transaction execution. It enforces specific rules and constraints on the values stored within a database to maintain overall integrity. For example, if an operation violates these constraints (e.g., inserting duplicate primary keys), the entire transaction will be rolled back to keep the database consistent.

Thirdly, Isolation guarantees each transaction’s independence from others executing concurrently. With isolation mechanisms such as locking or multiversion concurrency control (MVCC), transactions can run without interfering with one another or accessing inconsistent data states during their operations.

Lastly, Durability ensures that once a transaction is committed successfully and its changes have been written to permanent storage media (such as disk), they will survive subsequent crashes or failures. In this way, ACID properties assure potential incidents by ensuring the long-term persistence of data.

Locking Strategies

Maintaining consistency and avoiding conflicts when multiple users access the same resource is paramount in databases. This is where a clear locking strategy becomes crucial. Whether it’s tables, data rows, or blocks, these resources must be protected while allowing concurrent access.

A well-designed locking strategy can prevent lost updates, dirty reads, and inconsistent analyses. A database system can maintain data integrity and ensure that conflicting transactions are properly synchronised by implementing appropriate locking techniques such as shared locks (read locks) and exclusive locks (write locks). However, an overly restrictive locking strategy could lead to contention among users and hinder performance, whereas a too-relaxed approach may compromise consistency.

Adopting a comprehensive understanding of different types of locks available, like record-level or table-level locks, will allow for fine-grained control over the concurrency level while maximising availability. Furthermore, transaction isolation levels are vital in determining how locking is applied within a database system. From read committed to serializable isolation levels – each provides its trade-offs between performance and consistency guarantees.

The transaction isolation level is a way of controlling the degree of isolation between concurrent transactions. It determines how much information about other transactions can be seen or accessed by a given transaction. Several isolation levels can be set for a database, including:

  • Read-only: This isolation level allows a transaction to read the database but not to write to it. This means that the transaction can see the data in the database but cannot change it.
  • Read-committed: This isolation level allows a transaction to read the database and to write to it, but only if another transaction has committed the data. This means that the transaction can see the data in the database but can only change it once the other transaction has finished its work.
  • Repeatable-read: This isolation level allows a transaction to read the database multiple times, but only if the data is the same. This means that the transaction can see the data in the database but cannot change it while it reads it.
  • Serializable: This isolation level allows a transaction to read the database multiple times, but only if the data is in the same order each time. This means that the transaction can see the data in the database but cannot change it while it reads it.

In summary, devising an effective locking strategy is critical for database systems to handle multiple user accesses gracefully. It protects against data inconsistencies and ensures maximum concurrency without sacrificing performance excessively. The choice of specific lock types and transaction isolation levels depends on the particular requirements of an application; there is more than one-size-fits-all solution. Striking the right balance between protection and accessibility is an art that every database designer must master to achieve

That’s when concurrency control comes into play.  For example, when you create an application with a database being utilized by many users simultaneously, multiple users may be trying to access or update the same data or object simultaneously.  

Concurrency control in the database world

Before cracking down on the particulars of the different concurrency control styles (our next article topic), I’ll explain why we need them in the first place.

Concurrency control (CC) is vital to any database system, ensuring multiple users can access and modify the data simultaneously without compromising its integrity. In a database, concurrency refers to the ability of various transactions to run concurrently without interfering. Without proper concurrency control mechanisms in place, several issues can arise, such as lost updates, dirty reads, and inconsistent results.

One key reason why concurrency control is required in a database system is to prevent the occurrence of lost updates. This situation arises when two or more transactions attempt to update the same piece of data simultaneously. Without appropriate measures, one transaction’s update may overwrite another’s change, resulting in data inconsistency. Concurrency control techniques ensure that modifications are serialized properly so that each transaction’s changes are applied atomically and consistently.

Furthermore, another critical factor driving the need for concurrency control is maintaining data isolation and preventing dirty reads. Dirty reads occur when one transaction reads uncommitted data from another concurrent yet uncompleted transaction. By implementing proper locking or isolation levels like Read Committed or Serializable, concurrent transactions can be prevented from accessing uncommitted or incomplete changes made by others until they are finalized by only allowing a transaction to continue when a proper lock to the data is granted.

Incorporating efficient concurrency control mechanisms within a database system prevents conflicts between concurrent processes and ensures correct processing while preserving data consistency and integrity. Achieving these goals ultimately enhances performance and reliability while providing users an uninterrupted experience when accessing shared databases concurrently over distributed systems or networked environments like cloud computing platforms.

Summary

This article has provided a comprehensive overview of the fundamentals of database systems. With a focus on transactions, ACID properties, isolation level, and concurrency control, we have explored the essential components that ensure data integrity and consistency in a database system. Understanding how transactions operate and the importance of maintaining ACID properties can significantly enhance the reliability and accuracy of data within a database.

In the realm of database management systems, isolation level and concurrency control stand as fundamental elements in ensuring data integrity within transactions. Isolation level pertains to the degree to which one transaction is isolated from the effects of other concurrent transactions. On the other hand, concurrency control refers to mechanisms put in place to manage simultaneous access to shared resources, preventing inconsistencies that may arise due to parallel executions.

The distinction between isolation level and concurrency control lies in their focus—while isolation level primarily concerns itself with preserving the consistency and integrity of individual transactions, concurrency control places its emphasis on coordinating and managing interactions among concurrent transactions. Essentially, the isolation level dictates how much a transaction can be shielded from others’ effects, while concurrency control is responsible for orchestrating multiple transactions accessing shared resources simultaneously.

Given this intricate relationship between isolation level and concurrency control, it becomes evident that they serve complementary yet distinct purposes in optimizing database performance. Understanding their interplay not only enables efficient utilization of resources but also ensures the preservation of data reliability—a critical aspect in any database system design.

What’s next

This is the first article of a series of articles that clarifies critical concepts before discussing some interesting emerging technologies. Please stay tuned, in the following article, I will discuss two essential concurrency control methods (Optimistic and Pessimistic) and their impact on database systems.