Django - select_for_update() function / Locking Database Rows

in this video we're going to take a look at a very useful function in the D Jango orm and that's to select for update function and this function can be used with transactions in jangle and it works by returning a query set where the rows involved in that query set are locked until the end of the transaction and that means we can safely update the data in those rows without having to worry about concurrent processes changing that data and leaving our database in an inconsistent state so let's dive in now we're going to look at the documentation here here we have the function select for update in the Jango docs and as it says here it returns a query set that will lock rows until the end of the transaction and it generates the select for update SQL statement on supported databases now this function is going to take advantage of locking mechanisms that are provided by the database under the hood and we're using SQL light which doesn't have these locking mechanisms but this is supported on post gql Oracle and MySQL and in this this video we're going to migrate our database from SQL light to postgresql and in order to do that we're going to use a Docker container and create the postgres database in that container now the problem we're going to demonstrate here is as follows if we go back to the code and we go to the models. pile we have the order and the product models from the previous video and when a user places an order the number of items is specified for the given product and when that order comes through the number of items should update the number in stock for that product so for example if a user orders two books from the system the product here is the book and the number in stock should be decreased by two in that case now in the last video we talked about the problem where for example an order might be placed for those two books but before the product number in stock can be updated the system might crash and therefore you have the order in the database but the number in stock has not been updated to reflect that order we solved that problem in the last video using transactions we have an atomic transaction here and we are generating the order within that transaction and then we are decrementing the product's number in stock so the order is created and the number in stock is decremented as part of a transaction which means if there's any problem in the middle of that transaction with any of those operations then the whole thing is going to be rolled back and the order itself will not be created we're going to show a different problem in this video and it's related to transactions but it is a slightly different problem if we go back to this page let's imagine that we wanted to order two books from the system or rather one book from the system and there are two books in the database in stock so we have two books in stock when we submit this order we expect the number in stock to be decremented so it's going to go here from two down to one but the problem is Imagine two people place this order at exactly the same time the number of books in stock is two but two people are placing an order of a single book that should reduce the number in stock to zero but if they both read the value two at the same time each of them are going to place that order and that is potentially going to send the wrong value back to the database here when we decrement it by the number of items that have been ordered and just to demonstrate why this is we have two users placing an order so there are two transactions against this database table those orders will be created and within those transactions each one of them is going to read the number in stock as two and it's going to decrement that by the number of items that have been ordered which is one so the resulting value for both transactions is going to be one so even though two users are ordering a single book the number in stock is not going to go down from two to zero the final value is going to be one and that's because both users have read the value two here and decremented it by one at the same time so it's a concurrency problem that can exist when you have multiple transactions that are updating a piece of data in your database tables now to prevent such occurrences we can lock the product that's being extracted from the database using the select for update method now as I said earlier in order to do this we are going to need to use postgres and that's because SQL light will automatically lock all rows when any transaction is in progress this is a problem that can occur though in larger databases like postgres when we have multiple transactions now in order to install postgres we're going to use Docker here you can copy the docker pool command to your clipboard and you can run that in the terminal in order to download Docker so I'm going to bring up the terminal here and what I'm going to do is run Docker pool command and that's going to pull the docker container from Docker Hub down to your computer now if you don't want to use Docker we have also the ability to install postgres on your operating system so this is the page which I'll link in the description of the video you can click the download button and you have downloads for different operating systems and then you can set up your local database your postgress database on your local machine instead of using Docker but if you are using Docker you can download Docker hub from this page here I've got windows at the moment but there are downloads for all operating systems now I'm going to go back to the docker Hub page for postgres and if we scroll down here we have a sample command in order to start a postgres instance and what that's going to do when we run a command that's similar to this it's going to start a container that is running postgresql the database server and we can then connect to that database using D Jango so what I'm going to do is use an altered version of this command I'm going to go back to the terminal where we pulled that post gz container and I'm going to paste this command in here it's the docker run command and the name of the database that we're going to create here is omdb and we're setting an environment variable for the postc password I'm just going to set that to test for this video and finally we have a port mapping and that's Port 5432 in the container to Port 5432 and the host that's going to allow us to connect from our host which is the local machine into that container on the same port and finally the name of the actual image that we just pulled is the post C image we're going to use that to actually build the container once we run that command we're getting that the database system is ready to accept connections here so what we're going to do is we're going to go to Jango settings.py file so I'm going to open that up here and if we scroll up here we have a databases setting and when you create a jangle project by default that is going to connect to a SQL light database we're going to change this engine to post SQL and in order to do that I'm going to go back to the jangle documentation there is a setting for databases here I'll leave a link to this in the video we're going to copy this setting here and that's because that is connecting to postgres and we're going to fill out the details below that within that setting so let's paste this into settings.py now the name of the database is going to be orm series we'll call it that and the user that's going to connect is just going to be the postgres user and the password is the one that we set as an environment variable on the docker run command and that was just test and of course in a real database you want to extract these into a configuration file that's not sent to GitHub we don't want to just store the password as a raw string here and our host is Local Host and the port is 5432 so let's save this file and we're going to go to the terminal now what we need to do in order to connect to postgres from Django is install the python postgres driver here so I'm going to use a pip install command and the name of the package that we're going to use is this one here so what I'm going to do is we're going to actually just copy this pip install command go back to the terminal and we can paste that in that's going to install this package that's going to allow us to connect to postr L within this jangle environment so we have that installed what we can now do is test The Connection by running the server and hopefully we're not going to get any errors but in fact we do and the error that we're getting at the bottom here is that the database orm series that we're trying to connect to does not exist and that's the name of the database here that we're trying to connect to so we need to create that database on our post G container now how do we do that what I can do is run the docker PS command that will give us all run running containers you can see the postes container is the only container that I have running so we can connect to that container ID and what we can do is run a command that's going to allow us to create a database in that container and that command is the docker exit command and to that command we can paste this ID in here that's the postgres container and we can then place a command that we want to run and the command is create DB and we're going to give it the name omm series now when we execute that we're getting an error that the r rout does not exist we can specify a user here to the create DB command and that's the postgres user that's going to allow us to then create that database in the container so just to clarify the container is this here it is running a postgressql server and within that server we can of course create databases and the one we've just created here is called omm series hopefully now we can run the server and connect to this database that we have within the settings so let's run the Jango development server once again and we now have the server running and we have 23 unapplied migrations so this is a new database in order to generate the tables we can run python manage.py migrate and that runs through and creates all of those tables and the final thing we need to do in order to set this up is go to our management folder and within that we have a script called create data that we created earlier in this series what we can do is run that script python manage.py create data and that's going to run through and create a bunch of objects in the database that we can now use and use it for testing in this application once the data is in the database we're going to run the Django development server again and we're going to go to the Jango admin here I've got a user called admin I'm going to log in as and we are going to go to the core application here and these are the models that we've installed in the admin the create data script should add data for restaurants and you can see those here as well as well as some dummy ratings as well what we're going to do now is add some dummy products as we did in the last video so I'll get back to this once I've added those so I've now added some products we have a camera a video game and a book and as I said earlier there is a number in stock for each of these items the book in this case has three in stock now what we're going to do now is go back to VSS code here and we have this script here that we've been working with throughout the series what we're going to do within this script is demonstrate what the select for update function does so I'm going to copy the import of the transaction module from jangle and what we're going to do is basically Define a dummy transaction that lasts a long time so we're going to import the time module from jangle and let's set up a transaction here so it's with transaction. atomic and what we're going to do within this transaction is fetch a book from the database now that's the product model so it's product doobs doget and the name is going to be book so I need to import the product model from the core. models file so let's import that now we're fetching the book from the database and what we're going to do before we call do get is we're going to add a call to select for update so we are calling product doobs do select for update and then the get function and what this is going to do is it's going to get the book based on the parameters that we're sending to the get method but it's going to lock that particular rowle and it will return a single rule because it's using the get method that row is going to be locked which means that other rows cannot change any data within that object within that row until the transaction is completed so after we've fetched the book let's call time do sleep here and we're going to sleep for a minute so 68 seconds so this is the script that we're going to call let's bring a terminal over onto the page now and what we're going to do is we're going to call that script with python manage.py we're using the runscript extension from Django extensions and the name is omm script so we're going to run that and it's going to fetch that book and sleep for 1 minute and what we can do while that's sleeping is we're going to try and get this same book from the database in the Jango shell so let's open up shell plus here and we're going to fetch that book with a similar query it's going to be product doobs doget and the name is book here so we can fetch it no bother we get that at the bottom and we can access the fields such as the name on that book but what we cannot not do is update the book so if we try and reset the name to something else and we then call book Dove what we're going to see is that that row is locked we cannot do that you can see it's waiting at the moment and that's because the row it's trying to update is currently locked in the database table so you can see that's still locked and if we bring this up here to the page that is going to complete and only when this completes will the book be updated so that's the select for update method what we're going to do is go back to vs code and we're going to go to views.py and we're going to use it to solve the problem that we mentioned earlier where potentially the number in stock will be accessed by two different concurrent queries concurrent transactions and those are both going to try and update this value and that will result in the wrong final value being in the database now we're going to change the structure of the query a little bit before we call form. saave what we're going to do is we're going to extract the product the book from the database well it could be the book but it's basically going to be whatever the user selects on the front end so it's going to be product doobs do select for update and again the product model will need to be imported at the top here from the models.py file so once we have that imported what we're going to do is we're going to call Select for update and the question is what product are we actually going to get from the database based on the order that's been submitted from the front end we have this product order form here and that was defined the last video it's in the forms.py file and it has this field called Product which is the foreign key to the product model and that's what you see on the front end here we have this select box and each of these represents a foreign key to the product table so on the back end we can access the product from whatever's in this field here cuz that is going to be converted to a product model by the jangle form so let's go back to views.py and we're going to call the doget datab based method here and we're going to get the product by its ID and the ID is going to come from form. cleaned data I'm going to move this to a new line so we can see this it's going to be form. cleaned data and the key that we're looking at is called product that will give us back the product model that was selected on the front end and then we can get the primary key from that model and we're going to pass that in as the ID lookup to the products get method so what's going to happen here is that we're going to find this product by its ID from from the database and the ID comes from the user selection on the front end and then when we get back that single product we're going to lock it we're going to prevent other transactions from updating that data by calling the select for update method so the product here is going to be locked and that means that when we update the number in stock below here that's going to prevent other transactions from updating that at the same time and that will help to keep our database consistent for this particular field and note that we can update the product's number in stock within this transaction even though the product is locked and that's because this lock will be given to the transaction that currently executing this transaction here has acquired a lock for that rle by calling select for update when it fetches that data so that can update this but nothing else no other transactions will be able to do so so with that code we're now going to start D jango's server and we're going to go back to the front end and look at the form that we have on the page here so we have this form that allows us to submit an order for a particular product so let's submit an order for a book and we're going to select one for the quantity of items we submit that order and we get that processed on the back end now notice at the right hand side we have Jango debug toolbar here what I'm going to do is go to the history of the requests that have been sent to the server and we're going to look at the post request that was used to create that product or rather create that order in the database so let's switch to that and we're going to look at the SQL tab now and we're going to see some of the SQL that's being executed and one of these queries it's this one here contains the select for update expression in SQL and this is the expression that gets the product with the ID that was selected from the front end in this case it's the product ID of one and it gets the data for that product and the four update statement comes at the end and that means that whatever rows are returned by that query are going to be locked and that is what's preventing other transactions from modifying the data in that row when we are using this statement and that's the Crux of the select for update expression in order to test this on the page again I'm going to go back to vs code and we're going to import the time module and we're going to call time do sleep for 25 seconds here and by doing this I'm going to demonstrate a couple of arguments that you can pass to select for update so when we submit the form it's going to sleep for 25 seconds in the middle of this transaction that is locking the product that has been submitted so let's go back to the front end here and again we're going to select an order for the book so let's submit that and I'm going to bring up a terminal at the bottom here that has jango's shell opened and while that request is inlight from our page what I'm going to do is fetch the same product from the database and we're going to try and update the number in stock to 10 so if we call product Dove here you can see that it's not saving immediately and that's because this particular product is being used on the page we've sent a request and that product is therefore locked in the database until the request on the left hand side on the normal page is completed and that is only completed after I think it's 25 seconds I put in here after 25 seconds the rest of the transaction will be completed and then the lock will be released on this row that represents the product and only then once the lock is released will the code in this particular function or this terminal be executed now this is important of course for any fields that need to be consistent when you have multiple concurrent requests are updating values but the drawback is that when you're locking rows in the database any other requests that need to change those rows are going to have to wait until the transaction is finished before they can modify that data so you need to wait on the lock being released from those rows before those rows can be modified by other transactions and that can be a bottleneck in your applications if have a lot of Rights going on to different rows so the select for update function is a tool that should only be used when it's required only when you need to lock certain rows when performing operations so that other transactions cannot modify that data at the same time should you consider this function now let's finish this video by going back to D jango's documentation for select for update you can see there are some keyword arguments that can be passed to the function for example there's a keyword argument no weight which is by default set to false and the no weight keyword argument is described in this documentation and it's this Behavior here usually if another transaction has already acquired a lock on one of the selected rows the query will block until the lock is released if that's not the behavior you want you can pass no wait equals true and that will make the call non-blocking so let's demonstrate this again by submitting the form I'm going to increase the call to time do sleep to let's say 80 Seconds to give us a bit more time to do this and what we're going to do is we're going to go back to the form and we're going to submit a an order here for one book and then we're going to go back to the terminal and what I'm going to do is create a transaction here using the transaction. atomic function so with transaction. atomic what we're going to do is get the product from the database using product doobs do select for update and this time we're going to pass no wait equals true to that and we're going to try and get the product that the user has submitted and that's the one with ID of one and then if we try and update the number in stock here let's say update that to four when we call product do save in this case what we're going to get back is an error and that error as you can see here says that we could not obtain a lock on the row in relation core product and the reason now is that this is still submitting the form to the server so what happens here is that we're sleeping within the transaction and we have a lock in that transaction for this product with ID of one so in the terminal when we try and actually change that product within another transaction when we call Select for update in that transaction this time we're passing no wait equals true here which means that this call is not going to wait for the existing lock to be released it's simply going to return an exception so that's the no weight argument that you can use to prevent blocking calls to database resources that are locked what we also have in the documentation is another keyword argument this is the skip locked keyword argument so what we can do if we scroll down we can see the behavior of that and it's basically this line in the documentation you can ignore locked rows by using this skip locked keyword argument so let's very quickly see a demonstration of this now I'm going to go back to the form and again I'm going to submit that book and let's submit a single item to the database and go back to the terminal here I'm going to scroll down to the bottom and I'm just going to paste this code in here to be quick about this we are again opening a transaction and this time when we call Select for update on the product that we're getting back here from the dot get method we're passing the skip locked equals true keyword argument so let's now see what happens if we try and execute this we don't get back the same lock exception this time instead we're getting back a product does not exist exception and that's that the product matching the query that we provided does not exist now we know for a fact that we do have a product with ID of one but it's being being skipped in this call in this query here because we have set the skip locked keyword argument to True within the select for update expression so these two keyword arguments can customize the behavior when you call the select for update function to try and acquire a lock on a row or on a number of rows in a particular table and that's all for this video if you've enjoyed the video please like And subscribe to the channel and if you're enjoying this content please consider buying a coffee to support the channel as well and we'll see you in the next video

Channel: BugBytes

Views: 3,252

Keywords:

Id: xBl-qiAFrzg

Length: 23min 35sec (1415 seconds)

Published: Mon Oct 09 2023