DAT BLOG POST 10

Sessions 11-12 – Transactions

What Have I Learned?

This week I learned about SQL Transactions. As stated by MySQLTutorial (2020), “MySQL transaction allows you to execute a set of MySQL operations to ensure that the database never contains the result of partial operations. In a set of operations, if one of them fails, the rollback occurs to restore the database to its original state. If no error occurs, the entire set of statements is committed to the database.” Transactions are very useful in many scenarios, especially when we are doing many statements in one procedure that we all to execute successfully, rather than the ones executing successfully up until the one that fails.

A good example of where we would want to use a transaction from my game is the procedure shown below.

I have highlighted all of the statements where we are either updating (red) or deleting (green) a record. If we were to run this without a transaction and it were to fail halfway through, any updates or deletes before the error point will still commit. This is very bad for the integrity of the data within our database. Therefore, we use transactions to create a rollback to ensure that the database never contains the result of partial operations.

By using a transaction in the stored procedure shown on the left, we can rollback the server to the state that it was in before the procedure was executed. The method on the left has two declarations at the beginning of the procedure as shown below:

At the end of the procedure when we want to commit the changes, I initiate an if statement that checks the bool value of _rollback. When true, a rollback is called, when false the changes commit. This is shown below:

---

Why Have I Learned This?

I believe that I have learned about transactions as they are very important when it comes to handling data in a database. If I am handling many insert and update at one time, I want to ensure that the data stays consistent across the records. It is very important that any relevant records are either manipulated fully or not manipulated at all (all or nothing).

---

How Have I Learned This?

I have learned this by doing some research into what transactions are and how I implement them into my code. I did some tests on the tables that I have created for my game to see if it works as expected. I got the expected results and am satisfied that I understand how to use them for future tasks.

MySQLTutorial. (2020b, April 11). MySQL Transaction: START TRANSACTION, COMMIT & ROLLBACK by Examples. https://www.mysqltutorial.org/mysql-transaction.aspx