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
www.postgresql.org/docs/13/mvcc.html
source.wiredtiger.com/develop/architecture.html
docs.microsoft.com/en-us/troubleshoot/sql/performance/resolve-blocking-problems-caused-lock-escalation
Become a Member on KZbin
kzbin.info/door/_ML5xP23TOWKUcc-oAE_Egjoin
🔥 Members Only Content
kzbin.info/aero/UUMO_ML5xP23TOWKUcc-oAE_Eg
Support my work on PayPal
bit.ly/33ENps4
🧑‍🏫 Courses I Teach
husseinnasser.com/courses
🏭 Backend Engineering Videos in Order
backend.husseinnasser.com
💾 Database Engineering Videos
kzbin.info/aero/PLQnljOFTspQXjD0HOzN7P2tgzu7scWpl2
🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast
Gears and tools used on the Channel (affiliates)
🖼️ Slides and Thumbnail Design
Canva
partner.canva.com/c/2766475/647168/10068
🎙️ Mic Gear
Shure SM7B Cardioid Dynamic Microphone
amzn.to/3o1NiBi
Cloudlifter
amzn.to/2RAeyLo
XLR cables
amzn.to/3tvMJRu
Focusrite Audio Interface
amzn.to/3f2vjGY
📷 Camera Gear
Canon M50 Mark II
amzn.to/3o2ed0c
Micro HDMI to HDMI
amzn.to/3uwCxK3
Video capture card
amzn.to/3f34pyD
AC Wall for constant power
amzn.to/3eueoxP
Stay Awesome,
Hussein

[@hnasr]

Check out my udemy Introduction to Database Engineering course husseinnasser.com/courses Learn the fundamentals of database systems to understand and build performant backend apps

[@nikolaymatrosov]

Hope you'll get some money even if I bought your course with the current huge discount. Thanks for the content.

[@tdotcode3783]

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

[@javadoctor101]

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

[@hnasr]

Exactly I think Microsoft might have introduced this confusing concept of an optimistic lock which is contradictory phrase..

[@andrebenedetti5641]

Fantastic video. Thanks Hussein

[@MikeNugget]

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

[@helenagarcia5103]

who cares this

[@MikeNugget]

@@helenagarcia5103 at least you

[@lakhveerchahal]

Or is it? 😅

[@martinzokov]

Not a coincidence. He made it happen

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

[@firex5250]

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

[@manojBadam]

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

[@larbisahli2273]

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.

[@IgorRoztr]

You also do select for update in a transaction, where you need it ;)

[@abdelhadisabani]

love u bro, thanks for all these amazing videos

[@hnasr]

❤️

[@soul67]

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

[@venkateshgunda25]

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

[@mosaidir7988]

شكرا لك يا حسين! استمر في التألق والإبداع! الجو حار هنا في المغرب، لذا فالمطر سينفعنا لتنخفض الحرارة، لذا فلا حاجة لمظلة :) أرجو أن تقدم لنا فيديو عن الفروقات بين SQL وNoSQL ولأي شيء تصلح كل منهما، ومتى يكون استعمال أحدهما أنسب؟

[@emadabushofa2379]

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.

[@suvankardas7932]

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

[@nikhilghodke674]

I just want to tell Hussein, you are just awesome 😍

[@avinashkolaparthi3890]

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

[@blueghost512]

Coffee hit hard at 15:00 😂

[@sanilkhurana3991]

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?

[@heetgorakhiya2242]

+1

[@hnasr]

Hey Sanil, while true concurrency control are on the advanced side of DB engineering but still can be practiced. You basically create two clients each has a transaction make an edit to. A table try to read it from another. And see the behavior This however require fundamental understanding of whats going on in the DB. I suggest following percona blog they do amazing advanced DB Stuff

[@sanilkhurana3991]

@@hnasr Thanks Hussein, tbh was looking at your course and wanted to understand how would I practice this stuff 😅. But yeah I will probably look more into it.

[@tikz.-3738]

@@sanilkhurana3991 in mongodb in schema options pass optimistic ConcurrencyControl: true Then create and save a document. Then find the same document twice And make some changes to first instance and save and then make changes to second instance and save. The second instance will throw error and fail since the first one modified the versioning. The way mongoose handles this is it says when saving find a document with this I'd and the versionNumber when the document was brought in application. Since the version in db has changed it will try to find and update a document with this I'd and old version number which will result in no document

[@lord12790]

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

[@TheHasanJr]

Hi Hussein, I had a question.. Should unique tables/keys be indexed? And why is Amazon Aurora faster than MySQL and Postgres?

[@jeffdavies2824]

Have you looked at distributed NoSQL database like Cassandra? Highly scalable, fast write times, and possibly a rather interesting topic for discussing consistency levels. I'm still getting my head around it.

[@severinspoerri3036]

Danke!

[@splytrz]

Can someone explain the memory problem with the pessimistic approach? If we have an entire function operating on a row, what is a problem with storing an integer? If we have a million functions operating at the same time that it's the functions that are going to cost a lot of memory, not an array (or even a set) of integers.

[@kamaukenn7101]

Bythe way what do you think about harper db.

[@lakhveerchahal]

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

[@ultiumlabs4899]

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?

[@randriamitsiryjessgabriel723]

Could you please talk about end to end encryption please

[@NikhilKumar-gw6kp]

Hello Hussein, be it optimistic or pessimistic control, in either if a user has already initiated the transaction and some other concurrent user tries updating some field in that same row, their transaction will fail, right? So wht i understand, i can be terribly wrong, that via optimistic mechanism we are only saving memory footprint by avoiding locks. Please correct me or guide me here. Thanks a lot for your videos.

[@hnasr]

Not entirely true, here is an example of pessimistic mode. Say we have two transactions tx1 and tx2 start at the same time. Tx1 updates a row and takes a lock on it, Tx2 tries to update the same row but can’t because of the lock, it then waits until the lock is released. Once the lock is released (tx1 commits) , tx2 immediately unblocks and updates the row normally. So no failures or retries required. Hope that helps

[@omarmuhtaseb256]

Hey Hussein, regardless of preferences, I don't believe that optimistic lock is applicable in all the cases. Especially, when there is some external service request integrated with the flow. For example, let's say we have a system that enable users to collect points, then redeem the points to some store gift card. However, the gift card vouchers system is external. In that case, we cannot rely on optimistic lock for preventing multiple redemption requests at the same time for the same user. What do you think?

[@hnasr]

Hey Omar, I don’t see why not? The second concurrent redemption of the same gift card will fail in an optimistic CC mode. If it was an other example you might be right, external systems might not have the ability to retry in case of an OCC failure

[@omarmuhtaseb256]

@@hnasr To explain more, let's say the user has 100 points, and he wants to redeem all his points to gift card. Your system is responsible for handling the user's points. The external service will create gift cards whenever it is requested, it has no knowledge of users's points. So, when there are two concurrent requests with optimistic lock, both requests will create gift cards, but when you want to set the new points balance of the user, the second request will fail. In that case, if there is no undo request for the external service, you will be creating two gift cards of 200 points worth where as the user has only 100 points.

[@FreePal334]

@@omarmuhtaseb256 if i may add one point here, in such case as u described above, the application level should handle the logic of preventing duplicated/invalid redemption. So this is a decision btw the possibility/probability of this case vs the performance gained from optimistic logic.

[@ashwinkumar3587]

I have this doubt for a long time even after finishing your Database course 😅 Which takes priority - Isolation level or locks? Or does enforcing a isolation level implements locks to handle the isolation levels. I'm just confused about the co-existence of isolation level and locks

[@hnasr]

Don’t get stuck on definitions, just know the purpose and usefulness for both. Isolation is a property of the transaction that defines how Isolated it is from changes made by other concurrent transactions. There are many levels from low isolation (read uncommitted) to high isolation (serialization ) Locks are used to prevent a change on a resource (table, page or a row) and “can” be used to achieve isolation.

[@donjohnny5571]

Hey Hussein! Please make a video on WebAssembly?

[@ishtiaquekhan1148]

It's funny how social tends are creeping into tech

[@giantbush4258]

Not a single line of code to illustrste the concepts i can imagine not coding in a 14 udemy course. 😂

[@h.jpouya4715]

همه زبانی حرف زدی غیر از فارسی. یه بار هم یه جمله فارسی بگو دل ما خوش بشه

[@hnasr]

چشم

[@h.jpouya4715]

@@hnasr چشمت بی بلا عزیز دل

[@masoudmj1124]

عالی بود 😄😄

[@moosegoose1282]

get to the point man