Making a Deal with your Database: How to complete a Transaction
Keeping a clean & comprehensive database is crucial — how transactions in SQL play a pivotal role in accomplishing this
To learn more about Code The Dream & how you can get involved, visit their website! https://codethedream.org/
While I was excited to get my hands dirty with Ruby again, I unexpectedly learned that the first couple of weeks in CTD’s curriculum was SQL, or Structured Query Language.
Oh man…I definitely glossed over this part when I was at The Flatiron School. Working with the Active Record ORM (or Object Relational Mapping), it made sense why I did. Many developers can go about their tasks without ever writing a line of SQL. After all, Active Record, or other ORM’s like Django (Python) and Hibernate (Java) allows developers to perform CRUD actions and communicate to databases in their respective languages.
So here I am, expected to be a class mentor for new students on a topic that I not only learned for a short while half a year ago, but also skimmed over as I felt like it didn’t matter too much at the time.
I had my work cut out. But looking back on this week, it has certainly been a blessing in disguise.
Think of ORMs as a car. When you insert your key into the ignition and turn it (or I guess with the new fancier cars, press a button), your car magically turns on. When you press your foot down on the gas pedal, you expect the car to move. When you press your foot down on the brake pedal, you expect the car to stop.
What you don’t think of as you’re doing these motions is what’s happening under the hood to make these actions happen. Learning the basics of SQL is like opening the hood & understanding some of the basic mechanics of how your code is communicating with the database.
On that note, I’ve honestly opened the hood of my last car maybe a total of 5 times. Perhaps that’s why it’s no longer around…
I’ll assume that you have a general idea of how SQL works and the basic syntax. Let’s set up a basic database to get started!
If you want to learn the basics of SQL or need a refresher, I recommend SQLBolt to get some practice: https://sqlbolt.com/lesson/introduction
Setting up our Database
Let’s say we have a simple database of the books that we own. Looking on my bookshelf, here’s what we have:
That’s some bookshelf. But it looks like we’re missing an important book in our collection. Thankfully, we have SQL to help us!
After brushing up on some of the basics of SQL, we can quickly determine that we can create a row in our database by using the INSERT INTO keyword. Our statement might look something like this:
Now that’s a bookshelf! We have all 7 of the Harry Potter books in our database. All is well.
Except…we made an error. I accidentally spelled “Potter” run!
Before all you Harry Potter fans start sending me angry messages, it was intentional & I’m trying to make a point!
While we could create another statement using the UPDATE keyword to update our database, pretend for a minute that we were working with a database that had thousands of rows where you had to make numerous changes, or if you were seeding massive amounts of data into a database for a new company but wanted to revert to how you had it previously because you made some mistakes. That would be quite a tiresome task if you ask me.
Luckily, we can use something called transactions to make our lives easier.
Committing & Rollback
When writing our SQL statements, we have to be certain that our statements that we write and run is exactly what we want to get executed. In a production environment, it can be very difficult if you & your team want to revert to an earlier state. The reason for that is because in most instances, our statements are being run in auto-commit mode, or in other words, it automatically gets saved to our disk.
Let’s say we’re doing batch operations, or running multiple statements together. It may be best to turn auto-commit off as we run our script file in case we run into any issues.
We can complete this by using transactions. Let’s see how this will look:
We use the BEGIN keyword (or we can also use BEGIN TRANSACTION) to signal to our database to run all of our statements at once whenever we’re ready. In other words, it tells our database to switch off auto commit.
After our statements, we use the COMMIT keyword to lock in our statements and commit our changes to the database. Make sure everything looks OK before you commit!
However, back to our previous point of errors. Let’s say, instead, we have fat fingers and accidentally made some typos, or entered in some wrong dates:
Uh oh. Now we’ve done it!
But it’s OK. By issuing the ROLLBACK keyword before we commit, we can revert our changes back to the previous state. Now that’s a helpful transaction!
Review of Transactions
In a simple explanation, transactions can be defined as any changes to a database; any “transaction” between the database components and the data fields that they contain. To hammer home the concept, let’s bring in some help from some experts.
Techopedia explains that transactions are completed by COMMIT, and ROLLBACK SQL statements (as illustrated above), which indicate a transaction’s beginning or end. The ACID acronym defines the properties of a database transaction:
Atomicity: A transaction must be fully complete, committed or rolled back.
Consistency: A transaction must be fully compliant with the previous state of the database. In other words, the transaction cannot break the database’s constraints.
Isolation: Transaction data must not be available to other transactions until the original transaction is committed or rolled back.
Durability: Transaction data changes must be available, even in the event of database failure.
If our transaction covers all these properties, then we have ourselves a deal.
I hope this walkthrough helped you gain a better understanding of transactions! If you haven’t already, I encourage you to take some time to look “under the hood” and practice writing some SQL languages so you gain a better understanding as to how some of the magic works — it’s easy to take something for granted.
Please feel to let me know your thoughts below — would love to hear it!
Techopedia: What is a Transaction (in a database)?https://www.techopedia.com/definition/16455/transaction-databases