Transaction in DBMS 

In a database, managing data accurately and securely is crucial, especially when multiple users or systems access and modify data at the same time. Without proper control, simultaneous reads, writes, or updates can lead to errors and conflicts. This is where transactions play a vital role. A transaction in a DBMS ensures that a group of operations is executed completely or not at all, preserving data integrity. This ensures reliable results even when several operations occur at the same time. In this blog, you will learn what transactions are in a DBMS, how they work, the different operations and states involved, and the types of transaction schedules.

Table of Contents:

• What is a Transaction in DBMS?
• Properties of Transactions in DBMS
• Operations in a Transaction in DBMS
• Transaction Management in DBMS
• Transaction States in DBMS
• Transaction Schedules in DBMS
• Common Transaction Problems in DBMS
• Practical Applications of Transaction in DBMS
• Conclusion

What is a Transaction in DBMS?

When using a Database Management System (DBMS), managing data accurately and securely is very important. Even small mistakes can cause serious problems such as incorrect balances, missing data, or loss of information. To handle this, the concept of a transaction is used. A transaction is a group of one or more operations, like reading, updating, or deleting data, that are treated as a single logical unit. The main goal of a transaction is to keep the database consistent and accurate even when unexpected issues like power failures or system crashes happen. If all the operations in a transaction complete successfully, the transaction is committed, which means the changes are saved permanently. If any operation fails, the transaction is rolled back to undo all changes and protect the database from errors.

For example:

If you transfer money from your bank account to your friend’s bank account, the actions involved would include: 

• Deducting money from your bank account.
• Crediting money to your friend’s bank account.

All these actions should take place as a single transaction. If one action fails, then the transaction should be rolled back.

Properties of Transactions in DBMS

Transactions in a Database Management System(DBMS) are structured by rules that help to provide integrity, accuracy, and information safety in the database, even when failures occur and transactions are executed concurrently by many users.

The rules are called ACID properties, which stand for:

• A: Atomicity
• C: Consistency
• I: Isolation
• D: Durability

Let us understand each property with an example:

1. Atomicity

Atomicity refers to the property of a transaction where it behaves like a single atomic unit of any kind. Either all the operations occur or none of them occur.

Key concept: No partial updates allowed.

Example: Let’s say you are transferring money from your bank account to your friend’s bank account. 

• The bank would deduct Rs 500 from your bank account.
• The bank would add Rs 500 to your friend’s bank account.

If the Rs 500 was deducted from your account, but the second step were to fail, the entire transaction would need to be undone. That is atomicity, either both steps succeed or neither does.

2. Consistency

Consistency ensures that all predefined rules, constraints, and data integrity conditions are maintained before and after a transaction in a database. It guarantees that the database moves from one valid state to another.

Key concept: A transaction must take the database from one consistent state to another.

Example: Consider a rule where the price of a car in the inventory cannot have a price less than Rs 1 Lakh. If a transaction changes a car price to Rs 80,000, then the rule is broken and the database is now inconsistent.

3. Isolation

Isolation ensures that concurrent transactions do not interfere with one another. Each transaction behaves as if it is executing alone. This prevents issues such as dirty reads and non-repeatable reads.

Key concept: One transaction should not impact another transaction that is running at the same time.

Example: Two people are trying to buy the last car in stock at the same time.

• Transaction A checks the stock (1 car in stock).
• Transaction B also checks the stock (1 car in stock).

If both transactions are allowed to be processed, they will go beyond the stock. Isolation ensures that only one transaction occurs, while the other either waits or fails, thus avoiding concurrency conflicts.

4. Durability

Durability means once a transaction is successful and committed, its result is permanently stored in the system (even if the system crashes). 

Key point: Once success occurs, changes are not lost.

Example: After a successful car sale, the database updates the stock and commits. The car selling application stops unexpectedly. When the system is restarted, after shutting down, stock updates should still be there.

Your Data Journey Starts Here

Enroll in our premium SQL course and gain practical skills trusted by top companies.

Explore Program

Operations in a Transaction in DBMS

In a DBMS, a transaction consists of a collection of operations that are intended to do a job together, for example, to perform a money transfer. All the operations in the transaction need to be done in a controlled way to preserve consistency.

Typically, there are four operations found in most transactions. 

For understanding these operations, we will create a table named Cars that will be used as an example.

-- Creating the table
CREATE TABLE Cars (
  car_id INT PRIMARY KEY,
  car_model VARCHAR(50),
  price INT,
  stock INT
);
-- Inserting data
INSERT INTO Cars (car_id, car_model, price, stock) VALUES
(101, 'Honda City', 1200000, 5),
(102, 'Hyundai Creta', 1500000, 3),
(103, 'Tata Nexon', 1100000, 4);
-- Displaying the result
SELECT * FROM Cars;

Output:

This is how the table looks after creating and inserting data into it.

1. Read(X) Operation in DBMS Transaction

This operation retrieves the current value of a data item X from the database and places the value in main memory (buffer) for temporary storage. The database is not altered with this read operation.

Example: Check the price and stock of the Honda City.

SELECT price, stock FROM Cars WHERE car_model = 'Honda City';

Output:

Explanation: Here, the query retrieves all details of the car model Honda City.

2. Write(X) Operation in DBMS Transaction

This operation modifies the value of a data item X in the database. The modified value is recorded in the main memory and then placed back into the database, where it becomes the new value of that data item. This operation is also known as the update operation.

Example: One unit of Tata Nexon is sold, and we want to update the stock by reducing it by 1.

-- The stock of Nexon before one unit was sold
SELECT stock FROM Cars WHERE car_model = 'Tata Nexon';
-- Update the stock after the sale
UPDATE Cars SET stock = stock - 1 WHERE car_model = 'Tata Nexon';
-- The stock of Nexon after one unit is sold
SELECT stock FROM Cars WHERE car_model = 'Tata Nexon';

Output:

Explanation: Here, this query checks the stock of the Tata Nexon before and after selling one unit..

3. Commit Operation in DBMS Transaction

The commit operation represents the point in time at which the effects of all operations on the database are now permanent. Once a transaction is completed and committed, it cannot be undone by rolling it back. This signifies the successful closure of the transaction. 

Example: COMMIT is used to save changes permanently if the stock was updated successfully.

-- Use to make changes permanent
COMMIT;
-- Displaying the result
SELECT * FROM Cars;

Output:

Explanation: Here, this query is used to make the changes permanent.

4. Rollback Operation in DBMS Transaction

The ROLLBACK operation undoes changes made during a transaction that has not yet been committed.

To properly employ ROLLBACK, you must:

• Explicitly begin a transaction.
• Then execute your update within the transaction.
• And then ROLLBACK if necessary.

Example: Accidentally reducing the stock of the Hyundai Creta.

-- Start the transaction
START TRANSACTION;
-- Mistaken update
UPDATE Cars SET stock = stock - 1 WHERE car_model = 'Hyundai Creta';
-- Realize the mistake, and undo it
ROLLBACK;
-- Display the result
SELECT* FROM Cars;

Output:

Explanation: Here, the ROLLBACK operation is used to undo the changes made by mistake.

Transaction Management in DBMS

Transaction management in DBMS involves a set of actions (a transaction) undertaken in order to keep the database accurate and secure. A transaction can be thought of as a single operation. For example, if you transfer money from one bank account to another, there are two basic steps: you subtract money from one account, then you add it to another.

Benefits of Transaction Management in DBMS

Let’s explore how transaction management helps keep database operations reliable, consistent, and secure.

1. Data Consistency: Database management systems (DBMS) guarantee that the database transitions between correct states, regardless of concurrent operations or concurrent users.
2. Error Recovery & Handling: A DBMS can recover the database state if a transaction fails, allowing the undo of any incomplete changes if the system crashes. This error handling allows the integrity of the database to remain intact.
3. Concurrency Control: A DBMS provides concurrency control, allowing multiple users to work simultaneously and maintain their transactions.
4. Simplified Application Logic: The DBMS allows the developer to “group” actions together for purposes of treating them as a unit of work, and for making the code easier to understand, maintain, and manage.
5. Secure and Reliable Operations: A DBMS protects the data from partial updates, unintended changes, or unauthorized users, so that you can ensure the information is correct and reliable.

Transaction States in DBMS

In database management systems, a transaction is a logical unit of work that involves one or more database operations like insert, update, or delete. It guarantees that all or no operations are successful (atomicity). Transactions make sure that data remains consistent even in the case of failures or crashes. Transactions follow ACID properties: Atomicity, Consistency, Isolation, and Durability.

Transaction Schedules in DBMS

A Database Management System (DBMS) can support concurrent operations, involving the execution of multiple transactions simultaneously. This concurrent execution has an order associated with it, which is called a schedule. A transaction schedule is used to manage the concurrent execution of transactions to ensure the efficiency and correctness of the data. 

Need for Transaction Schedules:

• To manage concurrent transaction execution.
• To reduce conflicts such as lost updates or dirty reads.
• To ensure the final state of the database is valid.
• To ensure data consistency and isolation.

Let’s explore the various types of schedules used in DBMS.

1. Serial Schedule

• Transactions are executed sequentially.
• No interleaving of operations permitted.
• Simple and safe. No interaction between transactions.
• Always provides data consistency.

2. Non-Serial Schedule

• Interleaving operations of multiple transactions.
• Improves system performance, increases resource utilization,
• Can lead to issues like inconsistency if left uncontrolled.
• Must add additional checks to ensure correctness.

3. Serializable Schedule

• A serializable schedule is a non-serial schedule that generates results equivalent to a serial schedule.
• It allows for concurrent execution while ensuring the database is correct.
• Serializable schedules are essential in multi-user environments to ensure correct and consistent results when transactions run concurrently.

Common Transaction Problems in DBMS

Transaction problems refer to the issues that arise in a DBMS when multiple transactions are executed simultaneously. These problems occur due to improper handling of concurrent transactions, which can lead to data inconsistencies. Common transaction problems include lost updates, dirty reads, and non-repeatable reads.

1. Dirty Read: A dirty read occurs when one transaction reads data that is being changed by another transaction that has not yet been committed. If the other transaction rolls back, the read data becomes invalid.

2. Lost Update: A lost update occurs when two transactions read the same data, then both update it. One of the updates is lost because it was overwritten.

3. Uncommitted Dependency: Uncommitted dependency occurs when one transaction is allowed to read data from another transaction that is not yet committed.

4. Inconsistent Retrievals: An inconsistent retrieval happens when a transaction reads related information from different places, but another transaction updates part of the data being referenced in between queries, leading to inconsistencies.

Practical Applications of Transactions in DBMS

1. Online Banking

An example of a transaction in an online banking service is when a user sends money from their checking account to the receiver’s account.

Steps involved:

• Read the balance of the sender’s account.
• Subtract the amount the user wishes to send from the sender’s account.
• Add the amount the user has sent to the receiver’s account.
• All changes are saved (Commit).

2. E-commerce Order Processing

When a customer places an order on an e-commerce site, multiple operations such as checking inventory, processing payment, and updating the order status must be executed as a single transaction to ensure data consistency.

Steps involved:

• Check if the product is in stock.
• Reduce the number of items in the inventory.
• Persist the order details.
• Process payment.
• Acknowledge the order with the customer.

Your Free Ticket to SQL Mastery

Master the foundations of SQL with our free, beginner-friendly course. Learn at your own pace.

Explore Program

Conclusion

In a DBMS, transactions make sure database operations happen safely and completely. By treating multiple steps as one unit, transactions keep data accurate, consistent, and reliable. The DBMS manages transaction stages, rules, actions, and orders to make sure everything runs correctly. When transaction programs follow the ACID rules, the DBMS can safely handle your data even during unexpected failures or when many users change data at the same time

Take your skills to the next level by enrolling in the SQL Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts.

Transaction in DBMS – FAQs