What is a transaction in a database?

A database transaction is a sequence of operations that are executed as a single unit. The main idea of a transaction is to ensure data integrity, even if a failure or error occurs.

A transaction has four properties (ACID):

• Atomicity: all operations within the transaction are either fully completed or not executed at all.
• Consistency: the transaction transitions the database from one valid state to another.
• Isolation: concurrent transactions do not affect each other.
• Durability: after a transaction is committed, its changes are saved even in case of failures.

In Go, the database/sql package is often used for working with transactions. Example of using a transaction:

import (
    "database/sql"
    _ "github.com/lib/pq" // PostgreSQL driver
)

func transferFunds(db *sql.DB, fromAccount, toAccount string, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback() // rollback if Commit is not called

    _, err = tx.Exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromAccount)
    if err != nil {
        return err
    }

    _, err = tx.Exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toAccount)
    if err != nil {
        return err
    }

    return tx.Commit()
}

In this example, both balance updates are performed within a single transaction, ensuring data integrity.