Relational Database ACID Transactions (Explained by Example)

ACID are four properties of relational databases, Atomocity, consistency, isolation and durability, and I think anyone 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 Chapters 0:00 Intro 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 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 con