Hey everyone, welcome back to part six of topic six in our database class. In this video, I'm going to provide an introduction to resource locking. Now, the primary mechanism that we use to prevent these types of concurrency control problems is something called locking resource locking. So what we, the big idea with resource locking is that we are going to request or be granted by the database exclusive access to certain data in the database for a certain period of time. Typically, well, we are busy working on our transaction. Okay, so I can say, all right, database, I need to run this transaction. I'm going to be touching all of these data. So lock all of those things and don't let anybody else use it until I'm finished. Once I'm finished, we can unlock those data and then somebody else needs to use them. They're waiting in the queue, then they can go ahead and use those data. But while I'm working with, depending on what level of locking we're using, we're going to prevent any other users, any other transactions from reading, updating, inserting, deleting data that we are using to complete our transaction. So we're just going to try to grab exclusive access to it on a temporary basis and other requests to use the same data at the same time are just put in a holding state. They're just like, put in a waiting room. It's like, you have to wait there. You're in a queue. First come, first serve, unless we have some kind of priority structure, then once the current transaction that is using those data has completed all of its work, regardless of whether it succeeds or fails, once that transaction is complete, then those data will be unlocked and made available to other transactions for use. Now, we have two different types of locks that can be issued by the database, and these are implicit locks and explicit locks. Now, the difference between them is whether we have to request it. The database will issue implicit locks automatically. So all enterprise level databases have these. So if you and I are trying to use the same database at the same time, or the same data in the database at the same time, we're not necessarily going to have to request to lock the data. The database on its own is smart enough to look at this and be like, wow, these two users are trying to use the same data at the same time. So I've got to issue a lock to one of them. And the other one will just be put in a waiting room temporarily until that lock is released. So these are implicit locks are handled automatically by the DBMS. We don't have to do anything individually as users. In our transactions, we don't have to request exclusive access to system data. They're issued automatically by the database while the transaction is still alive. Explicit locks are our other type of lock. And these are requested intentionally by users. So I might have a transaction, a part of that transaction. I might request to lock some resources in the database. All right. And these locks can be issued at lots of different levels. So I might lock an entire table. All right. I might lock one or more rows within a table. I might lock one or more columns within a table, or I can even lock individual cells, individual values. Don't let anybody else use or even look at this value until I'm done with it. Okay. So these types of locks are explicitly requested by the user. An example of a scenario in which an explicit lock might be used might be your end of the week processing. So maybe again, that we need to move all of our transaction data for the week from our operational database over into our data warehouse. So what we want to do is say lock an entire table. Maybe we request we use a stored procedure. That store procedure is going to carry out a set of tasks. It'll first ask for a table lock. And the database will say, all right, I need to lock a, you know, the requested table so that it can exclusively be used by this user who has requested it. Now, if other users are currently using that table, what the database will do is it will stop all new incoming requests. It will allow the users who are currently working with the data in those tables to complete their transactions. And that way it clears out the request. It's a, I don't know, say that you wanted to, maybe you're very wealthy and you want to have exclusive access to a store. You want to be the only person shopping in there at a particular time. So you issue your request to the store manager, you say, hey, buddy, I'm really wealthy and want to spend a lot of money. So I want to have this experience of being the only shopper in here at this time. And the manager will say, okay, I can do that for you. Just wait. I've got to wait for these other customers to finish their shopping and get out of the store. And then once all the other customers have left, you are allowed to enter the store, the manager locks the door. And then you can do all of your shopping that you need until you're finished. When you're finished, the manager will unlock the store so that you can leave and then other customers can come back in and start shopping again. That's what we're talking about here with a table lock, right? I'm requesting exclusive access saying I'm the only person that I want to needs to have access to this table because I have an important task to take care of. And so the database will facilitate that request. So we lock the table, no one else is allowed to use it. Then maybe we move all the data out of there, do our end of week processing where we calculate things total sales for the week, total sales by store, maybe hourly sales, daily sales, these kinds of things. We dump all of those aggregated summary data into our data warehouse. And then once we're done, we have the official version of say our sales for the week. And then we can unlock the table and allow other transactions to start recording new sales information in this case. So that would be a table lock. Of course, we can again issue locks at the row or column levels. It's very similar except instead of locking the entire table, you're just saying, hey, I need this set the rows. Maybe I need exclusive access to all of the user working the accounting department. Like no one is allowed to mess with any of their data until I'm finished with my task. Or we could do it like on a column level, right? We could say, okay, I need access to the, maybe the email column because we're going to update everybody's email address. I don't want anybody else making any changes until I'm finished. So I lock that column, do all of my updates, and then unlock it. And again, we can also do that, but just specific values. I need to work with this one specific value in the database, like the number of units in inventory for item 100. So I can just lock in that and no one else is allowed to see it or use it until I'm finished. So these locks can be issued at lots of different levels. They can be implicit or explicit. All right. So let's see an example of concurrent processing here with a lock in place. All right. So again, we have users A and B and the same sort of scenario that we had before where they are interested in modifying the number of units that we have in inventory for item number 100. And if you recall from before, user A was going to reduce the number of units in inventory by five, because maybe they sold five of them. And a user B was going to reduce the number of units in inventory by three because they sold three of them, whatever item 100 is. And again, time is flowing like this. So at the same time, both of these users request a lock on item 100, because then they're like, I need to work with this level of inventory. So I'm going to lock it, try to avoid any concurrency control problems. And then at time two, the request is, hey, read the number of units in inventory. Each of them makes its respective change to that value and stores the results. Now the database will not be able to process these things simultaneously because a lock is being issued, right? So we're going to process this process this in a serial fashion. So if these lock requests come in simultaneously and both users have the same level of priority, then the database will just need to arbitrarily choose one to handle first. And in this case, it's going to choose to handle the request from user A first. So let's see what happens when we have this locking strategy in place. So we lock item 100 for A. And while this is happening, once it's locked for A, for user A, user B just has to wait. Okay. So they're put in the waiting room. So I don't know, going to the doctor's office, right? If there's only one doctor, they can only see one patient at a time. And if another patient is currently being seen by the doctor, then you have to wait until the doctor is available. And so we lock item 100 for A, we read its value and return that back to A. Remember, we have, we start with 10 units of item 100 inventory. Okay. And of course, our request from B to lock item 100 is waiting there, but the database cannot respond to that request yet. So again, B is just put in the waiting room. She says to sit there and wait, just spin, wait in the queue until the lock is released. Okay. So continuing a user, a is requests. All right. So we are going to reduce the inventory count by five. So previously it was 10, 10 minus five is five. We therefore want to set the new inventory count for item number 100 to five units. Okay. And then we write the result. That is, we make that permanent by committing the change to the database. And then we release the lock on item 100. Okay. So at this point, all of the tasks for user A is transaction have been completed. Okay. And the lock has been released, which means the inventory level for item number 100 is now available to be used by other transactions. And in this case, we have another transaction from user B that's been waiting to use those data. So the database will immediately begin processing user B's request. User B again begins by locking the inventory level for item A. I'm going to read the inventory level, reduce whatever it is by three, make that change permanent. And then we will unlock it so that it can be used by other transactions. Okay. So down here, we place the lock on item 100, the inventory level for item 100 for user B. Right. We read the number of units in inventory, which is currently five, right, because when user A's transaction was in process, we set it to five. Okay. So we send that value five back to user B. User B wants to reduce the count by three, five minus three is two. So we set the number of items and inventory for item number 100 to two. We then commit that change and make it permanent. So at this point, the official number of units and inventory for item number 100 is two. And then the database releases the lock. So that to the next transaction, when it comes in that needs access to the number of units of inventory for item 100, it will be able to gain exclusive access to that data value. Okay. So the result then by having this locking strategy in place is that by the time we get down here and we've completed processing all the steps in the transaction for user A and the transaction for user B is that we have two units of item 100 in inventory. And that is consistent with what we would expect. All right, because we started with 10 user A sold five of them, user B sold three of them. So together eight units were sold 10 minus eight is two. So with this locking or this locking strategy in place to support our concurrent processing, by the time we've the the databases completed these things and we reach the end of both transactions, the inventory value in the database for item 100 is correct and consistent with what we would expect. Okay. So when we have a concurrent processing scheme that works in this way, it is said to be a serializable processing scheme. So basically the idea is the final result should be consistent with what I would expect if I had processed those two transactions in any arbitrary order. Okay. So if I had processed user A's transactions first and then user B, the result should be the same as if I had processed user B's transaction first and then user A's. In either case in our inventory scenario, we should have two units left in inventory. So if I processed A first when A is finished, I have five units remaining and then I processed and I end up with two. If I had processed B first, I would have had seven units in inventory and then I processed A. I further reduced the number of units in inventory by five yielding two. So in the end, the result is the same. And if we have a concurrent processing scheme that has that property, it's said to be serialized. So this is interesting. We have these problems, that can arise when we have multiple users trying to use the same data at the same time. And so in reaction to those problems, this technique of resource locking has been developed and deployed on all these enterprise level databases. However, this introduction of resource locking can create the possibility of a new problem. And that particular problem is known as deadlock. Okay, so we tried to solve these problems with concurrent processing by using locking, but then because we have locking, we now have the possibility of deadlock. So what is deadlock? Well, deadlock is a situation where essentially two transactions are waiting on each other. That is, you currently have some resources that I need that are locked. So you have exclusive access to them. And I currently have exclusive access to some resources that you need. So I've locked those resources. And essentially, we're both just waiting for the other person to unlock the resources so that we can continue. And this will just go on and on forever, as you might imagine. So this is an interesting new problem that is introduced into the world of concurrent database processing by the introduction of resource locking. We can see an example of this. This is a non technical example where we're just going to illustrate this with pencils and paper, instead of dealing with like items and inventory or whatever else. So let's imagine that we have user A over here. And as you can see, user A has a transaction. They first want exclusive access to paper. So they're going to lock paper, meaning nobody else can access the paper until user A is done with it. Then they're going to do something with the paper. And then they're going to want to do something with pencils. Simultaneously, user B over here as a transaction. And they start off by grabbing exclusive access to the pencils. So no one else can have any pencils until user B is done with them. And so then user B does something with those pencils. And then they need paper. So they request exclusive access to paper. So you'll notice in this case that keeping in mind that these things are happening simultaneously, that user B is requesting a resource that was previously locked and is still currently locked by user A. Well, user A, once they reach time three here, is requesting a resource that is already in use and currently locked by user B. So both are just waiting for the other to unlock. If we take a look at this down here, we'll see the order of processing at the database. So the database is going to say, I'm going to arbitrarily choose to process user A is first request. And it will lock paper for user A. And then it locks the other resource for user B. So paper, both paper and pencils are locked at that point. And then it handles A and B's requests to do whatever they want. So they're taking paper or pencils, do whatever they're doing. And then we reach a point where A and B are requesting exclusive access to the resources that are already in use by another user. So A is put in a wait state for pencils. Right here, pencils are already locked. So the database can't lock them for user A. And in that case, A has to wait. Right. The same time, B is in a similar situation. So user B is put in a wait state for paper because B has requested exclusive access to the paper. But unfortunately, it's already locked. Somebody else user A in this case already has access to it. So you can see the result here steps five and six is that A is waiting for B to unlock pencils and B is waiting for A to unlock paper. And so around and around they go, they just sit and wait forever because it's going to release them because neither transaction is complete. So that is life with deadlock. And all of these modern enterprise level database management systems need to have mechanisms in place to detect a deadlock situation, hopefully detect it in advance so that they may be able to alter the order of processing, but thus ensuring that the deadlock won't occur in the first place. But again, this is just a consequence of introducing resource locking as a mechanism for protecting the quality and the integrity of the data in the database in the context of a multi user database environment.