3 Fundamentals to Understanding Transactions.
Apr 8, 2020
4 min read
Last week we started learning how to use a database in a web application. As it was said, “interacting with a database to store / retrieve data is a fundamental skill that is used throughout development of any web-based, data-driven system.” So, it only makes sense that this week in class, we expanded upon that. Knowing that data can be transferred between systems and how to plan accordingly to prevent the ‘transaction’ from failing is vital to a robust system. But, what is a transaction? We’re not talking about the exchange of currency for goods, although the idea is similar.
Transactions are an independent logical unit that are executed for your data retrieval / updates. Meaning that, using the query statements, like those to the left, you can retrieve / update data. They are used for various server-side technologies. So, when we use the: INSERT, UPDATE, CREATE or DELETE statements on a database, we are performing transactions. We indicate a specific task to be executed and when all the specified tasks end successfully, so does the transaction. By using the COMMIT or ROLLBACK SQL statements you control these transactions.
To put this all simply, your transactions in a database are like a to-do list. If I gave you have a list of 3 tasks to complete to make a PB&J sandwich, like: 1) gather ingredients 2) put PB&J on bread 3) put both sides together; then you went to complete the tasks. So, we 'Begin Transaction', you are off looking to start the first step: gathering the supplies you need to make this lunchtime treat. As you finished gathering all of the ingredients you needed, you realize you don't have any jelly! This would mean that step 1 of your transaction has failed. Therefore, the entire transaction failed. All of the statements executed in your SQL group either succeed or fail. Making the management of your databases simpler and safer for users to operate. To really understand how to control these transactions, we've got to dig a little deeper.
Take the same example, except I didn't give you the list, you were just making the sandwich as a nice gesture for your friend who has been playing Borderlands 3 for too long and you're not sure if they have eaten. Well, just like a real transaction in a database, the 'client' here represents your friend. Every process in a transaction is performed in a temporary state -- this is where the controlling of transactions comes into play. The client won't see the transactions until they are committed, giving you the opportunity to ROLLBACK or undo the transaction if you needed to. The fact that the first step failed would fail the transaction -- meaning no sandwich for your friend, but it is okay he didn't know you were making it anyway!
With all of these possible revisions, it is important that we remember we have to maintain our databases. This is usually when you start dropping ACID. No not that, these are the 4 properties that you should be aware of and abide by when completing transactions. They are:
Atomicity, Consistency, Isolation & Durability.
Abiding by these 4 principles makes our databases maintainable, but you can also manage all of the transactions in the database's log file! Logging who changes what, how and when can enable any developer the ability to maintain their database. These logs contain identifiers that link back to what transactions created them. Be mindful though, depending on what you log and how much you log these lists can grow enormous. The last thing you want to do is go on a scavenger hunt for the problem in a log of changes. Each database has their own way of logging changes as well. For instance, Microsoft SQL Server will log records in the order they are created.
For these reasons [ and more ] you should always do backups! Database backups are representations of the state of your database at a specific point in time. Any changes that you or others may have made since the last backup are going to be stored in the transaction log, they won't show up on a backup until you do a new one.
Finally, we get to some things that you need to watch out for when you are dealing with databases. First thing to be aware of is that currently, the default in MySQL is that it runs with auto commit mode enabled. Meaning that it will store the changes permanently (cannot be rolled back) as soon as you execute a statement that modifies a table. Secondly, there are a number of other statements in MySQL that cannot be rolled back. These would include statements like CREATE & DROP databases. Lastly, (at least for the purpose of this blog) be aware of the statements tat implicitly* end any transaction that is active in your current session -- similar to when we did the COMMIT before executing.
To end this long-drawn-out explanation of transactions into a easily digestible fashion that is easy to remember, let's break this down into the 3 fundamentals you need to understand transactions.
Hopefully this culmination will help you all along your way in your database journey. If you want to go through some more examples W3Resource is chock-full of substantial information and examples you can run through.
References: https://www.techopedia.com/definition/16455/transactionhttps://www.tutorialspoint.com/sql/sql-transactions.htm ← table examples
https://www.tutorialspoint.com/dbms/dbms_transaction.htm ← recreation of image
https://www.w3resource.com/mysql/mysql-transaction.php ← implicit Commit
https://www.w3resource.com/mysql/mysql-transaction.php ← W3Resource example of transactions
Comments