Interview Q&A / SQL & DBMS

1. What are the ACID properties of a transaction?

   ACID stands for Atomicity, Consistency, Isolation and Durability. A transaction either fully happens or not at all, leaves the data valid according to its rules, behaves as if it ran without interference from other transactions, and stays saved once committed, even if the server crashes.

   BeginnerIn-depth3 min read
2. What do COMMIT, ROLLBACK and SAVEPOINT do?

   COMMIT makes a transaction's changes permanent. ROLLBACK undoes everything since the transaction began. SAVEPOINT marks a point inside a transaction so you can roll back only part of it.

   BeginnerQuick answer1 min read
3. What is autocommit and how does it affect transactions?

   In autocommit mode every SQL statement is its own transaction and is committed as soon as it succeeds. To group statements into one atomic unit, start an explicit transaction with BEGIN or turn autocommit off.

   BeginnerQuick answer1 min read
4. What are dirty reads, non-repeatable reads and phantom reads?

   A dirty read sees another transaction's uncommitted data. A non-repeatable read gets a different value for the same row on a second read. A phantom read gets a different set of rows for the same query.

   IntermediateQuick answer1 min read
5. What are the four transaction isolation levels in SQL?

   From weakest to strongest they are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Higher levels prevent more anomalies such as dirty reads, non-repeatable reads and phantoms, at the cost of concurrency.

   IntermediateQuick answer1 min read
6. What is a deadlock in a database and how is it handled?

   A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle and aborts one transaction as the victim, and the application should retry it.

   IntermediateQuick answer1 min read
7. What is the difference between optimistic and pessimistic locking?

   Pessimistic locking locks the row up front so others must wait, assuming conflicts are likely. Optimistic locking takes no lock and checks at update time, usually with a version number, whether someone else changed the row.

   IntermediateQuick answer1 min read
8. What is MVCC in databases?

   MVCC keeps multiple versions of a row so readers see a consistent snapshot while writers create new versions. This lets reads and writes proceed without blocking each other. PostgreSQL, MySQL InnoDB and Oracle use it.

   AdvancedQuick answer1 min read