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]

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

[@bhavanprajapati4773]

please make videos on indexing and normalization.

[@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

[@pratikmakune2881]

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?

[@justsaysharu]

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

[@hashem467]

I wish you were my uni professor 😂

[@jinendrakhabiya2646]

Best video on ACID properties. Thanks!

[@sumeetracharla9316]

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

[@hnasr]

Thank you Sumeet, ! 🙏 I still think I have ways to go. We need to be able to keep an open mind and keep learning.

[@ameyapatil1139]

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

[@pratiyushprakash6509]

This is incredible. I have watched so many of your videos in just few days. Hope you keep making these great content.

[@bs7448]

The best ever tutorial on such a complex topic. Thanks 🙏

[@parasarora5869]

Never knew these important things about database. Thanks for such a great explanation !! 😇

[@sagartyagi2450]

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.

[@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.

[@hnasr]

Thank you Aduonye!! Enjoy the content ❤️

[@luizdebem]

Thank you so much Hussein! Great class!

[@KenanCasey]

Great video! You explained a deeply complex topic really nicely. Thank you.

[@ary9344]

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 :)

[@emiliosoto6521]

You are a professional at teaching. Thank you !

[@devyetii]

Reached the end 🎉 .. Thanks for the great content ❤️

[@wh264]

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!

[@hnasr]

Thanks for the suggestion ❤️ great idea

[@fernard8985]

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!

[@hnasr]

Glad it helps! I also go through more examples and more details on my fundamentals to database engineering udemy course.

[@nagame859]

This is quite exceptional!! Thank you soo much!!

[@mohamedswialm7534]

thanks for sharing Hussein !

[@yarden2096]

Great video!! I was looking for something like that

[@ignacioherrera299]

Your videos are amazing, great content and marvelous arrangement, thank you very much!

[@hnasr]

Thank you very much!

[@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.

[@xiaotianyang6572]

UTS？

[@cw5948]

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.

[@hnasr]

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!

[@machaallahmariam3162]

@@hnasr thank u

[@amlanbiswas4526]

​@@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?

[@cymi2607]

Thank you so much! amazing video!

[@omarkamel9116]

Great way of explaining complex things

[@salmanbaig8088]

Liked your clean examples and Hollywood' style of teaching

[@jayjay7333]

Best resource to understand acid tranction

[@saoudahmedkhan2549]

Thanks, men Thanks a lot for such type of content. this is really appreciated. JazakAllah

[@benjaminkovackeber]

This is a very good video, thank you:)

[@vishalsrane]

Thank you for this video.

[@user-lo6ed2kp2s]

Amazing content. thanks!

[@QuanNguyen-xq1jo]

you re awesome ! it helps me understand DB transaction easily

[@danielschiffers9696]

Hey, thanks man! Really liked the video made allot clear.

[@InvincibleMan99]

Hats off to you. God bless you

[@mind77749]

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

[@hnasr]

Thank you! Glad you are enjoying the content and happy you didn’t get bored 😅

[@mtnrabi]

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 :)

[@Amitexpresses]

His accent is irritating

[@siddharthsrivastava3961]

Awsome.. the way you explain things is awsome..👍👍

[@dan-vw4ve]

awesome video

[@B-Billy]

Boss level stuff!!! To the point.

[@AbleToLiveHere]

The best. As usual. Thank you

[@hnasr]

Thanks!!!

[@chenjun4151]

really really really clear explained and helpful！

[@hnasr]

Happy to hear that!

[@abhishekbanerjee6140]

This is just Gold mate.

[@hnasr]

enjoy!

[@Sujankumarreddy]

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

[@Amitexpresses]

His accent is irritating

[@amlanbiswas4526]

@@Amitexpresses your comment is irritating

[@krishnasumanth007]

Good one. I was unable to get much on lost updates concept here.

[@ahmedsallam2868]

Thanks for your great content :)

[@alexchernenko4901]

thanks! It's super nice and clear :)

[@jiayuhe8659]

great video!

[@pankajkaushik913]

Great stuff

[@SachinDolta]

Finally understood what ACID is

[@chrisye2183]

Awesome! can you make video to explain difference between SQL and NoSQL ?

[@emmanuelonyebueke544]

Hussein, you are amazing

[@nero1375]

awesome content! Keep it up!

[@hnasr]

Will do! Thanks for your comment 🙏

[@dgen77]

Thank you brother

[@varunkumarsingh758]

Thank you 🙏

[@marralizakrs2247]

i love you so much bc you are so funny and helpful

[@user-EricLin0619]

It's a golden channel

[@emilmatei2258]

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.

[@raj_kundalia]

thank you!

[@asfar1984]

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 !!

[@dylanngo4454]

Thanks😊🎉

[@SoeaOu]

This is hilarious, thanks. Very helpful.

[@coderkashif]

amazing

[@section9999]

Awesome

[@neuodev]

Thanks for the great video 🙌! Can you share your slides? I want to use it as a reference!

[@saravanasai2391]

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.

[@malayrevanth5854]

can you share the slides used in the video? It would be very useful as a reference. Thanks for the detailed explanation on ACID.

[@ru2979]

wow 🔥🔥

[@prostyxi2771]

perfect more please

[@sridharramalingam329]

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.

[@mertyertugrul]

This one is good 👍

[@hnasr]

Mert Ertuğrul 😍😍😄 let me know what should I make next .. cheers

[@yumengsi]

Very educational and funny

[@kirillzlobin7135]

33:26 I remember the time when on KZbin 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

[@deeraj3069]

Thank you so much for the video Can you please make a video on MongoDB architecture

[@neerajcrespo]

I was asked in an Interview How durability is internally achieved in postgres (something like when data from WAL is persisted)

[@NepaliBiraj]

Paid 6k to UNI and jump back here to get insight into it lol.....Thi is what it is..Thanks for the great video.

[@rodmajuelojr]

True story im watching video when I suddenly had Acid reflux. :( great contenr as always

[@hnasr]

Ohh get well soon ! Take those nexum

[@fsl4faisal]

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.

[@paulfrischknecht3999]

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

[@paulfrischknecht3999]

well ok, the documentation says that their interpretation of serializeable doesn't actually guarantee that sequential execution of concurrent transactions gives the same result

[@aymaneharmaz7126]

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 ?

[@hemantsinghjadon849]

Same as others. Hooked

[@CODINC]

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?

[@hnasr]

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.

[@CODINC]

@@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

[@ashutoshmishra2328]

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 ?

[@hnasr]

Ashutosh Mishra hey Ashutosh, I think I did this with mongodb and postgres Wiresharking MongoDB - Decrypting TLS traffic, mongo protocol, cursors and more kzbin.info/www/bejne/pJKtdGCvqqiNmJo Wiresharking PostgreSQL - SELECT * FROM on Postgres behind the scenes kzbin.info/www/bejne/rJu6pWCGe8RpqqM

[@ashutoshmishra2328]

@@hnasr Thanks.! You can add those videos to database engineering playlist so that other people will find it in the same playlist.

[@davidlee588]

Could you help explain why ORM frameworks such as Rails's ActiveRecord and Django don't retry aborted transactions?

[@JynyChen]

now u are my tech lead 😂😂😂

[@mdsazidkhan4720]

From where I can download the slides for this @hussein ?

[@SaifUlIslam-di5xv]

Reached the end. (Y)

[@nitingupta1591]

From where do we get the slides of this course, once have joined as members?

[@hnasr]

You can download it from here! payhip.com/b/07an

[@siddharthsrivastava3961]

Please make video on postgres WAL

[@hnasr]

👍👍 on my plan for the next video

[@SanadTabari]

If i want to learn Database , Can i start with this playlist ? Thank you Hussain ❤️

[@hnasr]

Yes, absolutely!

[@AISynthetic]

Can you make series on System Design?

[@hnasr]

Great idea! Will sure consider it

[@AISynthetic]

Thank you

[@reloadfast]

You have a Khan Academy vibe going on here...

[@Ahm3dAshour]

ياريت لو تضيف ترجمة للفديوهات علشان الضعاف في الانجليزي يستفادوا برضه

[@pieicx]

my cat freaked out when you changed your tone lol

[@hnasr]

Hằng 😂 sorry little kitty 😍

[@TrikNgonlen]

A W E S O M E !

[@ruslanvolovik2745]

😱🤟💥

[@hnasr]

Ruslan Volovik 😍😍😍

[@salohiddinqutbiddinov6182]

👍

[@thescubabuba6470]

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.