CodeTips#8: SQL Transactions A Dive into Basic Concepts with Real-World Examples

Last Updated on September 4, 2024

We live in an era when we are increasingly automating our day-to-day processes. We no longer go to bank agencies, but instead use digital banks that allow us to carry out everyday operations such as payments, balance inquiries, and transfers online, as well as no longer buying products, be it food, household appliances, electronics, etc. in physical stores, but rather carrying out those activities in the comfort of our own homes, on e-commerces such as Amazon, e-bay, etc.

However, with all this convenience it brings, we as consumers often don’t stop to think (and don’t need to) about how these processes are carried out in such a precise way, where error and success flows are dealt with in a precise manner, whether it’s ensuring the consistency of bank balances after a transfer or refunding the amount of purchase on a credit card after a connection error, for example.

For these error treatments and successes to occur in the best way, we developers need to apply concepts and best practices to deliver a satisfactory experience for our end users. And one of these concepts that we’re going to cover and learn how to use is that of Transactions.

So what are Transactions?

Generally, from the user’s point of view, a set of several operations (such as the processes listed above) in the database appears to be unique. For example, an e-commerce purchase of a certain product X, last in stock, is a single operation from the user’s point of view; for the database, however, it consists of several operations. It is essential that either all of these operations happen successfully, or that, in the event of failure, none take place. It would be unacceptable for the next user to be able to buy the same product X that is no longer in stock.

A transaction is therefore a single execution that accesses and updates several records. A transaction can usually be written in a data manipulation language (SQL) or programming language (Java, C, JavaScript, Ruby, etc.), where it is delimited by statements that indicate the start and end of a transaction. The steps between the start and end statements must appear to the end user as a single, indivisible step, whether it can be executed in its entirety or not.

However, if a transaction starts to execute, but fails for whatever reason, all the changes to the database that the transaction may have made must be undone. This requirement guarantees the atomicity of the transaction, where regardless of any internal or external failure everything or nothing is executed.

With this, we have the consistency of our database, where operations are atomically executed, maintaining data integrity and validity, isolating concurrent transactions, ensuring that the result of a transaction is independent of other concurrent transactions, and guaranteeing the persistence of changes. And even if our application guarantees the correct execution of a transaction, this could be useless if the system fails later. Therefore, for our transaction to be considered durable, once a transaction is confirmed, its changes must be reflected in the database, even after a failure.

The set of these 4 concepts: Atomicity, Consistency, Isolation, and Durability, form the famous acronym ACID.

Let’s practice

Well, enough theory! Let’s get down to practical work, and there’s nothing better than simulating a real case to better assimilate the concepts explained above. The scenario we’re going to work on is based on this simple model of an e-commerce store.

As we can see, our little e-commerce is made up of just 4 entities: Order, which is made up of 1 or N Order Items, Product, and the Inventory of the respective Product that makes up an Order Item. And finally, the Product itself can have 1 or N Inventories.

To apply the concept of Transactions, we’ll write an SQL query that decrements the number of Products in a given Inventory after an order has been confirmed.

BEGIN TRANSACTION;

UPDATE orders SET status = 'confirmed' WHERE id = 1;

UPDATE
  inventories
SET
  quantity = quantity - (
    SELECT
      SUM(order_items.quantity)
    FROM
      order_items
      JOIN orders ON orders.id = order_items.order_id
    WHERE
      order_items.inventory_id = inventories.id
      AND orders.id = 1
  )
WHERE
  id IN (
    SELECT
      order_items.inventory_id
    FROM
      order_items
      JOIN orders ON orders.id = order_items.order_id
    WHERE
      orders.id = 1
  );

COMMIT;

So, as you can see, the definition of a basic transaction in SQL is very simple, we just define a block starting with BEGIN TRANSACTION; and then after performing all our necessary operations, at the end of them we have to confirm (persist) them, performing via COMMIT operator, but in case of security tests, it is possible to perform ROLLBACK to undo our changes and guarantee the integrity of the previous state of our data.

Remember that the SQL query above was performed on a SQLite database, so some keywords may vary in other SQL databases (Postgres, MySQL, Oracle, etc.).

Congratulations! If you’ve made it this far you’ve successfully executed your SQL Transaction! However, in our day-to-day lives, we as software developers don’t need to manually write our SQL queries, only in cases where we need some fine-tuning to ensure the best performance and lowest possible operating costs, other than that, we generally use Object-Relation-Map (ORM) tools, Query builders, which take care of a lot of the most common cases, abstracting SQL writing and allowing us to manipulate our database using our favorite programming language, for example Assembly 🤓. Jokes aside, let’s implement the same transaction above but this time using the Ruby language in conjunction with the Rails framework, using the Active Record ORM.

class ConfirmOrderService
    # ...
    def call
        ActiveRecord::Base.transaction do
            ensure_order_exists_and_pending

            update_inventories_quantity

            confirm_order
        end
    rescue ActiveRecord::RecordNotFound
        raise OrderNotFoundError
    end

    private

    def ensure_order_exists_and_pending
        raise ActiveRecord::RecordNotFound unless Order.exists?(id: order&.id, status: 'pending')
    end

    def update_inventories_quantity
        items_quantities_map = order_items.map { |item| [item.inventory.id, item.quantity] }.to_h
            inventories_ids = order_items.pluck(:inventory_id)
            inventories = Inventory.where(id: inventories_ids)

            inventories.each do |inventory|
            inventory.quantity -= items_quantities_map[inventory.id]
        end

        upsert_inventories(inventories)
    end

    def upsert_inventories(inventories)
        Inventory.upsert_all(
            inventories.map { |inventory| { id: inventory.id, quantity: inventory.quantity, supplier: inventory.supplier } },
            unique_by: :id
        )
    end

    def confirm_order
        order.status = 'confirmed'
        order.save!
    end
end

Click here to check the source code.

As you can notice, the way ActiveRecord abstracts the use of transactions makes our lives a lot easier when we are making queries and writing to multiple tables/entities in our database through the ActiveRecord::Base.transaction block, which guarantees the rollback of our changes in the event of an error, as well as committing them without success.

Wrapping up

Our journey through understanding transactions unveils the importance of keeping the integrity of our data. Whether it’s processing payments, managing inventory, or confirming orders, the precision required to ensure error-free execution is the main point.

Through the examples above, we’ve understood the practical application of transactions through raw SQL queries or the high-level abstractions provided by ORM frameworks like ActiveRecord in Ruby on Rails. These tools help us developers handle database management’s complexities with confidence, abstracting away the intricacies while upholding the fundamental tenets of transactional integrity.