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
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!
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.
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 :)
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.
شكرا لك يا حسين! استمر في التألق والإبداع! الجو حار هنا في المغرب، لذا فالمطر سينفعنا لتنخفض الحرارة، لذا فلا حاجة لمظلة :) أرجو أن تقدم لنا فيديو عن الفروقات بين SQL وNoSQL ولأي شيء تصلح كل منهما، ومتى يكون استعمال أحدهما أنسب؟
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?
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
@@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.
@@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
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.
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.
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?
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
@@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.
@@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.
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.
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
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.
@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?
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.
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