📘 Database Transaction
A database transaction is a logical unit of work performed on a database that is treated as a single, indivisible operation. A transaction consists of one or more database operations, such as inserts, updates, or deletes, which are executed as a single unit.
ImgRef - guru99
📘 Concurrency in Transaction
Concurrency is necessary in transactions because modern database management systems are designed to handle multiple users or applications concurrently. When multiple users or applications access a database at the same time, they may perform transactions concurrently, which can lead to data conflicts, inconsistent data, and other problems.
Concurrency control is the mechanism used to ensure that transactions are executed independently and in a way that ensures data consistency and integrity. Without concurrency control, multiple transactions may attempt to modify the same data at the same time, leading to conflicts and inconsistent data.
Concurrency control uses locking mechanisms to ensure that transactions are executed in a controlled manner. Locks are used to prevent multiple transactions from modifying the same data simultaneously. When a transaction wants to modify a piece of data, it must first acquire a lock on that data. If another transaction has already acquired a lock on the same data, the transaction must wait until the lock is released before it can proceed.
Concurrency control also uses isolation levels to define the degree of isolation between transactions. The isolation level determines how much a transaction can see the changes made by other concurrent transactions. The most common isolation levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
In summary, concurrency control is necessary in transactions to ensure data consistency and integrity in a multi-user environment. Concurrency control mechanisms such as locking and isolation levels help prevent data conflicts and ensure that transactions are executed independently and in a controlled manner.
📘 Transaction States
A transaction in a database management system (DBMS) goes through various states during its execution. Here are the typical states of a transaction:
Active: The transaction is in the active state when it is executing. In this state, the transaction may perform read and write operations on the database.
Partially Committed: The transaction enters the partially committed state after it has executed all its operations and just before it is committed to the database. In this state, the transaction has executed all its operations, but the changes made by the transaction are not yet permanent.
Committed: The transaction enters the committed state after it has been successfully written to the database. In this state, the changes made by the transaction are permanent and are visible to other transactions.
Aborted: The transaction enters the aborted state if it encounters an error or if it is explicitly rolled back by the user. In this state, any changes made by the transaction are undone, and the database returns to its state before the transaction started.
Failed: The transaction enters the failed state if it encounters a system error, such as a power failure or a hardware failure. In this state, any changes made by the transaction are undone, and the database returns to its state before the transaction started.
Suspended: The transaction enters the suspended state if it is temporarily stopped, usually due to a lock conflict with another transaction. In this state, the transaction is waiting for the required resources to become available before it can continue executing.
State Transition Diagram
ImgRef - guru99
The state transition diagram consists of the following components:
States: The states of the transaction are represented as circles or ellipses. In the above diagram, the states are Active, Partially Committed, Committed, Aborted, and Failed.
Transitions: The transitions between states are represented by arrows or lines. The arrows show the flow of control between different states of the transaction.
Actions: The actions performed during each transition are shown next to the arrows or lines. For example, in the transition from Active to Partially Committed, the action is to prepare the transaction.
Conditions: The conditions that must be met for a transition to occur are shown next to the arrows or lines. For example, in the transition from Active to Partially Committed, the condition is that all operations must be executed successfully.
Now let's take a detailed look at how a state transition diagram works:
The transaction starts in the Active state, where it can execute read and write operations on the database.
When the transaction has executed all its operations, it moves to the Partially Committed state, where it prepares to commit the changes to the database.
If the prepare step is successful, the transaction moves to the Committed state, where the changes made by the transaction are permanently written to the database.
If an error occurs during the prepare step or the commit step, the transaction moves to the Aborted state, where all the changes made by the transaction are rolled back.
If a system error occurs, such as a power failure or a hardware failure, the transaction moves to the Failed state, where all the changes made by the transaction are rolled back.
If a lock conflict occurs, the transaction may move to the Suspended state, where it waits for the required resources to become available before it can continue executing.
In summary, transactions in a DBMS go through various states during their execution, including active, partially committed, committed, aborted, failed, and suspended. Each of these states represents a different phase of the transaction's lifecycle and has implications for data consistency and integrity in the database.
📘 ACID Property
ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These are four properties that guarantee the reliability and consistency of transactions in a database management system (DBMS). Let's take a closer look at each property with an example:
Atomicity: Atomicity means that a transaction is treated as a single, indivisible unit of work. Either all the changes made by the transaction are committed to the database, or none of them are. For example, suppose a bank customer wants to transfer money from their checking account to their savings account. The transaction must be atomic to ensure that the funds are either transferred completely or not at all. If the transfer is not atomic, it could result in the funds being lost or duplicated.
Consistency: Consistency means that a transaction takes the database from one consistent state to another consistent state. In other words, the changes made by the transaction must preserve the integrity of the database. For example, suppose a customer orders a product from an online store. The transaction must ensure that the product is deducted from the store's inventory, and the customer's account is debited for the purchase.
Isolation: Isolation means that a transaction executes independently of other transactions. Each transaction sees a consistent view of the database, even if other transactions are executing concurrently. For example, suppose two customers order the last remaining product from an online store simultaneously. The transactions must execute in isolation to ensure that each customer receives a consistent view of the store's inventory.
Durability: Durability means that once a transaction is committed to the database, it remains committed even in the event of a system failure. The changes made by the transaction are permanent and cannot be undone. For example, suppose a customer updates their billing information on an e-commerce website. The transaction must be durable to ensure that the customer's information is not lost in the event of a system failure.
In summary, the ACID properties are a set of properties that ensure the reliability and consistency of transactions in a database management system. Atomicity ensures that transactions are treated as single, indivisible units of work. Consistency ensures that transactions maintain the integrity of the database. Isolation ensures that transactions execute independently of other transactions.
📘 Schedule
In the context of a database management system (DBMS), a schedule refers to a sequence of operations that are executed on a database by one or more concurrent transactions. A transaction is a unit of work that consists of one or more database operations, such as read or write operations, that must be executed as an indivisible unit. Important points in schedule -
A schedule in DBMS is said to be serial if it contains only one transaction that performs all the operations sequentially.
However, in most practical applications, there are multiple transactions running concurrently that perform different operations on the same or different data items in the database. In this case, the schedule is called a concurrent schedule.
A concurrent schedule can lead to various problems, such as data inconsistency or transaction conflicts, which can affect the integrity of the database.
Therefore, DBMSs use concurrency control techniques, such as locking or timestamp-based methods, to ensure that transactions execute correctly and maintain database consistency.
Here's an example of a schedule in a DBMS:
Consider a database that maintains information about a bookstore's inventory of books. Suppose that there are two concurrent transactions T1 and T2, and they perform the following operations:
Transaction T1:
Reads the current stock of the book "The Great Gatsby".
Decrements the stock by 1.
Writes the updated stock back to the database.
Transaction T2:
Reads the current stock of the book "Pride and Prejudice".
Decrements the stock by 2.
Writes the updated stock back to the database.
The following is a possible concurrent schedule for these transactions:
In this schedule, T1 and T2 are executed concurrently, with T1 performing its read operation before T2 and both transactions completing their write operations before the other begins. However, this schedule could lead to a data inconsistency problem because T2 reduces the stock of "Pride and Prejudice" by 2, even though there might be only 1 copy available in the inventory after T1's update. This problem can be avoided by using concurrency control techniques to manage the execution of concurrent transactions.
✔ Serializability
It is a property of a database system that ensures that concurrent transactions appear to be executed in a serial order, even if they are actually executed concurrently. In other words, the system behaves as if the transactions are executed one at a time in some order, even though they are executed concurrently by multiple users or applications. Some of the important key points to be noted for Serializability are -
Serializability is important to ensure the consistency and correctness of a database, especially in the presence of concurrent transactions.
Without serializability, the outcome of concurrent transactions may be unpredictable and could result in incorrect data being stored in the database.
In order to achieve serializability, database management systems use concurrency control techniques, such as locking and timestamping, to control the access of multiple transactions to the same data items.
These techniques ensure that transactions are executed in a serializable order, even though they may be executed concurrently.
There are different levels of serializability, including strict serializability, which guarantees that the outcome of concurrent transactions is identical to the outcome of their serial execution in some order, and recoverable serializability, which guarantees that no committed transaction is undone by the execution of another transaction.
Achieving serializability in a database system is an important goal for ensuring data consistency and integrity.
✔ Conflict serializable vs View serializable
Thanks for reading, and happy coding!
Effective Transaction Management in Database Management Systems (DBMS) -> DBMS Concurrency Control: Techniques and Best Practices