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

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

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

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

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

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

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

[@mosaidir7988]

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

[@lakhveerchahal]

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

[@firex5250]

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

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

[@soul67]

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

[@abdelhadisabani]

love u bro, thanks for all these amazing videos

[@hnasr]

❤️

[@manojBadam]

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

[@andrebenedetti5641]

Fantastic video. Thanks Hussein

[@mhadi-dev]

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

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

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

[@randriamitsiryjessgabriel723]

Could you please talk about end to end encryption please

[@nikhilghodke674]

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

[@lord12790]

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

[@kamaukenn7101]

Bythe way what do you think about harper db.

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

[@TheHasanJr]

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

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

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

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

[@severinspoerri3036]

Danke!

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

[@ishtiaquekhan1148]

It's funny how social tends are creeping into tech

[@donjohnny5571]

Hey Hussein! Please make a video on WebAssembly?

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