In our MySQL practices so far, we’ve been interacting with the database directly as one user. Yet very commonly in actual usage, databases truly shine in their value and purpose when it’s used by many users. Let’s think back to the example we used in our assignment in creating a database for a mug shop.

If the mug shop became successful and it was connect to an online shop, it will most likely have multiple users. Each account that logs in to make orders will be a different user. We could even imagine the mug shop becoming the premier retailer for mugs internationally and having thousands of users all over the world purchasing mugs. Databases are designed for environments and usage such as this, and one critical aspect of it is to be able to accurately and efficiently function in such environments. You could think back to ACID (Atomicity, Consistency, Isolation, and Durability) that we covered in an earlier lesson.

When a user choses a mug color, size and design and makes an order the underlying code has to check the mug table to get the mug_id, then check the order table to get the next available order number (order_id) and create a new order. The code might also want to check the account table to check if all the info needed is intact for the this account. You can easily think of different scenarios where a simple real-life action, such as making an order, involves a combination of several SQL actions.

Imagine several users making all these actions to the same database at a very similar timing, then throw in the possibility of some of these actions being taking fields out of the database, such as removing mugs that we’re not selling anymore. Hope this didn’t get you too stressed! Because we have a solution, which is using transactions!

Let’s take a step back on this stressful situation and put our designer hat on. How would you design a database when you are aware that these would be issues? A solution could be to have a way to group several actions together and execute all or none. This should followed with supplementary features to make changes permanent when done or the option to reverse changes without causing damage.

With all this background and context in mind, let’s explore START TRANSACTION, COMMIT and ROLLBACK.

By using the START TRANSACTION statement, you are doing exactly that. You are starting a transaction, that could be thought of as a unit of one or more SQL commands. After the SQL commands that follow, you will end with the COMMIT command that will conclude the transaction and make all the changes permanent. An alternative is to end the transaction with a ROLLBACK. The ROLLBACK command will reverse all the changes that were made during the transaction.

Going back to the mug shop, let’s think about a scenario that we are removing a user. You could easily think that we could DELETE the row for that user account. It could look something like below.

DELETE FROM account WHERE account_id = ‘melissa45’;

But now, you have to think through if there are any other changes you need to make to the rest of the database. What if there were any orders that were made by this user account? Let’s say you talked with the stakeholders (the mug shop business owners) and they instructed you that when you delete a user account, you should delete all the orders made by that user. Then you could probably think of a command such as below.

DELETE FROM order WHERE account_id = ‘melissa45’;

This might feel straightforward, but in real-life in many cases, it might not. What if a different process accessed the database right between the timing that the account was deleted from the account table, but the orders of that account were not deleted yet? This could cause significant errors to the output or processes that will follow. You might think that there’s a low probability of this happening, but we still have to make sure that we have measures that prevent such situations.

Additionally, what if the first step of the process above, meaning deleting the account worked, but for some reason, the second step in deleting the orders didn’t work. Or, what if the removal of the account didn’t work, but the removal of the orders worked? Errors happen. In this case we will now have a situation where we have orders present for an account that is non-existent. You could easily imagine that this will cause a multitude of issues and impact the overall integrity and reliability of the database.

If you are thinking that TRANSACTIONs and ROLLBACK is the perfect solution for this, you are on the right track. TRANSACTIONs enable the developer to group together one or more commands as one TRANSACTION, and ROLLBACK enables the developer to reverse any changes to the database made during the TRANSACTION.

Going back to our example with the mug shop, we need to group together the removal of the user account and the removal of the orders. So first of all, they should be executed together, not one then the other, and secondly, either both commands have to be executed, or none of them could be executed. This means that we won’t allow a case where a portion of the group (the TRANSACTION) is executed, but not other portions (for instance we will not allow just the account to be deleted, but not the order, or just the orders to be deleted, but not the account).

If the reason that we need TRANSACTIONs and ROLLBACKs makes sense, we are in good shape to move on to the next topic and try out the implementation.

You can watch a video of a brief walk through of this topic here.