GATE CSEIT/Database/ Transaction in Database ( With basic introduction).

GATE CSEIT/Database/. Transaction in Database ( With basic introduction).

Transaction in Database Management System (DBMS)

Introduction to Transactions

A transaction in a Database Management System (DBMS) is a sequence of one or more database operations that are executed as a single logical unit of work. Transactions are used to maintain data integrity, consistency, and reliability in a database.

For example, in a banking system, transferring money from one account to another involves multiple operations:

1. Deducting money from Account A.

2. Adding money to Account B.

If any of these operations fail, the entire transaction must be rolled back to maintain consistency.

Properties of Transactions (ACID Properties)

Transactions in a database follow ACID properties:

1. Atomicity – A transaction is either fully completed or fully rolled back.

2. Consistency – The database remains in a valid state before and after the transaction.

3. Isolation – Transactions are executed independently without interfering with each other.

4. Durability – Once a transaction is committed, the changes are permanent, even if a system failure occurs.

States of a Transaction

A transaction goes through different states during execution:

1. Active – The transaction starts execution.

2. Partially Committed – All operations are performed, but changes are not yet saved.

3. Committed – Changes are permanently stored in the database.

4. Failed – Transaction execution is interrupted due to an error.

5. Aborted – Transaction is rolled back to maintain consistency.

Types of Transactions

1. Read-Only Transaction – Retrieves data without modifying it.

2. Read-Write Transaction – Reads data, modifies it, and stores the changes.

Transaction Control Commands in SQL

1. COMMIT – Saves all changes made by a transaction permanently.

2. ROLLBACK – Undoes all changes made by a transaction.

3. SAVEPOINT – Creates a checkpoint within a transaction to rollback to a specific point.

4. SET TRANSACTION – Defines the properties of a transaction (e.g., isolation level).

Conclusion

Transactions play a crucial role in maintaining database integrity and consistency. By following ACID properties and using transaction control commands, databases ensure that operations are reliable and error-free.

GATE CSEIT/Database/ Transaction in Database ( With basic introduction).

Institute of Engineering Studies (IES, Bangalore)

Database Management Systems Lecture Notes

Chapter 17: Transactions