ちょっとした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(¤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
}
関連記事 - More from my site -
MySQL デッドロック対処
Plesk Onyx メールが受信出来ないトラブル対応
Laravel Paginateを使わないリスト取得
ディレクトリ、ファイルの権限をセキュアに設定- コードレビュー指摘ポイント PHP Laravel
指数対数基本性質
Gmail活用術
Vue.jsのお勉強
掛け算のイメージ図
WordPress アカウントのパスワード、権限をMySQLから変更する
Windows ファイル自動削除 FORFILES
Out of memory: Kill process xxxx (httpd) 対応
カジュアル面談チートシート
Xperia Z3+Lineモバイルでテザリングを有効化、データの移行手順