130 lines
4.0 KiB
Go
130 lines
4.0 KiB
Go
// Package mysql owns SQL connections, explicit migrations and transaction boundaries.
|
|
package mysql
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"errors"
|
|
"sync/atomic"
|
|
"time"
|
|
|
|
driver "github.com/go-sql-driver/mysql"
|
|
)
|
|
|
|
// Open configures UTC, bounded I/O and an explicit connection pool. It never
|
|
// applies migrations and never logs the DSN. TLS is chosen in the secret DSN.
|
|
func Open(ctx context.Context, dsn string, maxOpen, maxIdle int) (*sql.DB, error) {
|
|
if maxOpen <= 0 || maxIdle < 0 || maxIdle > maxOpen {
|
|
return nil, errors.New("invalid database pool configuration")
|
|
}
|
|
cfg, err := driver.ParseDSN(dsn)
|
|
if err != nil {
|
|
return nil, errors.New("invalid MySQL DSN")
|
|
}
|
|
if cfg.DBName == "" {
|
|
return nil, errors.New("MySQL database name required")
|
|
}
|
|
cfg.ParseTime = true
|
|
cfg.Loc = time.UTC
|
|
cfg.MultiStatements = false
|
|
cfg.InterpolateParams = false
|
|
cfg.Timeout = 5 * time.Second
|
|
cfg.ReadTimeout = 10 * time.Second
|
|
cfg.WriteTimeout = 10 * time.Second
|
|
if cfg.Params == nil {
|
|
cfg.Params = map[string]string{}
|
|
}
|
|
cfg.Params["time_zone"] = "'+00:00'"
|
|
cfg.Params["sql_mode"] = "'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"
|
|
connector, err := driver.NewConnector(cfg)
|
|
if err != nil {
|
|
return nil, errors.New("invalid MySQL connection configuration")
|
|
}
|
|
db := sql.OpenDB(connector)
|
|
db.SetMaxOpenConns(maxOpen)
|
|
db.SetMaxIdleConns(maxIdle)
|
|
db.SetConnMaxLifetime(5 * time.Minute)
|
|
db.SetConnMaxIdleTime(time.Minute)
|
|
if err := db.PingContext(ctx); err != nil {
|
|
db.Close()
|
|
return nil, errors.New("MySQL connection failed; check endpoint, credentials and TLS configuration")
|
|
}
|
|
return db, nil
|
|
}
|
|
|
|
const transactionRetryLimit = 2
|
|
|
|
var transactionRetries atomic.Uint64
|
|
var transactionRetryExhausted atomic.Uint64
|
|
|
|
// TransactionStats 是本进程提交前事务重试的累计快照。
|
|
// 计数器只记录明确的 MySQL 锁等待超时或死锁回调失败，不记录提交回执不明。
|
|
type TransactionStats struct {
|
|
Retries uint64 // 已实际执行的重试次数。
|
|
Exhausted uint64 // 达到重试上限的事务次数。
|
|
}
|
|
|
|
// ReadTransactionStats 返回本进程事务重试计数，不访问数据库。
|
|
func ReadTransactionStats() TransactionStats {
|
|
return TransactionStats{
|
|
Retries: transactionRetries.Load(),
|
|
Exhausted: transactionRetryExhausted.Load(),
|
|
}
|
|
}
|
|
|
|
// WithTx retries only a callback failure caused by a MySQL deadlock or lock wait
|
|
// timeout before COMMIT. It never retries Begin/Commit errors because a commit
|
|
// response may be lost after the database has already applied the transaction;
|
|
// callers must reconcile such an ambiguous write by stable operation identity.
|
|
func WithTx(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
|
|
for attempt := 0; attempt <= transactionRetryLimit; attempt++ {
|
|
err, retryable := withTxAttempt(ctx, db, fn)
|
|
if err == nil || !retryable || attempt == transactionRetryLimit {
|
|
if retryable && attempt == transactionRetryLimit {
|
|
transactionRetryExhausted.Add(1)
|
|
}
|
|
return err
|
|
}
|
|
if err := waitTransactionRetry(ctx, attempt); err != nil {
|
|
return err
|
|
}
|
|
transactionRetries.Add(1)
|
|
}
|
|
return errors.New("transaction retry loop exhausted")
|
|
}
|
|
|
|
func withTxAttempt(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) (error, bool) {
|
|
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
|
|
if err != nil {
|
|
return err, false
|
|
}
|
|
defer tx.Rollback()
|
|
if err := fn(tx); err != nil {
|
|
return err, retryableTransactionError(err)
|
|
}
|
|
if err := tx.Commit(); err != nil {
|
|
return err, false
|
|
}
|
|
return nil, false
|
|
}
|
|
|
|
func retryableTransactionError(err error) bool {
|
|
var mysqlErr *driver.MySQLError
|
|
if !errors.As(err, &mysqlErr) {
|
|
return false
|
|
}
|
|
return mysqlErr.Number == 1205 || mysqlErr.Number == 1213
|
|
}
|
|
|
|
func waitTransactionRetry(ctx context.Context, attempt int) error {
|
|
delay := time.Duration(attempt+1) * 25 * time.Millisecond
|
|
timer := time.NewTimer(delay)
|
|
defer timer.Stop()
|
|
select {
|
|
case <-ctx.Done():
|
|
return ctx.Err()
|
|
case <-timer.C:
|
|
return nil
|
|
}
|
|
}
|