Transactions in Relational Database Management Systems

Introduction

• Topic: Transactions in relational databases
• Focus: ACID acronym
• Definition: A transaction is a unit of work to retrieve, insert, remove, and/or update data.

ACID Properties

• Atomicity:

  • All operations in a transaction are completed, or none are.
  • Guarantee prevents partial completion.
  • Example: In a bank transfer, if an error occurs, money isn't deducted without being added to the receiver's account.

• Consistency:

  • Data remains valid after a transaction.
  • Constraints ensure valid states (e.g., account balances can't go below zero).

• Isolation:

  • Transactions are executed as if they are sequential, even if they run concurrently.
  • Higher isolation means fewer interactions between transactions, while lower isolation may improve performance but reduce guarantees.

• Durability:

  • Once a transaction is committed, changes persist even during power outages or failures.
  • Changes are written to disk, ensuring data integrity.

Example: Bank Transfer

1. Check Funds: Select query to confirm sufficient balance.
2. Update Accounts:
   • Deduct amount from payer's account.
   • Add amount to receiver's account.
3. Mark Success: Final update to confirm transaction completion.

Conclusion

• Importance of ACID properties for reliable transactions in databases.
• Not all database systems follow ACID; some use BASE properties instead.