WittCode💻

Is Your Database Corrupted?

By

Certain database operations should only be committed if the previous statements succeeded. If this is not done, the database can become corrupted.

Table of Contents 📖

Atomicity

Databases need to follow the ACID properties (Atomicity, Consistency, Isolation, and Durability). Atomicity is the property that ensures all operations are treated as a single unit. In other words, either all SQL statements succeed or none of them are applied. For example, consider the following SQL statements:

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

These two statements demonstrate a money transfer from account 1 to account 2. However, if the first statement fails (say there is a constraint violation where account 1 can not have a negative balance) the second statement should not be applied. This is atomicity in practice. However, the way we have implemented this currently does not ensure atomicity.

Transactions

One way to ensure atomicity is to use transactions. Transactions ensure that either all operations within it succeed or none are applied. If any part of a transaction fails (e.g., due to an error or a crash), the entire transaction is rolled back, leaving the database in its original state. We can create a transaction by using the BEGIN and COMMIT keywords.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
  • BEGIN - starts a transaction. Once the transaction is started, the changes made to the database are not visible until the transaction is committed.
  • COMMIT - finalizes the transaction, making the changes permanent and visible to others.

WARNING: Postgres will automatically treat statements as transactions. However, running these two statements separately will treat them as separate transactions due to Postgres's default autocommit behavior. To run them as a single transaction, we need to use the BEGIN and COMMIT keywords.