ちょっとしたDBの物語
タロウさんの口座に10万円入っていました。A処理:口座引き落とし5万円
B処理:給与振り込み20万円
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(¤tBalance) 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 }