DB Transaction

TL;DR

• DB transactions ensure that:

  • The data we persist is consistent

  • Make sure your DB transactions follow the ACID properties

• Each action of the application is reliable

• If all the constraints are valid, the system will commit to the DB; otherwise, the system will roll back

  What is a DB transaction?

  A DB transaction involves multiple DB actions combined into a complex function to accomplish specific requirements.

  If the process is successful, that's great! We will commit our modifications to the DB:

    BEGIN;
    ... 
    COMMIT;
  

  But if our process encounters failure, we must ensure that every collection remains unchanged:

    BEGIN;
    ... 
    ROLLBACK;
  

  That's why the ACID properties exist!

  ACID Properties

  1. Atomicity: Either all operations complete successfully, or the transaction fails and the DB remains unchanged.

  2. Consistency: The DB state must be valid after the transaction. All constraints must be satisfied.

  3. Isolation: Concurrent transactions must not affect each other.

  4. Durability: Data written by a successful transaction must be recorded in persistent storage.

How to implement a DB transaction in Go

Before we start our implementation, you should understand how to use SQLC to generate your CRUD with PostgreSQL.

• Create a file store.go in the DB-related directory.

• Define a Store struct and a function to create a new store.

    // Store provides all functions to execute db queries and transactions 
    type Store struct {
        *Queries     
        connPool *pgxpool.Pool 
    }  

    // NewStore creates a new Store 
    func NewStore(connPool *pgxpool.Pool) *Store {
        return &Store{
         connPool: connPool,         
         Queries:  New(connPool),     
        } 
    }

• Write an execTx function to execute a transaction.

• The execTx function isn't exported because it's a basic function for transactions.

    func (store *Store) execTx(ctx context.Context, fn func(*Queries) error) error {
        tx, err := store.connPool.Begin(ctx)
        if err != nil {
            return err
        }

        // New is provided by SQLC
        q := New(tx)

        // Catch the error of a transaction
        err = fn(q)
        if err != nil {
            if rbErr := tx.Rollback(ctx); rbErr != nil {
                return fmt.Errorf("tx err: %v, rb err: %v", err, rbErr)
            }
            return err
        }

        return tx.Commit(ctx)
    }

Why do we need a store?

Our purpose is to ensure that we follow the ACID properties.

You probably noticed that the execTx creates a new query for every transaction. This is because every transaction should have an independent context to prevent affecting each other:

    q := New(tx)

For this reason, we can't just use Store to manipulate the queries, as it would become a nightmare and block your process.

Example in Go

Here is a simple example of using execTx to perform a transaction.

Suppose we are going to write a simple bank application, and we define 3 collections:

• Accounts: Every user should have an account.

• Entries: Record the money in & out.

• Transfers: Track the money going from one account to another.

Transfer Transaction

Since transfer is a common feature for bank applications, we use it as an example to explain how to execute our transaction.

In TransferTx, we have to:

• Create a transfer record.

• Create entries for both accounts.

• Update the accounts' balances.

    // TransferTxParams contains the parameters of the transfer transaction
    type TransferTxParams struct {
        FromAccountID int64 `json:"from_account_id"`
        ToAccountID   int64 `json:"to_account_id"`
        Amount        int64 `json:"amount"`
    }

    // TransferTxResult is the result of the transfer transaction
    type TransferTxResult struct {
        Transfer    Transfer `json:"transfer"`
        FromAccount Account  `json:"from_account"`
        ToAccount   Account  `json:"to_account"`
        FromEntry   Entry    `json:"from_entry"`
        ToEntry     Entry    `json:"to_entry"`
    }

    func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferResult, error) {
        var result TransferResult

        err := store.execTx(ctx, func(q *Queries) error {
            var err error

            // ...create transfer
            if err != nil {
                return err
            }

            // ...create entries
            if err != nil {
                return err
            }

            // ...update balance
            if err != nil {
                return err
            }

            return nil
        })

        return result, err
    }

In the code above, we see the function we pass as a parameter, which is a closure that retains the content of arg TransferTxParams until the transaction is done.

---

About me

• Eric Lin (he/him)

• 25 years old

• A full-stack developer, responsible for 5G private network CMS R&D

• Taipei

• Contact me: Instagram

If you like this post, don't forget to follow me for more informations.