TCL Statements in SQL: How to Manage Database Transactions?

SQL is the most widely used relational database language. Due to its intuitive nature, it’s the go-to choice for a growing number of data professionals and companies across numerous industries. In fact, according to our annual research on 1,001 data scientists’ LinkedIn profiles, 82% have listed SQL proficiency among their key skills, marking a thumping 60% increase in popularity compared to 2020.

Therefore, if you are a beginner in data science or a working professional looking to add SQL to their toolbox, knowing how to use the syntax is a crucial initial step. In other words, to fully understand how to handle data with SQL, you must first meet its 4 components: DDL, DML, DCL, and TCL.

In this tutorial, you will learn all about:

• The definition of transaction control language (TCL) and why we use it
• What the TCL statements are and how to apply them

What Is TCL?

While working with relational database management systems in a professional environment, it is key to be in control of the transactions you execute. In other words, you want to know very well what you are inserting, deleting, or updating in your database.

Here is where the transaction control language comes in – the part of the SQL syntax that allows you to manage transactions.

What Are the TCL Statements?

To work with TCL, you must be familiar with its commands, specifically:

• COMMIT
• ROLLBACK
• SAVEPOINT

Before we look at each one individually, it’s important to note that these will only respond to the DML syntax components: INSERT, DELETE, and UPDATE.

Please note that MySQL Workbench allows you to toggle the Autocommit “button”. This is a functionality that will automatically save every modification you make to the selected database.

If we leave this “button” pressed, we will exclude working with TCL statements in our work. Hence, throughout this tutorial, we will switch off this functionality:

COMMIT Statement

COMMIT will save the changes you’ve made permanently on the database and also let other users access the modified version.

Suppose you want to update a record in a table called “Customers” by changing the last name of the fourth client from Winnfield to Johnson:

 If you are a database administrator, you can start with the UPDATE clause:

UPDATE customers
SET last_name = 'Johnson'
WHERE customer_id = 4;

However, your work isn’t done just yet. The rest of the users working with the database system cannot see that you have made any modifications. To complete the entire process, you must add a COMMIT statement at the end of the UPDATE block:

UPDATE customers
SET last_name = 'Johnson'
WHERE customer_id = 4
COMMIT;

Now, the updated information will be available to all users – they will see Catherine Johnson instead of Winnfield in the “Customers” table:

Essentially, COMMIT saves the transaction in the database. Once issued, the changes are permanent and cannot be undone.

ROLLBACK Statement

Committed states can quickly accumulate in numbers. For example, if you are the administrator, you might need to use COMMIT 20 times today.

Consequently, you might insert or update part of your data erroneously. Fortunately, ROLLBACK is the transaction control language command that will let you restore the database to the last committed state, reversing any updates you don’t want to save permanently.

To apply this, type ROLLBACK; at the end of your code:

UPDATE customers
SET last_name = 'Johnson'
WHERE customer_id = 4
COMMIT;

ROLLBACK;

Please remember that, while it can be useful, you should be careful not to revert anything accidentally.

SAVEPOINT Statement

In practice, working with SQL is all about executing a transaction after transaction. That’s why you can think of a MySQL transaction as a work unit, made up of all statements and queries you intend to execute between 2 consecutive committed states.

The SAVEPOINT command can break such a unit into multiple parts. While you can roll back to a certain transaction, once you do that, the points will not exist anymore. That’s why this command can be considered a temporary event.

Going back to the “Customers” table, suppose you haven’t entered any records yet:

So, the first step is to insert data about 3 customers by using 3 separate INSERT statements, committing to the given state after the first. Here, it’s important to note that you can apply a SELECT command after each INSERT to check the state of “Customers” at that moment.

After the second and third statements, create savepoints with relevant names by using the following syntax:

SAVEPOINT savepoint_name;

Thus, your code will look something like this:

INSERT INTO customers (first_name, last_name, email_address, number_of_complaints)
VALUES ('John', 'McKinley', 'john.mckinley@365careers.com', 0);
COMMIT;

INSERT INTO customers (first_name, last_name, email_address, number_of_complaints)
VALUES ('Maggie', 'Wilkinson', 'maggie.wilkinson@365careers.com', 1);
SAVEPOINT customers_savepoint_01;

INSERT INTO customers (first_name, last_name, email_address, number_of_complaints)
VALUES ('Brad', 'Finley', 'jacob.finley@365careers.com', 0);
SAVEPOINT customers_savepoint_02; 

You can roll back to the second point by executing the following query:

ROLLBACK TO customers_savepoint_02;

Then, you will see all 3 records in the “Customers” table:

Also, if you decide you won’t need this savepoint anymore, release it by executing:

RELEASE SAVEPOINT customers_savepoint_02;

Now, you will obtain an error message saying that it doesn’t exist anymore if you roll back.

As a result, the table contains the first 2 records only:

Now, let’s suppose you want to assign a state of the ongoing transaction to a SAVEPOINT statement and give it a name that already exists. What happens is that the old point is replaced by the new.

For example, you insert a third record, which will take ID equal to 4:

INSERT INTO customers (first_name, last_name, email_address, number_of_complaints)
VALUES ('Brad', 'Finley', 'jacob.finley@365careers.com', 0); 

Thus, we will have 3 records again:

Nevertheless, if you create a savepoint called ‘customers_savepoint_01’, you will obtain an output containing 3 records as you roll back, not 2. To see this, you can execute:

SAVEPOINT customers_savepoint_01;
ROLLBACK TO customers_savepoint_01;
SELECT * FROM customers; 

But what happens if you just carry out a ROLLBACK statement without indicating any savepoint name? Well, you will get back to when there was a single record in the “Customers” table:

To prove that the command creates only temporary changes within the framework set by a certain ongoing transaction, so to speak, try to roll back to the ‘customer_savepoint_01’:

ROLLBACK TO customers_savepoint_01;

As you do that, you will obtain a message saying that this savepoint does not exist anymore:

TCL in SQL: What Comes Next?

Becoming fluent in SQL is a fundamental step toward your career in data science. Strong knowledge is a prerequisite for any job application in the field and many employers are highly likely to test your SQL skills at the interview stage.

Are you ready for the next step toward a career in data science?

The 365 Data Science Program offers self-paced courses led by renowned industry experts. Starting from the very basics all the way to advanced specialization, you will learn by doing with a myriad of practical exercises and real-world business cases. If you want to see how the training works, start with our free lessons by signing up below.