Understanding Transactions

Introduction

So far you have learned quite a lot about querying and modifying data in databases. You also know some theory about transactions and their role in maintaining the consistency and integrity of the databases. Now let's dive into the practical aspects of the implementation of transactions using SQL and see how transactions are managed.

To remind you why transactions are so important let's once again consider a classic problem — money transfer. Consider a table with 2 columns: the bank account number and the amount of money in this account. We need to transfer money from one account to another. To do this, you need to run an UPDATE query to reduce the first invoice amount by the required amount. After that, we perform an UPDATE to increase the value of the second account. Everything seems to be fine. But what if after decreasing the first account, the lights are turned off and the server does not have enough time to update another account? The money has already been withdrawn but has not been recorded anywhere, which means that it is gone. Suppose you update the account first and then withdraw money. If the withdrawal does not occur, the bank may lose money, because an extra amount appears and no withdrawal occurs.

What is SQL transaction?

A transaction is a sequence of operations that are each a logical unit of data manipulation. In other words, transactions allow us to control the processes of saving changes in databases.

You should know that there are two types of transactions, explicit and implicit:

1. Implicit transaction specifies any separate instruction INSERT, UPDATE, or DELETE as a transaction unit.

2. Explicit transaction usually is a group of language instructions with statements such as START TRANSACTION, SAVEPOINT, COMMIT, and ROLLBACK that mark the start and the end.

Let's consider the examples of explicit transactions.

Successful completion of transaction

The employees table has the following columns: surname(VARCHAR(50)), name(VARCHAR(50)), age(INT).

Below is the listing of table creation:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    surname VARCHAR(50),
    name VARCHAR(50),
    age INT
);

Our task is to update the age of all employees named "Justin".

Transaction listing should be the following:

START TRANSACTION;
    UPDATE 
        employees 
    SET 
        age = 18 
    WHERE 
        name = 'Justin';
COMMIT;

While START TRANSACTION means the beginning of a transaction, COMMIT makes the changes permanent against a database.

After the transaction is completed, the table will look like this:

Canceling transaction

Let's take the earlier resulting table employees and try to cancel the deletion of the employees with the surname "Brown".

Here's our transaction listing:

START TRANSACTION;
    DELETE FROM employees 
    WHERE surname = 'Brown';
ROLLBACK TRANSACTION;

ROLLBACK TRANSACTION means to roll back a transaction (to cancel it).

Below you can see the resulting table:

As you can see, an employee with the last name "Brown" was not deleted, and no changes took place because the transaction was canceled.

Transaction Save Point

We continue to work with the employees table. Let's imagine that we will insert some new records into the table and then change our minds several times.

The transaction listing is below:

START TRANSACTION;
    SAVEPOINT SP1;
    INSERT INTO employees (id, surname, name, age) 
    VALUES ('4', 'Smith', 'David', '18');

    SAVEPOINT SP2;
    INSERT INTO employees (id, surname, name, age) 
    VALUES ('5', 'Williams', 'Robert', '18');

    SAVEPOINT SP3;
    INSERT INTO employees (id, surname, name, age) 
    VALUES ('6', 'Miller', 'Paul', '18');

ROLLBACK TO SAVEPOINT SP2;
COMMIT;

SAVEPOINT here is the save point of the transaction. In this case, we've canceled the transaction after the second savepoint (SP2). All points after it have also been rolled back, accordingly, since transactions work sequentially.

The resulting table is the following:

As you can see, only an employee with the surname "Smith" was added to the table and two other insertions were rolled back.

Conclusion

There are two types of transactions: implicit, which is autocommit (there are no beginning and end of the transaction), and explicit transaction, which has a beginning and an end, and a rollback command.

The general template for writing transactions is the following:

START TRANSACTION; 
    SAVEPOINT {savepoint_name};
    some SQL statement; 
    SAVEPOINT {savepoint_name};
    some SQL statement; 
    ...
-- you can rollback a transaction to a certain point
ROLLBACK TO SAVEPOINT {savepoint_name};
-- or rollback a transaction completely
ROLLBACK;
-- or commit a transaction
COMMIT;

Now let's practice and use what you've learned today!