DBMS ACID Properties

Atomicity

• All or nothing
• Either entire transaction succeed or entire transaction fails & gets rolled back.
• Use transaction to commit when eveything goes well
  • otherwise, rollback the transaction to achieve atomicity

Consistency

• All database rules are enforced, or the entire transaction is rolled back.
• e.g. Sum of all balances should be constant after each transaction
• Consistency ensures that all database constraints (e.g., foreign keys, unique constraints) are upheld.
• In ACID, its about consistently applying rules, but in case of CAP theorem for eventually consistent DB, we are talking about consistently reading back data that we just wrote.
  • how quickly can we get our data back after we’ve written it, because replication might take some time.

Isolation

• No transaction is affected by any other transaction that is still in progress.
• We can use mutex to achieve isolation for updating a critical resource.
• Isolation prevents interference between concurrent transactions. Using isolation levels ensures correctness while balancing performance.

Durability

• Durability ensures that once a transaction is committed, changes persist even after a system crash.
• Databases use logs or snapshots for durability.
• Enable write-ahead logging for durability
  • db.Exec(“PRAGMA journal_mode=WAL;”)
• Explanation:
  • Write-ahead logging ensures data is saved to a durable medium before committing.
  • The journal_mode=WAL ensures changes persist even after crashes.

Key Takeaways

• Atomicity: All-or-nothing via transactions.
• Consistency: Database rules are always upheld.
• Isolation: Transactions are independent using locking or isolation levels.
• Durability: Logs or snapshots ensure persistence.
• Each property is vital for robust and reliable systems. Let me know if you’d like to explore any of these in more depth!

Please visit https: https://codeandalgo.com for more such contents