Interview - Database

interview

ACID:
A: Atomicity (ALL or NONE)
C: Consistency. The transaction should leave the system in a consistent state.
I: Isolation. If there are two transaction T1, and T2, and they are executed 
   in parallel. The result should be the same as though they were executed in 
   sequence.
D: Durability. After the changes are done, even if the system crash, when it 
   come back online, the result should be the same (the value of the change 
   should be persistent). The result of the transaction should be saved 
   completely.

The 3 problems that a fully ACID compliant database have to deal with:

1. Dirty read: a read is done while there is another parallel transaction 
     updating the same cell.
2. Non-repeatable read: in the same transaction, you execute the same query but 
   get different result, because another transaction has either insert, delete, 
   or update rows.
3. Phantom read: This is similar to non-repeatable read, but in the case of
   non-repeatable read, you get the same row back, but you may get different
   values for the individual cells, or no row is return.  When the phantom read,
   you may get more rows or less rows because another transaction has either
   insert, delete, or update rows.

The 4 isolation levels:

1. Read uncommitted: When a transaction try to read, it may get an uncommitted 
   result of another ongoing transaction. This is not a good isolation level. 
   This level does not solve any of the 3 common read problems.
2. Read committed: When a transaction try to read, it will wait until other 
   transaction committed.  This is accomplished using a write lock.
3. Repeatable reads: This isolation level solves the "dirty read" problem and 
   the "non-repeatable read" problem by using a write lock, and a read lock.
4. Serializable: This isolation level solves all 3 problems by using a write lock,
   a read lock and a range lock

Example of Consistency: Consider that we are transfering $100 dollar from
one account (A) to another account (B).  Let say that the initial amount in
account A is $1000 and the initial amount in account B is $200.  This is a two
steps process.  First, $100 is deducted from account A.  Second, $100 is 
deposited into account B.  If the system failed in between the two steps, the
transaction should be rolled back.  The total amount between the two accounts
should be $12,000.  If the transaction succeeded, the total amount between
two accounts should also be $12,000, assuming that we do not charge a fee
for the service.

What does ACID abbreviate for?

  1. A -> Atomicity (ALL or NONE)
  2. C -> Consistency. The transaction should leave the system in a consistent state.
  3. I -> Isolation. If there are two transaction T1, and T2, and they are executed in parallel. The result should be the same as though they were executed in sequence.
  4. D -> Durability. After the changes are done, even if the system crash, when it come back online, the result should be the same (the value of the change should be persistent). The result of the transaction should be saved completely.

What are the 3 problems that a fully ACID compliant database have to deal with?

  1. Dirty read: a read is done while there is another parallel transaction updating the same cell.
  2. Non-repeatable read: in the same transaction, you execute the same query but get different result, because another transaction has either insert, delete, or update rows.
  3. Phantom read

What are the 4 isolation levels?

  1. Read uncommitted: When a transaction try to read, it may get an uncommitted result of another ongoing transaction. This is not a good isolation level. This level does not solve any of the 3 common read problems.
  2. Read committed: When a transaction try to read, it will wait until other transaction committed.
  3. Repeatable reads: This isolation level solves the "dirty read" problem and the "non-repeatable read" problem by using a read lock.
  4. Serializable: This isolation level solves all 3 problems by using a read lock and a range lock
Isolation level / read problem Dirty read Non-repeatable read Phantom read
Read uncommitted problem problem problem
Read committed SOLVED problem problem
Repeatable reads SOLVED SOLVED problem
Serializable SOLVED SOLVED SOLVED
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License