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 concurrency
Is the view of a transaction in flight consistent? Are other inflight transactions making changes to the database affects that transaction view?


Slides and resources for this course and all my other courses are available for members of the channel consider joining by clicking the join button 😊 You also get access to my udemy courses for free


Stay Awesome!
Hussein