[Backend #9] Understand isolation levels & read phenomena in MySQL & PostgreSQL via examples

[@adilkhatri7475]

BY FAR THE BEST VIDEO ON ISOLATION I HAVE EVER SEEN.

[@B-Billy]

OMG, this is the perfect session, covering two major DBs, on the Isolation level. Subscribed. Loved it.

[@TECHSCHOOLGURU]

Awesome, thank you!

[@Sensibles93]

This is so far the best description of isolation levels based on real dbs. Thanks!

[@AbhishekVaid]

This is one of THE BEST tech learning channels on KZbin. Period !

[@TECHSCHOOLGURU]

Appreciate that!

[@nuttaphonr.6498]

Thanks again TECH SCHOOL ! MySQL Part 7:02 dirty read 9:04 non repeatable read 9:30 phantom read, same query but result miss 1 row 11:31 repeatble read prevent phantom read 12:55 move to highest isolation level 13:36 select is blocking, mysql convert plain select to select for share 14:42 deadlock occured

[@TECHSCHOOLGURU]

Thanks!

[@TheSalrec]

this is the best video I've found explaining the isolation levels in practice!

[@flatmapper]

Thank you for such a great content. It would be great if you could make another video showing how transactions with DIFFERENT isolation levels interfere with each other. That is how transactions will interfere when you set DIFFERENT isolation levels in console windows. I believe it will clearly show what really influence the occurrence of read phenomenas

[@abhilashpadmanabhan6096]

The way you tried every scenario in terms of Serialization anomaly coming back to MySQL. Thats the level of detail we all need, appreciate it! Thanks for the video. A suggestion would be to do the comparison on Oracle as well. :)

[@TECHSCHOOLGURU]

Thanks!

[@fserzz7993]

Thank you for the best isolation lecture!

[@ibmspringsprings3485]

First of all, thank you TECH SCHOOL had a public an excellent Explanation and Demonstrate about isolation levels & read phenomena and comparison between MySQL and Postgres of how its does it works on both MySQL and Postgres, thank you so much.

[@TECHSCHOOLGURU]

Thank you!

[@oduber.vasquez]

I am carrying out a project for a public institution, where several users have access to a game and from that game they transfer money to others and sometimes the balances are not correct! This gives me an idea of how to deal with these transfers or transactions. Thank you for your contribution!

[@TECHSCHOOLGURU]

Great to hear, Oduber!

[@nadezhdaburunova4880]

I finally understood isolation levels! And I love clear examples you provided. Subscribed, thank you

[@TECHSCHOOLGURU]

Awesome, thank you!

[@dmitrybunin9720]

Great walkthrough, simple and clear breakdown. Thanks!

[@simonpermeke2303]

This is one of the best courses on the interent :)

[@TECHSCHOOLGURU]

Thank you!

[@artemgoncharenko646]

Fully agree. The best explanation I've found on the internet.

[@artemcodes]

Thank you so much for this tutorial. It was extremely useful!

[@rained23JMTi]

luckily I found your video, I think your video is the best at explaining phenomena and isolation. Kudos.

[@TECHSCHOOLGURU]

Thanks a lot! Great to hear!

[@ngodinhloc]

Excellent !!! Waiting for a tutorial about retrying when an error occurred(timeout or deadlock). Thank you so much !!!!

[@eugene4640]

I wish I could like this twice

[@TECHSCHOOLGURU]

Thanks! Please share it with your friends and colleagues :)

[@nic_3005]

Thank you - Perfect Examples

[@Goldenhand76]

Fantastic , Priceless informations that cant find anywhere simple and exemplified like this!!!

[@anassbenhalima7004]

bro your content is great and so helpful . i hope it was totally free

[@daicaphong84]

Thank you, Quang ;) It's easy to understand. Wish you have good health and keep your passion

[@TECHSCHOOLGURU]

Thank you, Phong!

[@changchenchien4515]

Perfectly explained ! Thanks ! It helps a lot !

[@TECHSCHOOLGURU]

Glad to hear that!

[@valentineejk]

please do a video on implementing the highest level of isolation and retry mechanism

[@sks1717]

Seriously, It's an awesome video..Thank you so much for uploading it. Looking forward to more such videos. Could you please make a video on MYSQL Locking process?

[@chiblackjesus]

Just what i needed, you're a life saver

[@MuhammadDaif]

That was awesome, thank you !

[@alexandersmirnov4274]

after reading theory your examples the best!

[@TECHSCHOOLGURU]

thank you!

[@viethungha203]

Thanks so much for this! Very detailed and understandable explanation.

[@TECHSCHOOLGURU]

Glad you enjoyed it!

[@davidontiveros2895]

Have you had production issues due to isolation levels ? How have you solve them? You’re courses are gold by the way.

[@TECHSCHOOLGURU]

Thanks, David. I didn't have any issues on production due to isolation levels before.

[@alexismunier-pugin7070]

Golden content!

[@RahdixCloudNine]

Best of the best of the best! SUBSCRIBED!

[@TECHSCHOOLGURU]

Thank you!

[@RohitSingh-ITProfessional]

Awesome - you made it very easy - kudos

[@TECHSCHOOLGURU]

Thank you! Cheers!

[@alexandervasilenko7773]

As usual - excellent! Thanx a lot!

[@TECHSCHOOLGURU]

Thank you, Alex!

[@MustafaAlp38]

You videos are so great! Subscribed. Keep up to good work 👍🏼

[@TECHSCHOOLGURU]

Thank you! Will do!

[@Haniryion]

This is such a well made and easy to understand video. Thx a lot!

[@TECHSCHOOLGURU]

Thank you!

[@haphamdev]

Great explanation. Thank you very much.

[@TECHSCHOOLGURU]

Glad you liked it

[@michaelNguyen914]

Great course, very easy to understand. Thank you and best wishes to you.

[@TECHSCHOOLGURU]

Thanks, you too!

[@nesreenmohd665]

Very helpful. Thank very much!

[@jarriabidi1438]

Great content as usual

[@TECHSCHOOLGURU]

Glad you think so!

[@lakemanr]

Great explanation! Thanks!

[@icodevpi]

Love your channel, great logic in your backend courses. Please do some more parts in advaced Java EE.

[@TECHSCHOOLGURU]

Thanks, I will think about it!

[@ВасяФорточкин-х3я]

So great video! Thanks a lot!!!

[@TECHSCHOOLGURU]

Thanks!

[@MorbidPasta]

you're the 🐐, working to be you one day

[@zurumogbonda3225]

Awesome explanation, Thanks alot

[@TECHSCHOOLGURU]

Glad you liked it!

[@dokoltsov]

Thank you! Very nice explanation)

[@TECHSCHOOLGURU]

Glad you enjoyed it!

[@phamhoangvien8683]

It's easy to understand. Thanks a lot!

[@TECHSCHOOLGURU]

Glad to hear that!

[@염종철-u1u]

좋은 강의 감사합니다! 훌륭하네요

[@TECHSCHOOLGURU]

감사합니다!

[@nhattrungvodai2525]

I have learned a lot from this course.Thank you so much

[@TECHSCHOOLGURU]

Happy to hear that!

[@atomiccoding]

Great explanation!!!!

[@TECHSCHOOLGURU]

Thanks! Glad you think so!

[@Anup00211]

Great content. Keep doing great work.

[@TECHSCHOOLGURU]

Thank you! Will do!

[@TheVitkuz]

Amazing tutoial. Tnaks!

[@oduber.vasquez]

Excellent explanation thank you very much!

[@TECHSCHOOLGURU]

Glad it was helpful!

[@truongkimson]

At @11:34 I think you meant non repeatable read is prevented and not phantom read. Phantom read is applicable in the case of INSERT/DELETE, not UPDATES

[@TECHSCHOOLGURU]

No, I meant phantom read. Phantom read occurs when the same READ query is executed but different set of rows is returned (due to other transaction committed in between that alter the data). It's different from Non-repeatable read, which only involves 1 row. You're right about the INSERT/DELETE made by other transaction, which might add new rows or delete existing rows that the current transaction is looking for. But UPDATE query can also alter the data, which makes some new rows satisfy the searching condition of current transaction's query (so new rows appear), or makes some previously satisfied rows to not satisfy the searching condition any more (so less rows will be returned). As you see in the video, the transaction SELECT * FROM accounts WHERE balance >= 80; So it's possible that other transactions do some UPDATE queries that make some of the accounts' balance become smaller than 80, or greater than 80, which changes the list of rows return in the SELECT query. Hope that's clear to you. If you still have doubts, feel free to join Tech School's discord server to chat directly with me and other students: discord.com/invite/BksFFXu

[@hamidrayatmoghadam9954]

Thanks a lot. It was a perfect explanation!

[@TECHSCHOOLGURU]

Glad you liked it!

[@ZzBiazZ]

Cám ơn anh nhiều nhé, khoá học quá hay !

[@TECHSCHOOLGURU]

Thanks em!

[@vankshubansal6495]

If we insert a new row in repeatable read for both MySQL & PostgreSQL, we should get Phantom Reads, right?

[@HelloWorld-tn1tl]

This is gold !!!

[@TECHSCHOOLGURU]

Thanks!

[@gleamgreen]

beautiful!

[@jayanthbondi4251]

Thanks for this wonderful tutorial :)

[@TECHSCHOOLGURU]

Thank you!

[@babanadopu]

Extremely useful lesson!

[@TECHSCHOOLGURU]

Thank you!

[@arturmostowiak9427]

Good job.

[@TECHSCHOOLGURU]

Thanks!

[@MrVitalirapalis]

wow very good explained

[@TECHSCHOOLGURU]

Glad you think so!

[@milad9900]

Thanks

[@TECHSCHOOLGURU]

Welcome!

[@АйбатАманбайұлы]

Awesome

[@ngocvo9761]

good works!

[@TECHSCHOOLGURU]

Thanks!

[@ihebomri1529]

You saved me thanks

[@TECHSCHOOLGURU]

Happy to help

[@idirdev3729]

that was a great explanation, thank you

[@TECHSCHOOLGURU]

Glad you enjoyed it, Idir!

[@jcolindres001]

Hi Sir, Excellent trainings. Do you know if there is a front end framework written in go to develop very interactive we apps? Thanks, JA

[@bobnich]

thanks :)

[@Valem0r]

At 14:38 i dont understand why a deadlock occured. T2 selected the row first, so it acquired the lock. Makes sense that T1 would then wait because row is locked. However it does not make sense T2 got an error when trying to update.. since it had the lock on that row.

[@adampatterson]

This was super helpful, is there a reason why you wouldn't always use serialize isolation? When would it be ideal to read the old or outdated information?

[@TECHSCHOOLGURU]

Hi Adam! Good question! I think it's a matter of trade-offs between high consistency vs high performance. If we always use serialise level, we will achieve high consistency, but lower performance compared to other lower levels.

[@toanta4838]

in conclusion, in postgresql they still allow us to insert into table ?

[@ernestodiazsamame4731]

Excellent, migrations using liquibase in go

[@TECHSCHOOLGURU]

Thanks!

[@nameLastname786]

best

[@HoD999x]

i don't get why the insert fails at 24:40. why don't i simply get a duplicate insert? the db shouldn't be aware that the hard coded 810 would have been different had the transactions been sequential. i tried to reproduce this with the insert only and there is no error. (postgres)

[@laurentcharles7179]

What happen if session 1 uses read uncommited and session 2 uses serialization?

[@wizzard211]

Postgres no longer allows "serialization anomaly" during "repeatable read " when inserting new row - now it behaves exactly like mysql, it blocks the thread

[@wizzard211]

actually same thing happens during "serializable" isolation level, the thread is blocked.

[@gyuyoungpark5509]

Thank you for the great lecture. But I have one question. I don't know why the select query of transaction2 is blocked at 26:15. If mysql is in serializable level, isn't the only write operation blocked but the read not blocked?

[@TECHSCHOOLGURU]

Hi Gyuyoung, in serializable level, a read operation can also be blocked if there's other transaction that is still writing the data to the table (which can cause some anomalies for the reading transaction)

[@jambaa-yt]

Likee :)

[@buacomgiadinh1]

Video is uncompleted

[@TECHSCHOOLGURU]

Hey Ha, it is completed. I've just tried premiere mode so it synchronized the watch time of everyone. Now it's back to normal. Can you try to reload?

[@buacomgiadinh1]

TECH SCHOOL yeah it works

[@AsherEveren]

Great video! Thank you!