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

[hnasr]

Timestamps

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
27:45 Consistency
28:30 Consistency in Data
33:50 Consistency in Reads
35:00 Eventual Consistency
40:30 Durability

[hnasr]

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
27:45 Consistency
28:30 Consistency in Data
33:50 Consistency in Reads
35:00 Eventual Consistency
40:30 Durability

[justsaysharu]

This is pure GOLD. Literally, one semester subject knowledge in less than an hour! Crazy-or-what :D

[bhavanprajapati]

please make videos on indexing and normalization.

[jinendrakhabiya]

Best video on ACID properties. Thanks!

[sumeetracharla]

you are really a talented person, one of the best video's i have seen in my life :)

[ameyapatil]

No way ! No way someone makes a video so good ! Thanks !

[demislam]

great content! worth every second!! loved how you always highlighted the fact that its up to the developer to make the tradeoff in deciding what properties to satisfy and what to be loose about. That's basically what software engineering is, it's not just writing codes/queries and declaring models that the orm automatically creates a db out of.

[nmprecious]

Yes, I reached the end of the video. Thanks a ton.

[shabnamhaque]

hi hussein, a friend of mine introduced me to ur channel 5 days ago and let me tell you I'm hooked. you are doing great great work . Kudossss keep em coming!

[Sujankumarreddy]

Why my University doesn't have a professor like you :( You are AMAZING! Thanks for the great content.

[sagartyagi]

I used to watch a lot of tech videos before discovering your channel. Now I just want everything to be taught by you, you're a legend sir. Can we please make a video about Distributed Database? I'm really interested in knowing how do we prevent concurrent writes/updates in a distributed db.

[wh]

The Isolation part is very interesting. Great examples to help me remember. If I can give a suggestion, a summary at the end to tie all things up would help me remember even better. I'm subscribing and liking for more!

[aduonye]

I enjoyed every bit of your lecture especially the lucid examples. Well done! I will be glued to your channel for a long time.

[emiliosoto]

You are a professional at teaching. Thank you !

[santoshbhatnagar]

This video literally cleared my fundamentals, have read about this in college, didn't get it clearly understood.
Thank you @hussein nasser

[ary]

Just discovered your channel yesterday, thanks for your videos! I love the way you explain things, not only the what, but also the why, showing us how to think, this will help me in my first job interview :)

[jayjay]

Best resource to understand acid tranction

[SwetPotato]

Thanks for clarifying all these.
ACID was only on one slide in my Uni database subject and now we are asked to write something about it as part of an assignment.

[mind]

wow really nice video, watched all and repeating many parts to understand better. More than one hour is gone! Really love the video!!