Should you go with an Optimistic or Pessimistic Concurrency Control Database?

MongoDB, Postgres, Microsoft SQL Server, or MySQL, or any other database manages concurrency control differently. There are two methods, pessimistic and optimistic, both have their pros and cons. Let explore how different databases implement this and what is the effect on performance/scalability.

This is often known as Optimistic vs pessimistic locking. Although I don't really like to use locking with this because it confuses the story.

0:00 Intro
2:20 What is Concurrency Control
6:00 Pessimistic Concurrency Control
14:50 Optimistic Concurrency Control

Resources

Become a Member on YouTube

🔥 Members Only Content

Support my work on PayPal

🧑‍🏫 Courses I Teach

🏭 Backend Engineering Videos in Order

💾 Database Engineering Videos

🎙️Listen to the Backend Engineering Podcast

Gears and tools used on the Channel (affiliates)

🖼️ Slides and Thumbnail Design
Canva

🎙️ Mic Gear
Shure SM7B Cardioid Dynamic Microphone

Cloudlifter

XLR cables

Focusrite Audio Interface

📷 Camera Gear
Canon M50 Mark II

Micro HDMI to HDMI

Video capture card

AC Wall for constant power

Stay Awesome,
Hussein

[hnasr]

Check out my udemy Introduction to Database Engineering course

Learn the fundamentals of database systems to understand and build performant backend apps

[tdotcode]

I want to mention that I really enjoy your talks! I have learned so much!

[javadoctor]

You are such a gem. There are very few youtubers who have such deep knowledge of computer science. Rest of the youtube is all about building website, apps, react and making twitter clones. I really love your content. Keep it up!

[krozaine]

Thank you for the video! I was having a discussion on exactly this topic just yesterday. Guess I need to re-visit (revise) some of your old videos again and concepts around levels of Isolation and Multiversion Concurrency Control.
Also, when talking about optimistic concurrency control in the context of databases, I always like to explicitly mention that this is not something that a database engines implements/provides. The Optimistic lock (which is not a lock, as you had mentioned in some earlier video) is something that gets handled at application level :)

[AkshanshGusain]

I recently completed your Fundamentals of database engineering course on Udemy, immensely valuable.
I discovered things I didn't even know I don't know.

[MikeNugget]

What a coincidence, when he said rain it started raining right outside my window :D

[larbisahli]

In PostgreSQL to prevent the double-spending problem in my transactions, I use isolation level Serializable. Thanks Hussein for the awesome Introduction to Database Engineering course.

[venkateshgunda]

It'd be wonderful to work with Nasser. The way he describes these topics in detail makes me even more interested in these topics. Beautiful

[mosaidir]

شكرا لك يا حسين! استمر في التألق والإبداع!

الجو حار هنا في المغرب، لذا فالمطر سينفعنا لتنخفض الحرارة، لذا فلا حاجة لمظلة :)

أرجو أن تقدم لنا فيديو عن الفروقات بين SQL وNoSQL ولأي شيء تصلح كل منهما، ومتى يكون استعمال أحدهما أنسب؟

[firex]

I really appreciate your content, thanks for making me a better software engineer!

[abdelhadisabani]

love u bro, thanks for all these amazing videos

[soul]

Well your french and japanese are perfect!! Great video as usual.

[suvankardas]

I wish I could be in touch with u in my college days ...I would have score a bit more...honestly you are a great teacher...love from India

[emadabushofa]

We are using optimistic control in our event sourced system, events need to be sequential, so when adding 2 events with the same sequence it will throw an exception that we will catch then retry the command again.

We can't just lock the table to control all the rows, the system will stop responding badly.

[sanilkhurana]

Hey Hussein, really nice videos, just one question, any ideas on how can I practice these concepts or validate my knowledge? For simple stuff, it is easy to build projects, but if I want to practice concepts like having huge scale on a DB, it is a little more difficult. Are there any certifications that I should consider for this stuff as well?

[manojBadam]

great video, i was under impression that optimistic and pessimistic locks are coming from ORM, thanks for clarifying.

[ultiumlabs]

So, I as a DB user don't have to do anything because each DB has its own choice? I mean what's each DB choice have to do with me?

[lakhveerchahal]

Yes, Mac Pro supports upto 1.5tb of memory/RAM.

[avinashkolaparthi]

@hnasr In postgres, 'select for update' aquire row level locking it seems, and it says there will be a disk write to mark the row as locked. How can we get the locked information, which table stores these?

[lord]

Thanks for another insightful video. Can you make videos on CockroachDB, YugaByte, HarperDB and Cassandra/ScyllaDB?