Explain the ACID properties of a database transaction with a simple example.

What the interviewer wants to hear

• Atomicity: all steps succeed or none do
• Consistency: constraints and rules always hold
• Isolation: concurrent transactions do not interfere in unsafe ways
• Durability: committed data survives crashes, via write-ahead log
• Bank transfer example

Model answer

Think of moving 1,000 rupees from account A to account B. Atomicity means both the debit and the credit happen, or neither does; if anything fails, the transaction rolls back. Consistency means the database moves from one valid state to another, so constraints like "balance cannot be negative" or foreign keys are never broken. Isolation means another transaction running at the same time does not see a half-finished transfer; how strict this is depends on the isolation level. Durability means once COMMIT returns, the transfer is saved even if the server loses power a second later. Databases achieve durability by writing changes to a write-ahead log and flushing it to disk before confirming the commit.

More easy Database Administrator questions

• What is a database index, and what are the costs of adding one?
• How would you design a basic backup strategy for a production database?
• What is normalization, and when would you choose to denormalize?
• How do you find the slowest queries on a database server?
• What checks would you do at the start of a normal day as a DBA?
• What is database replication, and what is the difference between synchronous and asynchronous replication?
• When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
• What basic steps would you take to secure a new production database?
• Explain the main SQL join types and give an example of when you would use each.