Thanks a ton for the gold standard content. I have a question though, In Atomicity, you mentioned if the $100 debited and then the DB crash event occurred, after restart it was $900 w/o credit update query, so when will the rollback occur? After the DB restarts as it seems like if DB crashes, what happened to the Tx state, is it lost or recovered after restart?
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.
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!
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!
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 :)
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.
Fantastic explanation, I was finally able to understand the isolation level concept and hopefully grasp it. Thank you very much, I have failed the ACID question during several interviews, what eventually led me to your material. Keep up with the great work!
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.
You've got some amazing content here! Good mix of breadth and depth for the topic discussed especially when analyzing isolation. Looking forward to viewing some of your other videos in the playlist, particularly if there's anything comparing SQL and NoSQL databases.
cw Thanks cw Im glad you noticed because that is what Im doing, trying to go breadth and when I find the topic interesting or on demand I go deep. I am planning to do some more DB videos love those ... thanks for your comment enjoy the content!
@@hnasr I have a query. When you say versioning, do you mean MVCC? Also, I see that MVCC uses exclusive locks unlike 2PL (uses shared and exclusive) for updates and actually is more difficult to implement and have to address all the anomalies like handling edge cases. For instance, using MVCC, Oracle can only offer Snapshot Isolation, not Seralizable, which has Write Skews. Then, does MVCC still stop Phantom Reads and how?
Amazing piece of content bro. Love that you bring examples and use-cases for each topic, because without a problem those don't worth a whole. Hoping to more see videos like this one from you :)
Hi hussain. The explanation is clear. Can you make video on how does a consistency maintained in distributed system among the multiple microservies & I really can't find a good content on how to build our small own database to understand the working of relational database.
Nice video and explanation, agree with all except - traditional OLTP databases provides eventual consistency, I dont think any banking system will go for eventual consistency, though postgres has both syncronous(will provicde consistency with perfromancer compromise) and async replciation, but I think thats mostly for HA purpose for banking\critical systems, with these days everything running on VMs, the critical databases are mostly vertically scaled periodically and async sharding/replication is usually used for non critical reads like reporting etc never for banking\critical transactions, so we have no option but to live with these 70s RDBMS systems for some core businesses which require ACID to be stricltly implemeneted.
phantom read and non-repeatable read looks the same to me, what am I missing? Okay so in case of non-repeatable we were updating the same row, and that was the cause of issue and in case of phantom read we created a new row which was the cause of the issue thank you for the amazing content. I have already bought your database course planning to watch that too.
Hey man. Love your video but i am confused about dirty read example. You have mentioned that Transaction was Updated but not committed. How can the other session read different value other than what is in Database. Well unless it is committed it will read that updated value otherwise it will still read the value that is in database. Does it make sense?
it means that the read transactions when in read uncommitted isolation level (sql server for example) are configured to read the latest row change wither this is committed or not. this could be in a different data structure usually called the WAL (write ahead log) what you described is a read committed isolation level where we read the value stored in the row and committed.
@@hnasr thanks for quick reply. Thanks for explaining this to me. I always thought one can read updated value from database after committing, silly me. Always learn something new. Thanks again
33:26 I remember the time when on TH-cam we could see the "exact" number of subscribers and how I refreshed the page after several seconds on the channels with millions subscribers and this number chaged. Now I understand why they moved from this way and started just showing "5M" for example
I have a question, do the changes made by a transaction before being commited or rolled back performaed directly on the database (on disk) or each transaction performs the changes on its own isolated memory ?
- Transaction: A bunch of queries - Atomicity: Either all the queries in a transaction must commit, or all of them must rollback - Isolation: is about isolating transactions to handle concurrency issues arising from concurrent transactions. How: versions and locks(shared/read) in db level, locks in application level. Problems = Dirty read, Non Repeatable read, Phantom read, Lost update. Solutions = Isolation levels like Read uncommitted, Read committed, Repeatable read, Serializable - Consistency: Is about Consistent data and Consistent read. Consistent data: Happens within a machine due to non-atomicity, non-isolation etc Consistent read: happens across machines, in both sql and nosql dbs when replicas are present. You update a record, but when you read after, you get old data. It is always eventual consistency when replocas are present - Durability: committed transactions must be persisted in a durable storage
Talking about isolation i kind of run into a weird issue while using Repeatable read level and locks If you have two transactions running at the same time and you use lock for update (select for update) to get the record (let's say wallet) on t1 (first transaction) and you update the balance column value from 10 to 20 and then it's committed, now since you used lock for update that means t2 was waiting for the wallet record to be released and now it can continue after t1 committed On t2 if you get select the wallet record normally (without using lock for update) you'll get the balance 10 because both transactions were running at same time and it makes sense because we're using Repeatable read isolation level BUT if you get the wallet using lock for update on t2 it gives you the balance 20 😅 Does the locks have more priority over isolation? Is this a normal behaviour ? Because I'm using this trick to have a general isolation level of Repeatable read but in some cases i want to get the committed record even if it was committed during the transaction not before it So it's like having two isolation levels , the general Repeatable read and the Read committed but only on records that's i chose using lock for update . Wanna get your opinion to know if this "trick" gonna cause me a headache in the future 😅 BTW AWESOME CHANNEL ❤❤
depending on what you mean by lost updates, you can get them even with the serializeable isolation level: the second transaction loses all updates done by concurrent ones. At least in h2 db, serializeable just guarantees that you won't observe rows added since your transaction started (so no more phantom reads). But you can still end up updating the database unaware of any changes made by other concurrent transactions. I don't know if there is any database that allows only one transaction at once which would be required to avoid this
well ok, the documentation says that their interpretation of serializeable doesn't actually guarantee that sequential execution of concurrent transactions gives the same result
Hey Hussein, Thanks for the great content. Can you make a video to give some idea about how Db Client and Server communicate like mySQL uses its own protocol mysql. and how they use TCP connection like do they create new TCP connection for each query similar to HTTP 1.0 or they use single TCP connection for entire Txn similar to HTTP 1.1 ?
Ashutosh Mishra hey Ashutosh, I think I did this with mongodb and postgres Wiresharking MongoDB - Decrypting TLS traffic, mongo protocol, cursors and more th-cam.com/video/naJC-yuCZb8/w-d-xo.html Wiresharking PostgreSQL - SELECT * FROM on Postgres behind the scenes th-cam.com/video/vjWt-PF_6tA/w-d-xo.html
Can you please use dark background in your future videos :) I usually watch your videos in the evening and the white background is really hard on my eyes. If it's causing you too much work then don't worry.
Get my Fundamentals of Database engineering udemy course, head to database.husseinnasser.com for a discount coupon
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
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
Thanks a ton for the gold standard content. I have a question though, In Atomicity, you mentioned if the $100 debited and then the DB crash event occurred, after restart it was $900 w/o credit update query, so when will the rollback occur? After the DB restarts as it seems like if DB crashes, what happened to the Tx state, is it lost or recovered after restart?
This is pure GOLD. Literally, one semester subject knowledge in less than an hour! Crazy-or-what :D
you are really a talented person,one of the best video's i have seen in my life :)
Thank you Sumeet, ! 🙏 I still think I have ways to go. We need to be able to keep an open mind and keep learning.
Best video on ACID properties. Thanks!
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.
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!
please make videos on indexing and normalization.
No way ! No way someone makes a video so good ! Thanks !
I enjoyed every bit of your lecture especially the lucid examples. Well done! I will be glued to your channel for a long time.
Thank you Aduonye!! Enjoy the content ❤️
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!
Thanks for the suggestion ❤️ great idea
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 :)
Why my University doesn't have a professor like you :( You are AMAZING! Thanks for the great content.
His accent is irritating
@@CricMicroscope your comment is irritating
wow really nice video, watched all and repeating many parts to understand better. More than one hour is gone! Really love the video!!
Thank you! Glad you are enjoying the content and happy you didn’t get bored 😅
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.
Fantastic explanation, I was finally able to understand the isolation level concept and hopefully grasp it. Thank you very much, I have failed the ACID question during several interviews, what eventually led me to your material. Keep up with the great work!
Glad it helps! I also go through more examples and more details on my fundamentals to database engineering udemy course.
Yes, I reached the end of the video. Thanks a ton.
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.
UTS？
You are a professional at teaching. Thank you !
You've got some amazing content here! Good mix of breadth and depth for the topic discussed especially when analyzing isolation. Looking forward to viewing some of your other videos in the playlist, particularly if there's anything comparing SQL and NoSQL databases.
cw Thanks cw Im glad you noticed because that is what Im doing, trying to go breadth and when I find the topic interesting or on demand I go deep. I am planning to do some more DB videos love those ... thanks for your comment enjoy the content!
@@hnasr thank u
@@hnasr I have a query. When you say versioning, do you mean MVCC? Also, I see that MVCC uses exclusive locks unlike 2PL (uses shared and exclusive) for updates and actually is more difficult to implement and have to address all the anomalies like handling edge cases. For instance, using MVCC, Oracle can only offer Snapshot Isolation, not Seralizable, which has Write Skews.
Then, does MVCC still stop Phantom Reads and how?
Liked your clean examples and Hollywood' style of teaching
This is incredible. I have watched so many of your videos in just few days. Hope you keep making these great content.
Best resource to understand acid tranction
I wish you were my uni professor 😂
The best ever tutorial on such a complex topic. Thanks 🙏
Really Useful Video.
Thank You Hussein :)
Never knew these important things about database. Thanks for such a great explanation !! 😇
This video literally cleared my fundamentals, have read about this in college, didn't get it clearly understood.
Thank you @hussein nasser
Amazing piece of content bro.
Love that you bring examples and use-cases for each topic, because without a problem those don't worth a whole.
Hoping to more see videos like this one from you :)
His accent is irritating
Paid 6k to UNI and jump back here to get insight into it lol.....Thi is what it is..Thanks for the great video.
This is just Gold mate.
enjoy!
Great video! You explained a deeply complex topic really nicely. Thank you.
Awsome.. the way you explain things is awsome..👍👍
Good one. I was unable to get much on lost updates concept here.
Great way of explaining complex things
This is quite exceptional!!
Thank you soo much!!
Reached the end 🎉 .. Thanks for the great content ❤️
Thanks, men Thanks a lot for such type of content. this is really appreciated. JazakAllah
Great video!! I was looking for something like that
Your videos are amazing, great content and marvelous arrangement, thank you very much!
Thank you very much!
Thank you so much Hussein!
Great class!
thanks for sharing Hussein !
It's a golden channel
Finally understood what ACID is
Hats off to you.
God bless you
Hey, thanks man! Really liked the video made allot clear.
you re awesome ! it helps me understand DB transaction easily
Hussein, you are amazing
really really really clear explained and helpful！
Happy to hear that!
thats how we teach 💯
Hi hussain. The explanation is clear. Can you make video on how does a consistency maintained in distributed system among the multiple microservies & I really can't find a good content on how to build our small own database to understand the working of relational database.
True story im watching video when I suddenly had Acid reflux. :( great contenr as always
Ohh get well soon ! Take those nexum
Boss level stuff!!! To the point.
Thank you so much! amazing video!
Nice video and explanation, agree with all except - traditional OLTP databases provides eventual consistency, I dont think any banking system will go for eventual consistency, though postgres has both syncronous(will provicde consistency with perfromancer compromise) and async replciation, but I think thats mostly for HA purpose for banking\critical systems, with these days everything running on VMs, the critical databases are mostly vertically scaled periodically and async sharding/replication is usually used for non critical reads like reporting etc never for banking\critical transactions, so we have no option but to live with these 70s RDBMS systems for some core businesses which require ACID to be stricltly implemeneted.
very good video, the only thing I would like clarified is if there is a reason to store the image in the db as blob as in the example.
The best. As usual. Thank you
Thanks!!!
This is a very good video, thank you:)
I am looking at your videos for over a year, I will love if you touch Storage and big data processing also !! ETL/MapReuce/Hadoop !!
phantom read and non-repeatable read looks the same to me, what am I missing?
Okay so in case of non-repeatable we were updating the same row, and that was the cause of issue and in case of phantom read we created a new row which was the cause of the issue
thank you for the amazing content. I have already bought your database course planning to watch that too.
Hey man. Love your video but i am confused about dirty read example. You have mentioned that Transaction was Updated but not committed. How can the other session read different value other than what is in Database. Well unless it is committed it will read that updated value otherwise it will still read the value that is in database. Does it make sense?
it means that the read transactions when in read uncommitted isolation level (sql server for example) are configured to read the latest row change wither this is committed or not. this could be in a different data structure usually called the WAL (write ahead log)
what you described is a read committed isolation level where we read the value stored in the row and committed.
@@hnasr thanks for quick reply. Thanks for explaining this to me. I always thought one can read updated value from database after committing, silly me. Always learn something new. Thanks again
Amazing content. thanks!
Awesome! can you make video to explain difference between SQL and NoSQL ?
33:26 I remember the time when on TH-cam we could see the "exact" number of subscribers and how I refreshed the page after several seconds on the channels with millions subscribers and this number chaged.
Now I understand why they moved from this way and started just showing "5M" for example
Thank you for this video.
Thank you so much for the video
Can you please make a video on MongoDB architecture
I have a question, do the changes made by a transaction before being commited or rolled back performaed directly on the database (on disk) or each transaction performs the changes on its own isolated memory ?
awesome video
This is hilarious, thanks. Very helpful.
Thanks for your great content :)
thanks! It's super nice and clear :)
- Transaction: A bunch of queries
- Atomicity: Either all the queries in a transaction must commit, or all of them must rollback
- Isolation: is about isolating transactions to handle concurrency issues arising from concurrent transactions. How: versions and locks(shared/read) in db level, locks in application level.
Problems = Dirty read, Non Repeatable read, Phantom read, Lost update.
Solutions = Isolation levels like Read uncommitted, Read committed, Repeatable read, Serializable
- Consistency: Is about Consistent data and Consistent read.
Consistent data: Happens within a machine due to non-atomicity, non-isolation etc
Consistent read: happens across machines, in both sql and nosql dbs when replicas are present. You update a record, but when you read after, you get old data. It is always eventual consistency when replocas are present
- Durability: committed transactions must be persisted in a durable storage
awesome content! Keep it up!
Will do! Thanks for your comment 🙏
i love you so much bc you are so funny and helpful
my cat freaked out when you changed your tone lol
Hằng 😂 sorry little kitty 😍
Great stuff
Talking about isolation i kind of run into a weird issue while using Repeatable read level and locks
If you have two transactions running at the same time and you use lock for update (select for update) to get the record (let's say wallet) on t1 (first transaction) and you update the balance column value from 10 to 20 and then it's committed, now since you used lock for update that means t2 was waiting for the wallet record to be released and now it can continue after t1 committed
On t2 if you get select the wallet record normally (without using lock for update) you'll get the balance 10 because both transactions were running at same time and it makes sense because we're using Repeatable read isolation level
BUT if you get the wallet using lock for update on t2 it gives you the balance 20 😅
Does the locks have more priority over isolation? Is this a normal behaviour ?
Because I'm using this trick to have a general isolation level of Repeatable read but in some cases i want to get the committed record even if it was committed during the transaction not before it
So it's like having two isolation levels , the general Repeatable read and the Read committed but only on records that's i chose using lock for update .
Wanna get your opinion to know if this "trick" gonna cause me a headache in the future 😅
BTW AWESOME CHANNEL ❤❤
now u are my tech lead 😂😂😂
can you share the slides used in the video? It would be very useful as a reference. Thanks for the detailed explanation on ACID.
Thank you brother
I was asked in an Interview How durability is internally achieved in postgres (something like when data from WAL is persisted)
Thank you
🙏
Thanks for the great video 🙌! Can you share your slides? I want to use it as a reference!
great video!
depending on what you mean by lost updates, you can get them even with the serializeable isolation level: the second transaction loses all updates done by concurrent ones. At least in h2 db, serializeable just guarantees that you won't observe rows added since your transaction started (so no more phantom reads). But you can still end up updating the database unaware of any changes made by other concurrent transactions. I don't know if there is any database that allows only one transaction at once which would be required to avoid this
well ok, the documentation says that their interpretation of serializeable doesn't actually guarantee that sequential execution of concurrent transactions gives the same result
perfect more please
Thanks😊🎉
If i want to learn Database , Can i start with this playlist ?
Thank you Hussain ❤️
Yes, absolutely!
Hey Hussein, Thanks for the great content.
Can you make a video to give some idea about how Db Client and Server communicate like mySQL uses its own protocol mysql. and how they use TCP connection like do they create new TCP connection for each query similar to HTTP 1.0 or they use single TCP connection for entire Txn similar to HTTP 1.1 ?
Ashutosh Mishra hey Ashutosh, I think I did this with mongodb and postgres
Wiresharking MongoDB - Decrypting TLS traffic, mongo protocol, cursors and more
th-cam.com/video/naJC-yuCZb8/w-d-xo.html
Wiresharking PostgreSQL - SELECT * FROM on Postgres behind the scenes
th-cam.com/video/vjWt-PF_6tA/w-d-xo.html
@@hnasr Thanks.!
You can add those videos to database engineering playlist so that other people will find it in the same playlist.
thank you!
wow 🔥🔥
amazing
Can you make series on System Design?
Great idea! Will sure consider it
Thank you
Please make video on postgres WAL
👍👍 on my plan for the next video
From where do we get the slides of this course, once have joined as members?
You can download it from here! payhip.com/b/07an
This one is good 👍
Mert Ertuğrul 😍😍😄 let me know what should I make next .. cheers
Same as others. Hooked
Very educational and funny
Can you please use dark background in your future videos :) I usually watch your videos in the evening and the white background is really hard on my eyes. If it's causing you too much work then don't worry.
From where I can download the slides for this @hussein ?