Interview Q&A / SQL & DBMS

1. 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
2. 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
3. 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