Hey everyone, welcome back. In this video, part 7 of topic 6 in our database class, I'm going to describe the difference between optimistic and pessimistic locking. Alright, so now that we've reviewed conceptually what locking is all about, let's take a look at the two major types of locking strategies, and they are optimistic and pessimistic locking. Now, I'll start with pessimistic locking because this is the simplest to understand. And the idea here, the reason that we call this pessimistic locking is because we are going to assume going in that it's likely that some other user will want to work with or update the same data that we want to work with at the same time that we want to work with those data. Okay, so it's got pessimistic view, like we're expecting, we're saying it's pretty likely that if someone else is going to want to use these data or access these data, well, I'm working with it. So that's where the pessimism comes in. It's just negative outlook like, hey, I have to plan for things to go badly because I think it's likely that they will. Okay, so this is what pessimistic locking, that's kind of the philosophy behind it. Now, here's what happens with pessimistic locking, the series of steps. The first thing that we do is the database will lock whatever resources we need. That is, we are granted exclusive access to those resources, and then we can do whatever we need to do, right? So we read the data that are locked, we can process other steps in our transaction. So maybe we do some updates, maybe we do some inserts, maybe we do some deletes, whatever it is, we change the data in the database. And then ultimately, we commit those changes, which if you remember is the database terminology for saving the proposed changes, we make them a permanent part of the database, just if you were editing a document and Microsoft Word, right, it's not, it doesn't become a permanent part of the document until you click on save. Same thing here, right? So we propose some changes, maybe during this step, maybe some updates, deletes, inserts, whatever it may be, and then we make those changes permanent at which time the locks on whatever resources we were using are released, and then somebody else can use those resources necessary, okay? So you can see that with the pessimistic locking strategy from the outset, we are assuming that a conflict will occur. So to prevent that conflict, we lock the resources that we need at the beginning, we do everything we need to do and save our changes, and then we unlock them. Now keep in mind that while these resources are locked, no one else can use it, they can't even read them. So they have to wait. And when people are waiting, when users are waiting, it's essentially a lowers the performance of the database, right? The database constantly has to react to user requests, and we want to be able to handle those user requests in a timely fashion. So if we have this pessimistic locking strategy in place, and resources are always locked, it may slow down the overall rate at which the database can process user requests. So we have to consider that, right? We're definitely going to protect all of the data, but people may need to wait longer. Now in the broader, the bigger picture of things, if we can afford to do so, we may be able to counterbalance that degradation in performance by something like purchasing better hardware. All right, so if I have a faster database server, better CPU, more memory, faster disks, et cetera, I may be able to counterbalance whatever decline in performance I'm getting by implementing a pessimistic locking strategy. But there is another way, right? We could try an optimistic locking strategy. Now optimistic locking, as the name suggests in contrast to pessimistic locking, is we are optimistic that a conflict will not occur. That is, we're feeling pretty good that, hey, things are going to go exactly as we need them to go. No one else is, it's very unlikely that someone else will want to use the same data that I'm using at the same time. And if we feel that based on the nature of our data, the usage that's out there, if we feel that that's true, optimistic locking can deliver some higher level of performance. That is the throughput, right? So the rate at which the database is able to process transactions will be faster if our belief about optimistic locking is true. Where remember that belief here is that a conflict will not occur, right? We're optimistic, we have this positive view of the world, there's not going to be any conflict. And in this case, it just means that, hey, no one else is going to be touching the same data that we're using at the same time that we're using that. So if we choose the optimistic approach, we will usually get better performance out of the database given the same hard work configuration and the same number of users. However, it only will deliver that performance increase in situations where the probability or the chances of a resource conflict occurring are low. So let's see what happens here. It's a different philosophy, right? So we're positive, you'll notice that instead of the first thing that we do is locking resources as we do over here, we just start doing our work. So we read the data, we make whatever sort of changes we need to do in our transaction, right? Inserts, deletes, maybe some updates, so we just do whatever sort of changes to the database we need to make. And then we look to see if things are as we would expect them to be. So we look to see if our proposed changes match what we would expect, we want them to be consistent with our expectations. So we look to see if a conflict occurred. And if no conflict occurred, that's great. What we'll do is we'll commit the transaction. Otherwise if a conflict did occur, and in this case, a conflict occurs when somebody else has changed the data that we're working with before we were able to commit those changes. So if a conflict does occur, then we have to roll back our changes and repeat. So instead of committing them, what essentially we do is we go back up here and we start over because something happened before we were able to save our changes. Somebody else altered something, like maybe using our previous example of two users trying to modify the same inventory value at the same time. Maybe when we looked to see if things were as we would expect them to be, we discovered that they were not. So what do we do? Well, we can't save our changes. Instead, we start over. And so we would go and we would do these steps again. We would read the data, process the same things that we were doing before, whatever reads, updates, deletes, et cetera. And then we look for conflict. And again, if a conflict occurred, then we start over again. And we just keep doing that over and over and over again until no conflict occurs, at which point we can commit the changes that is we make those changes per minute part of the database and then our transaction has successfully completed. So you'll notice that there's this kind of loop built in here when this happens. If a conflict occurs, we have to loop back and do it again. And then if a conflict has occurred again, we loop back and do it again. So you can see how the assumption that we're making here is critical to determining if optimistic locking will or will not actually deliver a performance improvement for us. Our assumption is that a conflict will not occur. And as you can see, if a conflict does not occur, this is very efficient. We're just doing these steps. There's no locking involved. Read process update. You don't have to lock and unlock resources and other people that may need to read the same data at the same time will be allowed to because the data aren't locked. So in this case, our lock would occur here. We would issue a lock here, commit the changes, release the lock there, and then the transaction would be complete. However, if our basic assumption about optimistic locking is false, that is, if conflicts are likely to occur, then we get stuck potentially in this loop, right? Where our transaction may fail several times consecutively. We just go around and around and around because these conflicts continue to occur. So our assumption about whether or not conflicts are likely is critical to our choice of optimistic locking versus pessimistic locking. And I can tell you this just as a piece of friendly advice that may help you to make some decisions here. So database primarily uses reads. That is, if we have lots of selects, we're just reading data from the database, right? And we only have a few changes, like inserts, updates or deletes, then hopefully it makes sense that optimistic locking would be a good strategy because primarily what we're doing is reading data and we don't have very many changes that happen to the database. So because we don't have very many changes, it's unlikely that a conflict will occur. So if you have a database where you have lots and lots and lots of just reads, lots of selects, then that might be very, very reasonable to choose an optimistic locking strategy because when an update is required, it's going to be a comparatively rare thing. So how about some practical examples of this? I don't know. Think about something like Wikipedia. So Wikipedia, right? It's open source database. And if you think about the volume of user transactions on Wikipedia, remember all of their articles are stored out there in a database, in their case, a MySQL database. But I think if you contemplate this, you will agree that most of the interactions with the database are going to be selects. Most of the time, people are just reading articles. So to read an article that is to render it to display it in the user's web browser, we need to select the article, the article title, all the images and stuff like that from the database, and we just display them on the screen. So lots of select operations going on there. Now people can update Wikipedia articles. They can add new articles, which would be like an insert. They can update existing articles, or if they have appropriate privileges, they may be able to delete articles. But in comparison to the number of people who are reading articles, the number of modifications or new articles that are created on Wikipedia is relatively small. So in that kind of a data processing environment, we would be very smart to choose optimistic locking, because the probability of a conflict is relatively low. Now, let's contrast to that with pessimistic locking. Now pessimistic locking would be an obvious choice in situations where we have lots of changes being made to the data. So lots of inserts, updates and deletes. I could spell up the... So lots of changes being made to the database, and comparatively few selects. So if we have a database processing environment that is characterized by this type of user interactivity, where we have lots and lots of changes happening. But not a lot of reads, then we would probably want to use a pessimistic locking strategy. We don't want to have any conflicts. So I don't know, an example of this might be something like, I don't know, think of like a financial processing organization, right? If I'm processing lots of credit card transactions, I'm making lots and lots of changes to data. I'm adding new rows, maybe updating account balances, deleting very many records, because we want to keep a record of everything. But at least we're doing lots and lots of inserts and updates, and probably more of those, like a higher volume of those, than we are just reading data. So in a situation like that, pessimistic locking would probably be a better choice. Now, this is an important point. I want to make this point, and I want to emphasize that this is an important point. Regardless of which locking strategy you choose, right? You might choose optimistic locking, or you might choose pessimistic locking. It doesn't matter which one you choose, your transaction will be processed. Right? So either choice, the database will still work as a database should in a multi-user database environment. This is a question of performance here based on our expectations and our knowledge of the volume of different types of transactions that the databases of being asked to handle. So I hope you can understand here that in either case, the transaction will eventually succeed as long as there's nothing wrong, right? As long as it doesn't have an error in it, the transaction will eventually succeed if we choose pessimistic locking or optimistic locking. But we may be able to get some better performance over here if we're operating in a situation where we have lots of reads and few changes. So that's why these two different styles were created to accommodate different styles of database processing. Now, the reality, of course, in many, many real world scenarios is that the usage patterns for an organization's database might fall somewhere between these two extremes. So in that case, the choice is a little more difficult. What do we do? Well, we experiment. It's how it goes. So there's a permanent choice here. We can change the locking strategy for an entire database or for a particular table. And if we're unsure as to which we'll deliver the best performance for us, the easiest thing to do is just do an experiment. So if you're a database administrator, I don't know. If you choose pessimistic locking first and you let the database run for a week and you see like, what is its average time to service user requests? And then you switch it over to optimistic locking and let it run for a week and see what was its average time to service user requests during that week, whichever is better is the one that you choose. So let us do an experiment. There's no problem with that. Sometimes we just don't know, we don't know which will yield the better performance until we try it. It's one of the fun things about being a database administrator is you get to tinker with things. You're constantly trying to tune the database and get better and better performance based on how people are using it. But at the same time, it's a moving target because the way that people use the database slowly changes over time. If you think about, I don't know, pick a company like Microsoft, right? It has its internal databases. If you choose one of those and say, how was this database being used 10 years ago versus how it's being used today, you can imagine that the usage patterns have changed substantially over time. So the database administrator for that database needs to be aware of the slow pace of change and occasionally do these experiments to see if she can sneak out a little bit better performance, maybe by doing something like changing the locking strategy. Just some sort of pseudo code with some SQL here. We've got a transaction and we'll compare optimistic locking versus pessimistic locking. Remember in optimistic locking, we are optimistic that a conflict will not occur. Our positive view of the world, we're unlikely to have a conflict. In that case, we do not begin by locking our resources. We just start by reading. Here, we're getting some data. In this case, you can see we're fetching the, I don't know, one of the things we're interested in is the quantity of a product that we have available. We store that in some variable in our transaction. We're going to set our new quantity equal to whatever the product quantity is minus five. And then any other steps that we need to take, blah, blah, blah. And then we get to a point where we're going to issue a lock. So we issue the lock here after we've already done a bunch of work. This is work. And we've done all this work already. And then we issue a lock and we try to do an update. And note that our update is based on the condition that the current product quantity is equal to what it was up here when we first, when we were toward the beginning of our transaction. So if that is true, then this update will succeed. If that is false, this update will fail. Now, when would this be false? Well, it would be false if at some point between our fetching of the quantity and storing it here and our attempt to update the quantity here, right? Somebody else changed the quantity. So if a conflict occurred, it occurred between these two parts of our transaction. And in that case, this update will fail. Right? No records will be changed. And in that case, what do we do in optimistic? Well, we're going to have to loop back and start over again. All right? So around and around we go. And we'll go there. We'll work our way down through these various steps. And if it fails, we have to do it over and over and over again until eventually it succeeds. So basically we're doing some things in here. And if we're busy doing this stuff and somebody else changes the quantity for that product in the product table, then hey, this will no longer be true. And our update statement will fail. So that's optimistic blocking pessimistic, of course. Oh, I skipped over something. There we go. pessimistic locking, of course, is the assumption that something will go wrong. And so to just prevent a conflict, we begin by locking the resources that we need. So in this case, we're locking the product table at some level of granularity. Maybe it's for a particular row, right? So maybe we need it for the pencil product. And nobody else can access this pencil product while we're busy using it because we've locked it. So between this lock statement and this unlock statement down here, we're the only user that can read or work with pencils in this example. So we issue our lock. We do our work. All right. So here's all the work that we might do. Blah, blah, blah, blah. No one can work with pencils while we're busy doing this. We're done. We ultimately update our product that change is committed. And then we unlock it and note that in this case, in contrast to optimistic locking, there's no need to check if the update was successful because we know it was. Right. The, we had the resource exclusively locked during that time. So as long as there wasn't some sort of failure in the sense that maybe there's a disc failure or something went wrong. So where we don't need to see if the actual value was updated as we would expect it to be. So in the remember pessimistic, we're just assuming that a conflict is likely to occur. And we're going to take actions to prevent that conflict intentionally, even if ultimately it doesn't occur. And remember again that with pessimistic locking can potentially slow down the overall throughput of the database that is its ability to process all of the incoming the speed with which it can process. All of the incoming requests because we are always issuing locks. And if someone else needs to use these data at the same time, even if it's just a read, they're not changing anything. They're just reading values. They will not be able to. They'll have to wait. So you can multiply that over 1000s and 1000s of users. And you can see how it might slow things down a bit.