Transaction Processing

Transaction Processing in Databases

Transaction Processing refers to the management and execution of database operations as a set of operations grouped together to maintain the consistency and integrity of a database. A transaction in a database system is a logical unit of work that consists of one or more operations (such as inserting, updating, deleting) on the database.

Transaction processing ensures that the database is always in a consistent state, even in the event of system failures. It ensures the ACID properties (Atomicity, Consistency, Isolation, and Durability), which are fundamental principles that guarantee reliable transaction management.

---

1. What is a Transaction?

A transaction is a sequence of operations performed as a single logical unit of work. A transaction can involve multiple database actions, such as inserting, updating, or deleting data. These actions should appear to be executed as a single, indivisible unit.

Example:

In a banking system, a transaction could involve:

1. Debit from one account (subtracting money).
2. Credit to another account (adding money).

Both operations should either be completed together, or neither should happen at all (in case of a failure).

---

2. ACID Properties of Transactions

The ACID properties are the core principles that ensure reliable transaction processing:

a. Atomicity

• Atomicity ensures that a transaction is treated as a single unit, which means that either all operations of the transaction are executed successfully or none are.
• If a transaction fails at any point, all previous operations are rolled back to ensure the database is in a consistent state.

Example:

Consider a banking transaction:

• If money is deducted from Account A but the addition to Account B fails, the whole transaction should be rolled back. No money should be deducted or added.

b. Consistency

• Consistency ensures that a transaction takes the database from one valid state to another valid state, maintaining the integrity constraints (such as foreign keys, check constraints) and business rules defined for the database.
• After a transaction completes, the database must be in a consistent state.

Example:

• If a transaction debits an amount from one account and credits another, consistency ensures that the total balance of all accounts before and after the transaction remains valid according to the business rules.

c. Isolation

• Isolation ensures that transactions are executed in such a way that they do not interfere with each other, even if they are executed concurrently.
• Transactions should not see the intermediate results of other transactions, ensuring that the transactions appear to execute serially, one after the other, even if they are processed concurrently.

Example:

• If two transactions are running simultaneously, one transferring money from Account A to Account B, and the other transferring money from Account C to Account D, both transactions should be isolated. Neither transaction should see the partially updated state of the other.

d. Durability

• Durability ensures that once a transaction is committed, the changes made by the transaction are permanent, even in the event of a system crash or failure.
• After a transaction commits, its changes are written to non-volatile storage (such as disk) and are guaranteed to persist.

Example:

• If a transaction successfully transfers money between accounts, and the system crashes immediately afterward, the changes made by the transaction should still be reflected in the database when the system recovers.

---

3. Transaction States

A transaction can be in one of the following states during its lifecycle:

1. Active: The transaction is currently being executed.
2. Partially Committed: The transaction has completed its operations, but the changes are not yet permanent (i.e., not committed to the database).
3. Committed: The transaction has successfully completed, and the changes are now permanent in the database.
4. Failed: The transaction encountered an error and cannot be completed. It will be rolled back.
5. Aborted: The transaction was explicitly aborted, or an error occurred, and the transaction has been rolled back.

---

4. Transaction Control in SQL

In SQL, transactions are controlled using the following commands:

• START TRANSACTION (or BEGIN TRANSACTION): Marks the beginning of a transaction.
• COMMIT: Marks the successful completion of a transaction. All changes made by the transaction are saved permanently.
• ROLLBACK: Reverts the changes made by the transaction and returns the database to its state before the transaction began.
• SAVEPOINT: Marks a point within a transaction to which you can roll back, without affecting the entire transaction.
• RELEASE SAVEPOINT: Removes a savepoint from the transaction.

Example:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

COMMIT;

If any of the operations fail, a ROLLBACK can be issued to undo the changes.

---

5. Concurrency Control in Transaction Processing

Concurrency control ensures that multiple transactions can execute simultaneously without conflicting with each other. It prevents issues like dirty reads, non-repeatable reads, and phantom reads, which can arise when multiple transactions are run concurrently.

Some methods of concurrency control include:

a. Locking

• Locking is used to prevent other transactions from accessing the same data at the same time.
  • Exclusive locks prevent other transactions from reading or modifying data.
  • Shared locks allow other transactions to read the data but prevent them from modifying it.

b. Timestamp Ordering

• Each transaction is given a unique timestamp, and transactions are executed in timestamp order. This ensures that older transactions are given priority over newer ones.

c. Optimistic Concurrency Control

• Transactions are allowed to execute without locking, but before committing, the system checks if any conflicts occurred. If conflicts are found, the transaction is rolled back.

---

6. Deadlock in Transaction Processing

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a situation where no transaction can proceed. This can happen when:

• Transaction A holds a lock on resource 1 and waits for resource 2.
• Transaction B holds a lock on resource 2 and waits for resource 1.

Example:

• Transaction 1 locks Account A for withdrawal and waits to lock Account B.
• Transaction 2 locks Account B for withdrawal and waits to lock Account A.
• Both transactions are now deadlocked.

To handle deadlocks, most DBMS use a deadlock detection and resolution mechanism, which can:

• Timeout: Abort one of the transactions after a certain time limit to break the deadlock.
• Transaction rollback: Choose one transaction to be rolled back and restart it.

---

7. Recovery and Transaction Logs

To ensure durability and to recover from failures, databases maintain a transaction log. The transaction log records all changes made to the database during the transaction processing, and it is used to recover committed transactions or undo changes from failed transactions after a crash.

Key log actions:

• Write-ahead logging (WAL): Before making any changes to the database, the log is updated with the details of the transaction.
• Redo log: If the system crashes, the redo log helps in reapplying changes of committed transactions.
• Undo log: If the system crashes before committing, the undo log helps to roll back uncommitted transactions.

---

8. Isolation Levels

Isolation levels define the degree to which transactions are isolated from each other in terms of visibility of uncommitted changes. The most common isolation levels are:

1. Read Uncommitted: Transactions can read uncommitted changes from other transactions (can result in dirty reads).
2. Read Committed: Transactions can only read committed changes, preventing dirty reads, but non-repeatable reads are still possible.
3. Repeatable Read: Ensures that if a value is read once, it will not change during the transaction. This prevents dirty reads and non-repeatable reads, but phantom reads are possible.
4. Serializable: The highest isolation level, where transactions are executed serially (one after the other), preventing dirty reads, non-repeatable reads, and phantom reads.

---

9. Conclusion

Transaction processing is a vital part of database systems, ensuring data consistency, integrity, and reliability through the application of the ACID properties. It involves managing transaction states, handling concurrency, ensuring isolation, and guaranteeing durability through recovery mechanisms. By implementing proper transaction control, concurrency control techniques, and isolation levels, database systems can process transactions efficiently while maintaining data integrity.