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

Warren Niu
Mar 20 · 6 min read

In recent weeks, I have been fortunate to be involved as a class mentor for a wonderful organization called Code The Dream (CTD). This opportunity has allowed me to take intermittent breathers from JavaScript and re-enter the back-end world of Ruby on Rails & SQL.

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.

Conclusion

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!

Sources

Techopedia: What is a Transaction (in a database)?https://www.techopedia.com/definition/16455/transaction-databases

Nerd For Tech

From Confusion to Clarification

Nerd For Tech

NFT is an Educational Media House. Our mission is to bring the invaluable knowledge and experiences of experts from all over the world to the novice. To stay up to date on other topics, follow us on LinkedIn. https://www.linkedin.com/company/nerdfortech

Warren Niu

Written by

Uncovering the truths of Software Engineering one story at a time. Former Healthcare Administrator and proud dad of my Pomeranian, Nami. Based in Brooklyn, NY

Nerd For Tech

NFT is an Educational Media House. Our mission is to bring the invaluable knowledge and experiences of experts from all over the world to the novice. To stay up to date on other topics, follow us on LinkedIn. https://www.linkedin.com/company/nerdfortech

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store