Skip to content

✍ 06: Database Administration — Exercises

SQL & Databases

Module 06: Database Administration 12 questions

Home Notes All Exercises Quiz

Tip

Practice — try each question first, then expand the answer to check your reasoning.

Work through each question, then click Show answer to check yourself. Review the notes if you get stuck.

🔹 Q1. Two clerks both read berths_available = 1 for the same boat, both compute 0, and both UPDATE the row. What is this failure called, and what's the underlying cause?

Show answer This is the **lost update problem**. The underlying cause: both transactions read the same stale value before either committed its write, so the second `UPDATE` overwrites the first with no awareness a conflict occurred — one booking's decrement is silently lost.

❓ Q2. Write a BEGIN ... COMMIT transaction that uses SELECT ... FOR UPDATE to safely decrement berths_available on boat, avoiding the lost update problem from Q1.

Show answer 
BEGIN;

SELECT berths_available FROM boat
WHERE boat_reg_number = 'WA-4471'
FOR UPDATE;              -- takes an exclusive row lock

UPDATE boat SET berths_available = berths_available - 1
WHERE boat_reg_number = 'WA-4471';

COMMIT;
`FOR UPDATE` forces any second transaction trying to lock the same row to wait until this one commits or rolls back, so it can never act on the stale pre-decrement value.

🔹 Q3. Explain the difference between optimistic and pessimistic concurrency control. Which one is SELECT ... FOR UPDATE an example of?

Show answer **Pessimistic** control assumes conflicts will happen and locks data up front before any work begins, holding the lock until commit. **Optimistic** control assumes conflicts are rare, does the work without locking, and only checks for a conflict (usually via a version/timestamp column) right before writing — retrying if a conflict is found. `SELECT ... FOR UPDATE` is **pessimistic** — it takes the lock immediately.

❓ Q4. Transaction A locks row X then wants row Y; Transaction B locks row Y then wants row X. What is this condition called, and how does PostgreSQL resolve it?

Show answer This is a **deadlock** (deadly embrace). PostgreSQL's deadlock detector identifies the circular wait automatically, picks one transaction as the "victim," and aborts it with a `deadlock detected` error — the application is expected to catch that error and retry the aborted transaction.

🎯 Q5. List the four ACID properties and, in one sentence each, what each one guarantees.

Show answer - **Atomic** — all of a transaction's steps happen, or none do. - **Consistent** — every commit leaves the database obeying all its constraints. - **Isolated** — concurrent transactions don't see each other's uncommitted changes. - **Durable** — once committed, changes survive a crash.

❓ Q6. A bank-style transfer moves $500 from account A to account B: debit A, then credit B. Write this as a single SQL transaction, and explain what ROLLBACK would need to undo if the credit step failed.

Show answer 
BEGIN;

UPDATE account SET balance = balance - 500.00 WHERE account_id = 'A';
UPDATE account SET balance = balance + 500.00 WHERE account_id = 'B';

COMMIT;
If the credit to B failed (e.g., account B didn't exist, violating a foreign key), `ROLLBACK` must undo the debit already applied to A — otherwise $500 would simply vanish from the bank's books, violating atomicity.

🔹 Q7. Name the three read anomalies (dirty read, non-repeatable read, phantom read) and briefly distinguish them.

Show answer - **Dirty read** — reading another transaction's uncommitted change, which might later be rolled back. - **Non-repeatable read** — re-reading the *same row* twice in one transaction and getting two different values because another transaction committed a change in between. - **Phantom read** — re-running the *same filtered query* twice and getting a different set of rows, because another transaction inserted or deleted matching rows in between.

❓ Q8. Which SQL standard isolation level prevents all three anomalies from Q7? Which is the weakest, allowing all three?

Show answer `SERIALIZABLE` prevents all three. `READ UNCOMMITTED` is the weakest and allows all three (though note: PostgreSQL doesn't actually implement `READ UNCOMMITTED` distinctly — it silently upgrades it to `READ COMMITTED` due to its MVCC storage engine).

🔹 Q9. MySQL/InnoDB defaults to which isolation level? PostgreSQL and Oracle default to which one? Why does this surprise developers switching between the two?

Show answer **MySQL/InnoDB** defaults to `REPEATABLE READ`. **PostgreSQL and Oracle** default to `READ COMMITTED`. This surprises developers because code that behaves consistently under MySQL's default (immune to non-repeatable reads) may exhibit non-repeatable reads if the exact same transaction logic runs unmodified against Postgres's weaker default — the isolation level has to be raised explicitly if that guarantee is needed.

🎯 Q10. Write SQL to create a role booking_clerk that can SELECT and INSERT on the charter table, but only SELECT on customer_id, customer_name, and phone columns of customer (not the full row).

Show answer 
CREATE ROLE booking_clerk WITH LOGIN PASSWORD 'change_me_immediately';

GRANT SELECT, INSERT ON charter TO booking_clerk;
GRANT SELECT (customer_id, customer_name, phone) ON customer TO booking_clerk;
Column-level `GRANT` restricts `booking_clerk` from reading sensitive columns (e.g., `credit_card_number`) that might exist on the same `customer` table.

❓ Q11. What is the "principle of least privilege," and why should an application's database login almost never have DROP TABLE rights?

Show answer The principle: grant each account or role only the access strictly required for its job, nothing more. An application login should never have `DROP TABLE`/`ALTER TABLE`/superuser rights because if the application has a vulnerability (e.g., SQL injection), an attacker who takes over that connection is limited to whatever that account can do — without destructive schema privileges, they can't drop or alter tables even if they compromise the app.

🔹 Q12. Distinguish full, differential, and incremental backups, and explain how point-in-time recovery (PITR) goes beyond all three using PostgreSQL's write-ahead log (WAL).

Show answer - **Full backup** — captures the entire database. - **Differential backup** — captures everything changed since the *last full* backup. - **Incremental backup** — captures everything changed since the *last backup of any kind* (fastest to take, slowest to restore since every incremental must be replayed in order). **PITR** goes further: instead of restoring only to the moment of the last backup, you restore the last full backup and then replay the archived WAL segments up to *any specific timestamp* — e.g., one minute before an accidental mass delete — because every committed change is durably recorded in the WAL before it's applied to the data files.

All Exercises  |  Next: 07: Data Warehousing, BI & Big Data — Exercises