PostgreSQL Transactions and How To Use Them

In this article, we’ll answer all of those questions and more to help you get started with PostgreSQL transactions and implementation.

What Are Transactions?

Simply put, transactions are propagations to any change in the database. Let's say that you added a new element to the database—that’s an example of a transaction. A transaction need not just be a single change, rather, it can contain one or more operations. In real-world applications, transactions are usually a combination of various database operations.

The important thing to note about PostgreSQL transactions is that they are ACID compliant. That means they are consistent, atomic, durable, and isolated. Transactions are consistent in the sense that the changes are propagated throughout the database at the same time—in other words, there won’t be any discrepancies. They are the most fundamental unit of any database and are hence atomic. If you want to learn more about what it means to be ACID compliant and what other kinds of compliance there are, please check out this article.

PostgreSQL transactions are also isolated. If one transaction fails due to some reason, the database remains unaffected and the other transactions may still go through. This means that they are also pretty durable and dependable.

Why Database Administrators use Transactions

The main selling point for transactions is that they are easy to handle. Many database administrators use transactions to take advantage of a database's various features. Transactions can also simplify many tasks by automating part or most of the work.

Transactions also add a layer of protection that can prevent simple mistakes from causing catastrophic failures. There have been many cases when DBAs failed to use transactions and lost crucial data. One noteworthy example is the Microsoft Sidekick incident. One of Sidekick's third-party providers neglected to establish a proper backup architecture using transactions, which ultimately led to all the data on these devices being lost forever. The Sidekick event could’ve been avoided by setting up a proper backup flow using database transactions.

Another data loss incident that could’ve been avoided using transactions is the AMAG Pharmaceuticals data loss incident. The database administrators at AMAG Pharmaceuticals lost HR data after one of the files stopped syncing with the folder. As a result, the other files started to disappear from the folder as well. Even though the company was able to retrieve the lost data from a backup, it was an unnecessary cost that could have easily been avoided by following proper transaction flows.

Advantages of Using Transactions

There are several advantages to using transactions in any database network.

Chaining Events Together

We can chain some events together using multiple transactions in a database. For instance, if we want to design a transaction for customers filling out a form to get money, we can include several other events—such as sending their account balance, sending a request to the payment database, and then paying the customer.

The only thing a local administrator will have to keep track of is the initial request and the response since most of the other stuff is handled by the transactions in the background.

Flexibility

Flexibility is another primary advantage of database transactions. Using transactions allows us to change values in the database without accessing sensitive information—a perfect use case for corporate employee databases. In these databases, the user will only be able to access or change their information without knowing any of the sensitive details such as database passwords or server addresses.

Avoiding Data Loss

Data loss is extremely common in the real world, with millions of people losing their data every day due to some technical difficulty or a glitch. We mentioned above that transactions are consistent, so using transactional databases will help maintain the data without any data losses due to technical errors.

Transactional databases will also reduce the risk of losing any intermediate data if there is a power cut or an unexpected system shutdown.

Database Management

Transactional databases make the jobs of many database administrators quite simple. Most transactional databases do not provide any way to change the data within a transaction to an end-user, so the user won’t be able to change anything in the transaction that can allow them to take advantage of their state.

Implementing Transactions in PostgreSQL

Transactions in PostgreSQL depend on which versions of the table rows are visible in a particular transaction. Tuple visibility is also the key to maintaining the atomicity of transactions in PostgreSQL. We can make the transactions atomic by hiding the tuples that the transaction is not supposed to see.

Non-overwriting storage is also a key to creating transactions in PostgreSQL. If you remember, one of the most important things about a transaction is consistency. If we want to preserve the data, we must maintain multiple copies of the tuple. We can only remove the tuple when there are no transactions on that particular tuple for a long time.

Implementing transactions in PostgreSQL is quite simple, thanks to years of documentation and support from developers around the globe. Now, let's see how we implement the transactions in PostgreSQL.

Practical Example

We’ll see two examples of how to implement transactions. Follow the below steps to start working with your PostgreSQL in Arctype.

First, create a PostgreSQL database using the below command.

createdb -U postgres MyDB

The above command creates a database with the name ‘MyDB’ as the superuser. You need to use the password for this user later on. If you haven’t changed anything during the installation, the database will be up and running on the default port 5432.

Now, we can start configuring the database. For this example, we'll make simple MMORPG style database with two tables— player_character and skill_table. The player table will contain the list of all the players and various elements like level, name, etc. The skill table will consist of all the skills in the game. Each player can have multiple skills.

We can create this table using the below command:

CREATE TABLE [IF NOT EXISTS] player_character( 
    user_id serial PRIMARY KEY,
    player_name VARCHAR ( 50 ) UNIQUE NOT NULL,
    player_type VARCHAR ( 50 ) NOT NULL,
    player_level INT( 255 ) NOT NULL,
    player_skill VARCHAR(255) NOT NULL,
    player_money INT
)

Now, we’ve created a player character table with an ID, name, player type, and player level. The NOT NULL constraint tells the database that the field cannot be empty. The UNIQUE constraint tells the database that this field should be unique for all players. The PRIMARY KEY constraint indicates that the ID will be the primary identifier for the table.

Now, let’s go ahead and create three skills in a skills table.

CREATE TABLE [IF NOT EXISTS] skill_table(
    skill_id serial PRIMARY KEY,
    skill_name VARCHAR ( 50 ) UNIQUE NOT NULL,
    skill_type VARCHAR ( 50 ) NOT NULL,
)

Now, we have to join the two tables using the PostgreSQL join command. Let us see how to go about that.

SELECT
    player_skill,
    skill_name
FROM 
    player_character
INNER JOIN 
    skill_table ON player_skill = skill_name

The SELECT command selects the mentioned columns in both tables. Then, we used the FROM and INNER JOIN commands to actually link the two values. If the value of skill_name changes, the value of player_skill also changes.

Using COMMIT to Finalize Changes

Now that we’ve seen the basics, let us create a simple transaction. For this, we’ll start with an Item table.

CREATE TABLE [IF NOT EXISTS] items(
    id serial PRIMARY KEY,
    item_name VARCHAR ( 50 ) UNIQUE NOT NULL,
    price INT( 50 ) NOT NULL,
)
DECLARE ct, maxct INT;
BEGIN
    SELECT 
        player_money (*) 
    INTO 
        count1 
    FROM 
        player_character 
    WHERE 
        user_id = $(user_id );
    SELECT 
        price 
    INTO 
        maxct 
    FROM 
        items 
    WHERE 
        id = $(itemID);
    IF(count1 >= maxCount) THEN
        BEGIN;
            INSERT INTO users (user_id, ...) 
            VALUES (...)
            SET ct = ct - maxct
        COMMIT;
    END IF;
END;

The above transaction inserts the item into the user database only when the user can afford to pay for the item and then removes the money from the user account.

Using ROLLBACK to Test or Undo Changes

Another great functionality of transactions is testing. You can test UPDATE statement for example to see if it would work in practice. To do that you would use a transaction to wrap your UPDATE statement and a SELECT query (to test the update) and then reliably roll it back immediately.

Here, for example, is a test of an UPDATE to the skill_type of a particular skill.

BEGIN;
    UPDATE 
        skill_table 
    SET 
        skill_type = 'Magic'
    WHERE 
        skill_name = 'Firebreath';
    SELECT 
        skill_type 
    FROM 
        skill_table 
    WHERE 
        skill_name = 'Firebreath';
ROLLBACK; -- << Important! This Un-does your UPDATE statement above!

A transaction typically ends with a commit but since you're just testing and do not want the changes to be permanent you will just rollback.

Conclusion

Transactions are a key part of PostgreSQL and the world of data as a whole. After reading this article you can now create transactions and operate with them with full efficiency.