Join Our 4-Week Free Gen AI Course with select Programs.

Request a callback

or Chat with us on

Learning Hub

Articles

Exploring Transaction in DBMS: Operations, ACID Properties, and Practical Examples

Basics of SQL

12 Hrs. duration

12 Modules

2600+ Learners

Start Learning

How does the system protect your data when you transfer money from your bank account? What occurs if the system crashes in the middle of the transaction?

These concerns highlight how crucial transactions are to database management systems.

In database management systems, a transaction is a series of actions carried out as a single work unit. If one component fails, the transaction is rolled back in its entirety, guaranteeing consistency in the database.

Let’s examine the transaction in DBMS in more detail and understand their significance.

Detailed Explanation of Transaction Operations

Different kinds of requests can be made by a user to see and alter database contents. Let’s discuss them one by one:

Read Operation

Reading data from a database is like checking your bank balance. The system fetches the data and shows it to you.

This operation doesn’t change the data; it just displays it.

Write Operation

Writing data updates, the database. Imagine transferring money between accounts. The system reads the current balances, updates them, and writes the new values back.

This operation changes the stored data.

Commit Operation

The commit operation saves all changes made during the transaction permanently. Think of it as confirming your purchase in an online store.

Once committed, the changes are final, and you can’t undo them.

Rollback Operation

Rollback undoes all changes made during a transaction. If something goes wrong, the database returns to its previous state.

It’s like canceling an order before it’s shipped, ensuring no unwanted changes persist.

Also Read: Advantages and Disadvantages of DBMS

Exploring ACID Properties in Transactions

Now, we have seen different operations involved in a transaction in DBMS. It is important to know how they all take place efficiently.

Here comes the concept of ACID.

A: Atomicity

C: Consistency

I: Isolation

D: Durability

ACID properties ensure transactions are processed reliably. Let’s break them down:

Atomicity: Ensuring Complete Execution

Atomicity means all parts of a transaction are treated as a single unit. They all succeed or fail together. If one step fails, everything is rolled back, leaving the database unchanged.

Example: Transferring funds between accounts.

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

Output Table:

Account ID	Balance
1	900
2	1100

Consistency: Maintaining Database Integrity

Consistency ensures that a transaction brings the database from one valid state to another. Rules, such as unique keys and constraints, must always be met.

Example: Ensuring inventory counts remain accurate during a sale.

Code Example:

START TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE product_id = 123;

INSERT INTO sales (product_id, sale_date) VALUES (123, NOW());

COMMIT;

Output Table (Products):

Product ID	Stock
123	49

Output Table (Sales):

Sale ID	Product ID	Sale Date
1	123	2023-08-01 10:00:00

Isolation: Managing Concurrent Transactions

Isolation ensures transactions do not interfere with each other. Changes made in one transaction are only visible to others once committed.

Example: Two users booking the last seat on a flight simultaneously.

Code Example:

START TRANSACTION;

SELECT seats FROM flights WHERE flight_id = 456 FOR UPDATE;

UPDATE flights SET seats = seats - 1 WHERE flight_id = 456;

COMMIT;

Output Table:

Flight ID	Seats
456	199

Durability: Persisting Changes After Completion

Durability guarantees that once a transaction is committed, its changes are permanent. Even in case of a system failure, the data remains intact.

Example: Saving a document in a cloud service.

Code Example:

START TRANSACTION;

INSERT INTO documents (user_id, document_name, content) VALUES (1, 'MyDocument', 'Hello World');

COMMIT;

Output Table:

Document ID	User ID	Document Name	Content
1	1	MyDocument	Hello World

Various States of a Transaction in DBMS

What happens when a transaction starts? What if it fails halfway? Understanding the states of a transaction can help answer these questions.

Transaction in DBMS

Active State of a Transaction

The active state is the starting point. Here, the transaction begins and performs read or write operations.

If everything goes smoothly, it moves to the next state.

Partially Committed State Before Finalisation

In this state, the transaction has completed its final operation. It is ready to commit, but not finalized yet.

If an error occurs now, it goes to the failed state.

Committed State After Successful Execution

When a transaction reaches this state, all operations are successfully completed. The changes are saved permanently.

This ensures data integrity.

Failed State Due to Errors

If an error occurs during the transaction, it moves to the failed state. The system ensures no changes are made to the database in this state.

This protects the data from corruption.

Aborted State and Rollback Process

An aborted state is when the transaction cannot be completed. The rollback process undoes all changes made during the transaction.

This brings the database back to its previous state.

Terminated State After Completion or Abortion

Once the transaction is either committed or aborted, it reaches the terminated state. Here, the transaction ends, and resources are released.

Transaction Scheduling and Serializability in DBMS

It is highly possible that more than one transaction will be executed in a system at a single time instant. So, how do we handle multiple transactions happening at the same time?

This is where transaction scheduling comes in.

Understanding Serial and Non-Serial Schedules

A serial schedule processes one transaction at a time. This keeps the database consistent but can be slow.

A non-serial schedule allows transactions to run simultaneously. This improves performance but can lead to conflicts.

Ensuring Database Consistency with Serializable Schedules

A schedule is serializable if it results in the same state as a serial schedule. This ensures consistency even when transactions run in parallel.

Example: Two users transferring money at the same time.

Code Example:

START TRANSACTION;

SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

SELECT balance FROM accounts WHERE account_id = 2 FOR UPDATE;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

Output Table:

Account ID	Balance
1	900
2	1100

Internship Assurance

DevOps & Cloud Engineering

Explore

Handling Transaction Failures and Recovery Mechanisms

Failures are inevitable. How do we recover from them? Generally, three types of failures occur; let’s understand them and how to handle them.

Types of Failures:

Transaction Failure: Errors within the transaction itself.
System Failure: Issues like power outages or software crashes.
Disk Failure: Physical problems with the storage device.

Techniques for Recovery

Checkpointing: Periodically save the state of the database.
Journaling: Log all changes before applying them to the database.

These techniques help restore the database to a consistent state after a failure.

Practical Examples of Transactions in Real-World Applications

Let’s look at how transactions work in real life with some comprehensive examples.

Example 1: Online Shopping Cart Transaction

When we add items to our cart and proceed to checkout, a transaction starts. It reads the stock, updates the cart, and commits the purchase.

If something goes wrong, it rolls back to avoid charging for unavailable items.

Code Example:

START TRANSACTION;

SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;

UPDATE products SET stock = stock - 1 WHERE product_id = 1;

INSERT INTO orders (product_id, quantity) VALUES (1, 1);

COMMIT;

Output Table (Products):

Product ID	Stock
1	49

Output Table (Orders):

Order ID	Product ID	Quantity
1	1	1

Example 2: Hotel Room Booking System

Booking a room involves checking availability, reserving the room, and confirming the booking. The transaction reads the available rooms, updates the reservation status, and commits the booking.

If there’s a problem, it rolls back to release the room for others.

Code Example:

START TRANSACTION;

SELECT available_rooms FROM hotels WHERE hotel_id = 1 FOR UPDATE;

UPDATE hotels SET available_rooms = available_rooms - 1 WHERE hotel_id = 1;

INSERT INTO bookings (hotel_id, room_number, guest_id) VALUES (1, 101, 1);

COMMIT;

Output Table (Hotels):

Hotel ID	Available Rooms
1	49

Output Table (Bookings):

Booking ID	Hotel ID	Room Number	Guest ID
1	1	101	1

Example 3: Library Book Lending System

When borrowing a book, the system checks if the book is available, updates the status, and records the lending. The transaction reads the book status, updates it, and commits the lending record.

If an error occurs, it rolls back to keep the inventory accurate.

Code Example:

START TRANSACTION;

SELECT available FROM books WHERE book_id = 1 FOR UPDATE;

UPDATE books SET available = 0 WHERE book_id = 1;

INSERT INTO lending (book_id, member_id, lending_date) VALUES (1, 1, NOW());

COMMIT;

Output Table (Books):

Book ID	Available
1	0

Output Table (Lending):

Lending ID	Book ID	Member ID	Lending Date
1	1	1	2023-08-01 10:00:00

Advantages and Disadvantages of Using Transaction in DBMS

Are you wondering if using the transaction in DBMS is worth it? What are the real benefits and potential drawbacks?

Let’s break it down.

Benefits of Data Integrity and Concurrent Access

Data Integrity

Transactions ensure that all operations are completed successfully or none are applied at all. This keeps the database consistent and reliable.

Concurrent Access

Multiple users can access and modify the database at the same time without conflicts. Transactions manage these simultaneous operations smoothly.

Data Security

Transactions provide a secure way to handle multiple operations. They ensure that only authorized changes are applied, protecting the data from corruption.

Error Handling

If an error occurs during a transaction, the system can roll back to its previous state. This prevents partial updates and maintains data consistency.

Simplified Application Logic

Using transactions can simplify the application code. Instead of handling errors and inconsistencies at the application level, we can rely on the DBMS to manage these issues.

Limitations and Challenges in Transaction Management

Complexity

Implementing transactions can add complexity to the database design and application logic. It requires careful planning and management.

Performance Overhead

Transactions can slow down the system due to locking and logging mechanisms. This can be a concern in high-transaction environments.

Resource Intensive

Handling transactions requires additional resources like memory and processing power. This can be a challenge for smaller systems.

Scalability Issues

As the number of transactions increases, managing them can become more challenging. This can affect the scalability of the system.

Potential for Deadlocks

When multiple transactions are waiting for resources held by each other, a deadlock can occur. This requires additional logic to detect and resolve.

Cost of Implementation

Implementing a robust transaction management system can be costly. It involves additional software and hardware resources.

Specific Implementations of Transactions in NoSQL Databases

How do NoSQL databases handle transactions differently? Let’s explore how ACID properties are implemented.

Supporting ACID Transactions in NoSQL Databases

NoSQL databases support ACID transactions even in a distributed environment. This means you get the same reliability and consistency as in traditional databases.

Example: Updating multiple documents across different collections within a single transaction.

Code Example:

const session = client.startSession();

session.startTransaction();

try {

const coll1 = client.db("test").collection("coll1");

const coll2 = client.db("test").collection("coll2"); 

await coll1.updateOne({ _id: 1 }, { $set: { name: "Alice" } }, { session });

await coll2.updateOne({ _id: 1 }, { $set: { age: 30 } }, { session }); 

await session.commitTransaction();

console.log("Transaction committed.");

} catch (error) {

await session.abortTransaction();

console.error("Transaction aborted. Error:", error);

} finally {

session.endSession();

}

Snapshot Isolation and OpLog in NoSQL Databases

Snapshot Isolation

This ensures transactions are isolated from each other. Each transaction sees a consistent snapshot of the data.

OpLog

The operation log (OpLog) records all changes. This helps in recovery and replication.

For example, a banking application can use snapshot isolation to handle concurrent transfers without conflicts.

Conclusion

In this blog, we’ve explored the critical role of the transaction in DBMS. They are essential for maintaining database consistency and reliability, making them a crucial aspect of any robust database system.

We learned that transactions ensure data integrity and consistency through ACID properties. We also discussed the various states a transaction can be in, from active to terminated.

Understanding these states helps in managing and troubleshooting transactions.

We examined the benefits, such as improved data security and simplified application logic, as well as the limitations, including complexity and performance overhead.

NoSQL databases, despite their differences from traditional systems, have adapted to support ACID transactions effectively.

By integrating transactions into your DBMS, you can maintain reliable, consistent, and secure database operations.

FAQs

What are the ACID properties in transactions, and why are they important?

ACID properties ensure reliability and integrity. They stand for Atomicity, Consistency, Isolation, and Durability, making transactions safe and consistent.

How does the isolation property prevent issues in concurrent transactions?

Isolation ensures transactions do not affect each other. Changes made in one transaction are not visible to others until committed.

What are some common causes of transaction failures in DBMS?

Failures can result from logical errors, system crashes, power outages, or hardware malfunctions.

Why is it important to use the commit operation in a transaction?

The commit operation finalizes changes, making them permanent and ensuring data consistency even after system failures.

The DevOps Playbook

Simplify deployment with Docker containers.

Streamline development with modern practices.

Enhance efficiency with automated workflows.

Programs tailored for your success

Popular

Data Science

Technology

Finance

Management

Future Tech

PLACEMENT ASSISTANCE

Certificate Program in

Extended Reality (VR+AR)

Part-time · 7 Months

Apply by : 7 September, 2024

Executive Program in

Product Management

With

Part-time · 6 Months

Apply by : 7 September, 2024

3 ASSURED INTERVIEWS

Accelerator Program in

Business Analytics and Data Science

With

Part-time · 10 months

Apply by : 7 September, 2024

Upskill with expert articles

View all

Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Technology

Certificate Program in Full Stack Development with Specialization for Web and Mobile

Certificate Program in DevOps and Cloud Engineering

Certificate Program in Application Development

Certificate Program in Cybersecurity Essentials & Risk Assessment

Finance

Integrated Program in Finance and Financial Technologies