DATABASE MANAGEMENT SYSTEM

TRANSACTION IN DBMS

A transaction is a unit of work that is performed against a database which change the database. A transaction typically represents a single logical operation on the data, such as a bank transfer or a purchase in an online store. Transactions in a DBMS are crucial for maintaining data integrity and consistency.

Properties of Transactions: ACID

1. Atomicity:

   • Definition: Ensures that all the operations within a transaction are completed successfully. If any part of the transaction fails, the entire transaction fails and the database state is left unchanged.
   • Example: In a money transfer transaction between two accounts, both the debit and the credit operations must succeed. If either fails, neither operation should be reflected in the database.

2. Consistency:

   • Definition: Ensures that a transaction brings the database from one valid state to another, maintaining the predefined rules and constraints (such as foreign keys, constraints, and triggers).
   • Example: If a transaction adds a record that violates a database constraint, the entire transaction will fail, ensuring the database remains consistent.

3. Isolation:

   • Definition: Ensures that the operations within a transaction are isolated from other transactions. Intermediate results within a transaction are not visible to other transactions until the transaction is committed.
   • Example: If two transactions are executing concurrently, the isolation property ensures that the operations of one transaction do not interfere with those of another.

4. Durability:

   • Definition: Ensures that once a transaction has been committed, it will remain so, even in the event of a system failure. Changes made by the transaction are permanently recorded in the database.
   • Example: After a successful money transfer transaction is committed, the changes to the accounts' balances are saved in the database, even if the system crashes immediately afterward.

Transaction States

A transaction in a DBMS typically goes through the following states:

1. Active: The transaction is being executed.
2. Partially Committed: The final operation of the transaction has been executed.
3. Failed: The transaction has encountered an error.
4. Aborted: The transaction has been rolled back and the database is restored to its state before the transaction began.
5. Committed: The transaction has been successfully completed, and all changes have been saved to the database.

Transaction Control Commands

DBMS provides several commands to manage transactions:

1. BEGIN TRANSACTION: Starts a new transaction.
2. COMMIT: Saves all the changes made by the transaction to the database.
3. ROLLBACK: Reverts all the changes made by the transaction to the state before the transaction began.
4. SAVEPOINT: Sets a savepoint within a transaction to which you can later roll back.
5. ROLLBACK TO SAVEPOINT: Rolls back the transaction to a specified savepoint.

BEGIN TRANSACTION;

-- Deduct amount from Account A
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A123';

-- Add amount to Account B
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B456';

COMMIT;