Locked and Loaded: Navigating Database Locking Strategies - Part 2

In Part 1 of this post we discussed some of the thoretical concepts related to the types of locks in databases and how to implement them along with the real world scenarios where they can be very useful. Continuing on that now we will dive deeper into the practical aspects of how these locks actually work. I will try to provide some practical examples along with raw sql queries which will help you better understand the scenarios where locks can be useful. So lets begin.

Initial Setup

We will mimic simultaneous transactions by spinning up a mysql docker container. This can be done very easily with the following command from mysql official website.

docker run --name mysql_v1 -e MYSQL_ROOT_PASSWORD=toor -d mysql:latest

This will download the latest mysql image from docker hub on your local system and start the mysql container. But make sure your docker application is running in the background before executing any docker commands.

Once the container is up and running we can get to the shell of mysql using the exec command of docker. Run these commands in two different terminals.

docker exec -it mysql_v1 bash
mysql -u root -p'toor'

docker exec -it mysql_v1 bash
mysql -u root -p'toor'

After doing this we are ready with our two mysql command line terminals from where we can run our transactions to perform different kind of locks.

Read Locks

We read in the Part 1 of this post that read locks can lock the rows in such a way that other transactions may read those rows but cannot perform update operations on it. Lets see how this can be done practically. Consider the following table for our reference.

mysql> select * from test limit 10;
+----+------+
| id | val  |
+----+------+
|  1 |   47 |
|  2 |  825 |
|  3 |   86 |
|  4 |  955 |
|  5 |  519 |
|  6 |  730 |
|  7 |   95 |
|  8 |  284 |
|  9 |  133 |
| 10 |  817 |
+----+------+

We create a transaction where we fetch some rows from table.

# For transaction 1
mysql> start transaction;
mysql> select * from test where val = 730 FOR SHARE;

This command locks the rows which the database engine needs to access in order to provide the results. Now if we try to update any of these rows through any other transaction then we are made to wait until the lock is released by the previous transaction.

# For transaction 2
mysql> start transaction;
mysql> update test set val = 745 where id = 6;

As you can see in the screenshot below that once we have acquired read locks on the rows other transaction has to wait for update until the lock is released by the previous transaction.

Write Locks

Write Locks acquire exclusive lock on the rows same as what an sql UPDATE query would acquire. This kind of lock prevents other transactions to update the affected rows or even perform a SELECT ... FOR SHARE on them. This kind of lock is essential in situations where a single entity is getting updated by multiple transactions like a counter. We will refer the same table test for our reference.

Lets say we have two transactions who want to increase the value of a particular row by 1. If we use the SELECT ... FOR SHARE locking which allows transactions to read the locked data then multiple transactions will see the same value and will not correctly increment the value. We need to prevent the transactions from accessing this value untill the other transaction which has acquired a lock successfully increments the value. Lets see how we can solve this using SELECT ... FOR UPDATE.

In the above image you can see that the transaction on the right side is not allowed to take a read lock on the row which is already is under write lock on the left side. So the transaction on the left side can safely increment the value and be assured that the transaction on the right will always get to see the updated value.

Avoiding Locks

As we read in Part 1 of this post that there are scenarios where such lockings are not acceptable. The transaction should either exit raising an error or just skip the rows that are locked and move forward. There are two methods that can be used along with read or write locks to avoid this waiting on locked rows namely NOWAIT and SKIP LOCKED.

We can see in the above image that with the same example that we used earlier if we just add the NOWAIT keyword then the query raises an exception immediately rather than waiting for the lock to be released by the transaction on the left. But there are scenarios where you just need to skip the locked rows and move forward. This can be done by using the SKIP LOCKED keyword. Lets see how we can use it with an example.

In the above example first we obtained a write lock in a transaction shown on the left. Now when we start another transaction on the right side and execute the query to obtain a lock on an already locked row using SKIP LOCKED we dont get an error but rather an empty result. This is because the database engine just skipped the locked rows. But if we look at the next query which is trying to acquire a lock on id = 10 is also getting no result. Why is that row getting skipped when we can clearly see in the next query that id = 10 do exist ?

You would understand this better if you read Part 1 of this post where you will get to understand the importance of indexes during locking. In this example since we do not have any indexes applied to the table hence the database engine needs to scan the entire table to fetch the record hence a table wide lock is acquired by the transaction. Lets see how we can overcome this issue by having an index.

If you see on the left side we created an index on the column "val". So when we acquire a lock with a WHERE condition on this column then only that row will get locked with the help of index. Now when we try to acquire a lock on some other row in another transaction we are successfully able to do so as depicted in the transaction on the right. This demonstrates how important it is to create good indexes so our queries do not scan more rows than required.

Conclusion

I hope this post has atleast given you a brief idea of what locks actually are and what is the practical use of them. You also must have learned the practical implementation and use of these locks in the real world. Practising on your own and trying different combinations will help you get a deeper understanding. Still if you have any doubts or you need to discuss anything related to this topic i would be glad to get in touch with you. You can email me or ping me on my socials.