Episode 105 - Relational Databases

  ACID ACID are four properties of relational database, they Atomocity, consistency, isolation and durability, and I think any one working with a relational database like postgres, mysql, sqlserver oracle, should understand these properties. In this video we will go through the four properties and explain why each is critical to make a relational database we will also talk about why some people are moving to  NOSQL database Atomicity  All or none. if a failure happened during transaction, db failure, or one of the queries failed.  Example Isolation Concurrency, is transaction isolated from other inflight transactions? if a transaction is in flight does it see changes from other inflight transactions? Does is it see any changes? Does it only see committed changes. Does leading to inconsistent results.  Problems arising from isolation (read phenomenons)  dirty reads Non repeatable reads Phantom reads  Isolation levels Read uncommitted Read committed  Repeatable read  Serializable  Durability When I commit a transaction does my changes stay durable after the database restarts/crashes etc.  See if your data still there.  Consistency Consistency from referential integrity keys  Does the number of likes on a picture = the number of rows that the picture got on another table? If a delete a picture does all the likes of that pictures go away on the other table. Consistency in reads  If I committed something does everybody see it immediately or are they going to get an old value? Consistency in concurrency Is the view of a transaction in flight consistent? Are other inflight transactions making changes to the database affects that transaction view? Jump Codes 2:00 What is a Transaction? 4:30 Atomicity  7:00 Isolation * 9:30 Isolation - Read phenomena * 11:40 Dirty Reads 14:40 Non-repeatable Read 17:00 Phantom read 18:53 Isolation Levels*2 19:20 Read uncommitted 19:55 Read committed 21:05 Non-repeatable Read 23:40 Serializability  25:00 Isolation Levels vs Read phenomena Table 27:45 Consistency  28:30 Consistency in Data 33:50 Consistency in Reads 35:00 Eventual Consistency  40:30 Durability  Cards 27:40 Exclusive lock vs shared lock