ちょっとしたDBの物語
タロウさんの口座に10万円入っていました。

A処理:口座引き落とし5万円
B処理:給与振り込み２０万円
AとBはタイミングがかち合いました。

A処理、残高チェック:10万円
B処理、残高チェック:10万円
B処理、10万+20万＝30万で更新
A処理、10万-5万＝5万で更新

最新口座残高は？

— SEライダー (@Sys_Rider) May 13, 2024

A処理が終わるまで、排他ロックでB処理の残高チェックはさせない

ユーザのアカウントに残高を持って計算する場合

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // データベースに接続
    dsn := "user:password@tcp(127.0.0.1:3306)/bank"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // トランザクション1: 入金処理
    err = processTransaction(db, "deposit", 1, 100)
    if err != nil {
        log.Printf("Transaction 1 failed: %v\n", err)
    }

    // トランザクション2: 引き落とし処理
    err = processTransaction(db, "withdrawal", 1, 100)
    if err != nil {
        log.Printf("Transaction 2 failed: %v\n", err)
    }

    // 最終残高を表示
    finalBalance, err := getBalance(db, 1)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Final balance: %d\n", finalBalance)
}

func processTransaction(db *sql.DB, transactionType string, accountId int, amount int) error {
    // トランザクションを開始
    tx, err := db.Begin()
    if err != nil {
        return err
    }

    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            tx.Rollback()
        } else {
            err = tx.Commit()
        }
    }()

    var balance int
    // 行ロックを使用して残高を読み込む
    err = tx.QueryRow("SELECT balance FROM accounts WHERE id = ? FOR UPDATE", accountId).Scan(&balance)
    if err != nil {
        return err
    }

    var newBalance int
    if transactionType == "withdrawal" {
        if balance >= amount {
            newBalance = balance - amount
        } else {
            return fmt.Errorf("insufficient funds")
        }
    } else if transactionType == "deposit" {
        newBalance = balance + amount
    } else {
        return fmt.Errorf("invalid transaction type")
    }

    _, err = tx.Exec("UPDATE accounts SET balance = ? WHERE id = ?", newBalance, accountId)
    if err != nil {
        return err
    }

    fmt.Printf("%s %d. New balance: %d\n", transactionType, amount, newBalance)
    return nil
}

func getBalance(db *sql.DB, accountId int) (int, error) {
    var balance int
    err := db.QueryRow("SELECT balance FROM accounts WHERE id = ?", accountId).Scan(&balance)
    if err != nil {
        return 0, err
    }
    return balance, nil
}

取引履歴から残高を計算する場合

実値を入れずに更新履歴をインサートで積み重ね、
オンライン処理時は都度残高と更新履歴で計算
残高は夜間バッチで更新しましょう。
RDBMSはテーブルデータの増減処理が得意ではないですからね。

— ミルディア/虚金＠RO民(+PBW+TRPG+SE) (@mildia_distern) May 13, 2024

 オンライン処理

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // データベースに接続
    dsn := "user:password@tcp(127.0.0.1:3306)/bank"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // トランザクション1: 入金処理
    err = processTransaction(db, "deposit", 1, 100)
    if err != nil {
        log.Printf("Transaction 1 failed: %v\n", err)
    }

    // トランザクション2: 引き落とし処理
    err = processTransaction(db, "withdrawal", 1, 100)
    if err != nil {
        log.Printf("Transaction 2 failed: %v\n", err)
    }

    // 最終残高を表示
    finalBalance, err := getBalance(db, 1)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Final balance: %d\n", finalBalance)
}

func processTransaction(db *sql.DB, transactionType string, accountId int, amount int) error {
    // トランザクションを開始
    tx, err := db.Begin()
    if err != nil {
        return err
    }

    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            tx.Rollback()
        } else {
            err = tx.Commit()
        }
    }()

    // 残高を計算し、ロックをかける
    var currentBalance int
    err = tx.QueryRow("SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE account_id = ? FOR UPDATE", accountId).Scan(&currentBalance)
    if err != nil {
        return err
    }

    var amountSigned int
    if transactionType == "withdrawal" {
        if currentBalance < amount {
            return fmt.Errorf("insufficient funds")
        }
        amountSigned = -amount
    } else if transactionType == "deposit" {
        amountSigned = amount
    } else {
        return fmt.Errorf("invalid transaction type")
    }

    // 取引履歴を挿入
    _, err = tx.Exec("INSERT INTO transactions (account_id, transaction_type, amount) VALUES (?, ?, ?)", accountId, transactionType, amountSigned)
    if err != nil {
        return err
    }

    fmt.Printf("%s %d\n", transactionType, amount)
    return nil
}

func getBalance(db *sql.DB, accountId int) (int, error) {
    var balance int
    err := db.QueryRow("SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE account_id = ?", accountId).Scan(&balance)
    if err != nil {
        return 0, err
    }
    return balance, nil
}

夜間バッチで残高を計算

func batchUpdateBalances(db *sql.DB) error {
    rows, err := db.Query("SELECT id FROM accounts")
    if err != nil {
        return err
    }
    defer rows.Close()

    for rows.Next() {
        var accountId int
        if err := rows.Scan(&accountId); err != nil {
            return err
        }

        balance, err := calculateBalance(db, accountId)
        if err != nil {
            return err
        }

        _, err = db.Exec("UPDATE accounts SET balance = ? WHERE id = ?", balance, accountId)
        if err != nil {
            return err
        }
    }

    return nil
}

ホテルの予約の場合

引用先も含めてコメントみるとTwitter界隈は9割くらいトランザクションを意識している。

ただ現実世界では自分の経験上『トランザクションって何？』と、言う人が多い。
そして同じ席を2つ予約出来ちゃうようなシステムになる。
ECの在庫計算も同様。

コレは某資格の試験でよくある物語です。

— SEライダー (@Sys_Rider) May 13, 2024

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // データベースに接続
    dsn := "user:password@tcp(127.0.0.1:3306)/hotel"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 予約を試行
    err = bookRoom(db, 101, "Taro Yamada", "2024-05-20", "2024-05-25")
    if err != nil {
        log.Printf("Booking failed: %v\n", err)
    }

    // もう一度同じ部屋を予約しようとする
    err = bookRoom(db, 101, "Hanako Yamada", "2024-05-20", "2024-05-25")
    if err != nil {
        log.Printf("Booking failed: %v\n", err)
    }
}

func bookRoom(db *sql.DB, roomNumber int, guestName string, startDate string, endDate string) error {
    // トランザクションを開始
    tx, err := db.Begin()
    if err != nil {
        return err
    }

    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            tx.Rollback()
        } else {
            err = tx.Commit()
        }
    }()

    var roomId int
    var available bool
    // 部屋の空き状況を確認し、ロックをかける
    err = tx.QueryRow("SELECT id, available FROM rooms WHERE room_number = ? FOR UPDATE", roomNumber).Scan(&roomId, &available)
    if err != nil {
        return err
    }

    if !available {
        return fmt.Errorf("room %d is already booked", roomNumber)
    }

    // 予約を挿入
    _, err = tx.Exec("INSERT INTO bookings (room_id, guest_name, start_date, end_date) VALUES (?, ?, ?, ?)", roomId, guestName, startDate, endDate)
    if err != nil {
        return err
    }

    // 部屋のステータスを更新
    _, err = tx.Exec("UPDATE rooms SET available = FALSE WHERE id = ?", roomId)
    if err != nil {
        return err
    }

    fmt.Printf("Room %d successfully booked for %s from %s to %s\n", roomNumber, guestName, startDate, endDate)
    return nil
}

Amazonおすすめ

iPad 9世代 2021年最新作

iPad 9世代出たから買い替え。安いぞ！🐱 初めてならiPad。Kindleを外で見るならiPad mini。ほとんどの人には通常のiPadをおすすめします＞＜