Transaction in Databases

In the real world, accomplishing a task requires executing a series of operations. These operations together form a single unit of work, known as a transaction. Similarly, in database transactions, multiple queries must be executed to complete a single task.

During transaction execution, the database undergoes numerous changes. For example, consider an Amazon app: when you place an order, updates occur in the order table, payment table, and other related tables. At this stage, the database may be in an inconsistent intermediate state.

What is an Inconsistent Intermediate State?

An inconsistent intermediate state is a condition where the database is neither in its initial nor final state. During an ongoing transaction, modifications occur, but until the transaction is completed, the data may not represent a valid or meaningful state. As a result, anyone accessing the database at this point may encounter inconsistent data.

To prevent such issues, databases follow the ACID properties, which ensure reliable transaction execution. Let's explore them one by one.

ACID Properties

Atomicity

A transaction consists of multiple statements intended to achieve a final state. Atomicity ensures that either all the statements within a transaction are executed successfully, or none at all. This prevents the database from being left in a partial, inconsistent state.

Consistency

Consistency guarantees that the database remains in a valid state before and after a transaction. Any constraints, checks, or validation rules must always be maintained to prevent corruption or invalid data storage.

Isolation

Isolation ensures that multiple transactions can execute simultaneously without interfering with each other. This is crucial to prevent conflicts that may arise when multiple users or processes modify the database concurrently. Different isolation levels determine how transactions interact and whether they can see changes made by others before a transaction is committed.

Durability

Durability ensures that once a transaction is committed, its changes are permanently recorded in the database, even in the event of a system crash or power failure. This guarantees data persistence and reliability.

Execution of Transactions

Transactions involve a set of read and write operations. They can be executed in two ways:

Serial Execution

In serial execution, transactions are executed one after another, ensuring no interference. Each transaction completes before the next one begins, maintaining strict isolation.

Parallel Execution

Also known as context switching, parallel execution allows multiple transactions to run simultaneously, improving performance. However, this can introduce complexities, leading to anomalies if not handled properly.

Execution Anomalies in Concurrent Transactions

When transactions interleave, various conflicts may arise, leading to execution anomalies such as:

• Read-Write Conflict

• Write-Write Conflict

• Write-Read Conflict

These conflicts can cause inconsistent data retrieval and incorrect updates, requiring mechanisms like locking, timestamps, or isolation levels to manage them effectively.

By understanding transactions and their execution anomalies, we can ensure data integrity and maintain a robust database system.