Transaction Management in DBMS

Introduction

• Transaction Management: Program or transaction unit, where execution can change the content of the database.
• Example: Bank database account updates like deposits and transfers.

Types of Transactions

• Selecting: Retrieving data from the database.
• Inserting: Adding data to the database (e.g., depositing money).
• Updating: Changing existing data (e.g., transferring money between accounts).
• Deleting: Removing data from the database.

Consistency in DB

• Database should be in a consistent state before and after a transaction.
• Consistent State: Database values remain correct before and after transactions.

Example of DB Consistency

1. Initial amounts:
   • Account A: 300
   • Account B: 400
   • Total: 700
2. Transaction: Transfer 100 from A to B.
   • Read A: 300
   • A - 100: 200
   • Write A: 200
   • Read B: 400
   • B + 100: 500
   • Write B: 500
3. Final amounts:
   • Account A: 200
   • Account B: 500
   • Total: 700

Ensuring Consistency

• Use ACID properties.
  • Atomicity: Transactions are fully completed or not executed at all.
  • Consistency: Transactions move DB from one valid state to another.
  • Isolation: Transactions are executed independently.
  • Durability: Completed transactions are saved permanently.

Conclusion

• Successful transactions change the database from one consistent state to another.
• Using ACID properties ensures database consistency during and after transactions.

Keywords: Transaction, Consistency, ACID properties, Database, DBMS