SQL & PostgreSQL
Joins, window functions, transactions, and query optimization.
What does ACID mean?
ACID describes four properties used to reason about reliable transactions: atomicity, consistency, isolation and durability. Together they address partial changes, preserved rules, concurrent work and persistence after commit. They do not automatically supply correct business rules or the strongest isolation level.
What are isolation levels and read anomalies?
Isolation defines how one transaction can observe and interfere with concurrent transactions. Different levels allow different anomalies to balance correctness, concurrency and retry cost. PostgreSQL uses snapshots under MVCC, but even a stable snapshot does not automatically make every business decision safe against concurrent writes.
Optimistic vs pessimistic locking?
Optimistic locking checks whether data changed before committing an update, often by comparing a version number. Pessimistic locking acquires a database lock before performing a critical change. The first avoids waiting when conflicts are rare; the second coordinates competing writers but can increase waits and deadlocks.
What is a deadlock, and how do I handle it?
A deadlock is a cycle of waiting transactions: each holds a lock another needs to continue. The database detects the cycle and cancels a participant so the others can proceed. Applications must treat that cancellation as a failed transaction and decide whether to retry it.
Difficulty is an editorial estimate. Completion means you chose to mark a question as understood.