Home

Learning Hub

Articles

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

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.

Output Table:

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:

Output Table (Products):

Output Table (Sales):

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:

Output Table:

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:

Output Table:

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.

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:

Output Table:

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:

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

Techniques for Recovery

1. Checkpointing: Periodically save the state of the database.
2. 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:

Output Table (Products):

Output Table (Orders):

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:

Output Table (Hotels):

Output Table (Bookings):

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:

Output Table (Books):

Output Table (Lending):

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:

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.