数据库事务与隔离级别：深入理解ACID

最新推荐文章于 2026-06-17 22:56:21 发布

原创最新推荐文章于 2026-06-17 22:56:21 发布 · 156 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#linux #系统内核 #嵌入式

数据库事务与隔离级别：深入理解ACID

引言

数据库事务是现代应用系统数据一致性的基石。在并发环境下，事务处理不当会导致脏读、不可重复读、幻读等一系列问题。理解事务的隔离级别及其实现原理，是每个后端开发者必须掌握的核心技能。

一、ACID特性详解

1.1 事务的ACID定义

Atomicity（原子性）：事务是最小执行单位，要么全部成功，要么全部失败回滚
Consistency（一致性）：事务执行前后，数据库状态必须保持一致
Isolation（隔离性）：并发执行的事务相互隔离，不互相干扰
Durability（持久性）：事务提交后，其结果永久保存

1.2 Go语言事务处理

package database

import (
    "context"
    "database/sql"
    "fmt"
)

type TransactionManager struct {
    db *sql.DB
}

func NewTransactionManager(db *sql.DB) *TransactionManager {
    return &TransactionManager{db: db}
}

type TxFn func(tx *sql.Tx) error

func (tm *TransactionManager) WithTransaction(ctx context.Context, fn TxFn) error {
    tx, err := tm.db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }

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

    if err := fn(tx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("tx error: %v, rollback error: %w", err, rbErr)
        }
        return err
    }

    if err := tx.Commit(); err != nil {
        return fmt.Errorf("failed to commit transaction: %w", err)
    }

    return nil
}

func (tm *TransactionManager) WithReadOnlyTransaction(ctx context.Context, fn TxFn) error {
    tx, err := tm.db.BeginTx(ctx, &sql.TxOptions{
        ReadOnly: true,
        Isolation: sql.LevelReadCommitted,
    })
    if err != nil {
        return fmt.Errorf("failed to begin read-only transaction: %w", err)
    }

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

    if err := fn(tx); err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

二、隔离级别详解

2.1 四种隔离级别

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	可能	可能	可能
Read Committed	不可能	可能	可能
Repeatable Read	不可能	不可能	可能
Serializable	不可能	不可能	不可能

2.2 设置MySQL隔离级别

-- 查看当前会话隔离级别
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;

-- 查看全局隔离级别
SELECT @@global.tx_isolation;

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 开启事务时指定隔离级别
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

2.3 Go语言设置隔离级别

package database

import (
    "context"
    "database/sql"
    "fmt"
)

type IsolationLevelExample struct {
    db *sql.DB
}

func NewIsolationLevelExample(db *sql.DB) *IsolationLevelExample {
    return &IsolationLevelExample{db: db}
}

func (i *IsolationLevelExample) ExecuteWithReadCommitted(ctx context.Context, fn func(tx *sql.Tx) error) error {
    tx, err := i.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelReadCommitted,
    })
    if err != nil {
        return err
    }

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

    if err := fn(tx); err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

func (i *IsolationLevelExample) ExecuteWithRepeatableRead(ctx context.Context, fn func(tx *sql.Tx) error) error {
    tx, err := i.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelRepeatableRead,
    })
    if err != nil {
        return err
    }

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

    if err := fn(tx); err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

三、并发问题详解

3.1 脏读（Dirty Read）

事务A读取了事务B未提交的数据：

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 读到事务B未提交的数据: 1000

-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- ROLLBACK;  -- 回滚，balance恢复为500

-- 事务A
SELECT balance FROM accounts WHERE id = 1;  -- 读到回滚后的数据: 500
COMMIT;

3.2 不可重复读（Non-repeatable Read）

同一事务中，两次读取同一行数据结果不同：

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 第一次读: 500

-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = 1000 WHERE id = 1;
COMMIT;

-- 事务A
SELECT balance FROM accounts WHERE id = 1;  -- 第二次读: 1000，结果不同
COMMIT;

3.3 幻读（Phantom Read）

同一事务中，两次查询返回的记录数不同：

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 第一次: 10条

-- 事务B
START TRANSACTION;
INSERT INTO orders (status) VALUES ('pending');
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- 事务A
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 第二次: 12条，多了两条
COMMIT;

四、乐观锁与悲观锁

4.1 乐观锁实现

乐观锁假设并发冲突较少，适合读多写少场景：

package database

import (
    "context"
    "database/sql"
    "fmt"
)

type OptimisticLock struct {
    db *sql.DB
}

func NewOptimisticLock(db *sql.DB) *OptimisticLock {
    return &OptimisticLock{db: db}
}

type VersionedEntity struct {
    ID        int64
    Name      string
    Version   int64
    UpdatedAt sql.NullTime
}

func (ol *OptimisticLock) Update(ctx context.Context, entity *VersionedEntity) error {
    query := `
        UPDATE entities
        SET name = ?, version = version + 1, updated_at = NOW()
        WHERE id = ? AND version = ?
    `

    result, err := ol.db.ExecContext(ctx, query, entity.Name, entity.ID, entity.Version)
    if err != nil {
        return fmt.Errorf("failed to update entity: %w", err)
    }

    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return fmt.Errorf("failed to get rows affected: %w", err)
    }

    if rowsAffected == 0 {
        return fmt.Errorf("concurrent modification detected: entity was modified by another transaction")
    }

    return nil
}

func (ol *OptimisticLock) UpdateWithRetry(ctx context.Context, entity *VersionedEntity, maxRetries int) error {
    var lastErr error

    for i := 0; i < maxRetries; i++ {
        currentEntity, err := ol.GetByID(ctx, entity.ID)
        if err != nil {
            return err
        }

        entity.Version = currentEntity.Version

        if err := ol.Update(ctx, entity); err != nil {
            lastErr = err
            continue
        }

        return nil
    }

    return fmt.Errorf("update failed after %d retries: %w", maxRetries, lastErr)
}

func (ol *OptimisticLock) GetByID(ctx context.Context, id int64) (*VersionedEntity, error) {
    query := `
        SELECT id, name, version, updated_at
        FROM entities
        WHERE id = ?
    `

    entity := &VersionedEntity{}
    err := ol.db.QueryRowContext(ctx, query, id).Scan(
        &entity.ID,
        &entity.Name,
        &entity.Version,
        &entity.UpdatedAt,
    )
    if err != nil {
        return nil, fmt.Errorf("failed to get entity: %w", err)
    }

    return entity, nil
}

4.2 悲观锁实现

悲观锁假设并发冲突较多，使用锁来避免冲突：

package database

import (
    "context"
    "database/sql"
    "fmt"
)

type PessimisticLock struct {
    db *sql.DB
}

func NewPessimisticLock(db *sql.DB) *PessimisticLock {
    return &PessimisticLock{db: db}
}

func (pl *PessimisticLock) LockForUpdate(ctx context.Context, tx *sql.Tx, id int64) (*VersionedEntity, error) {
    query := `
        SELECT id, name, version, updated_at
        FROM entities
        WHERE id = ?
        FOR UPDATE
    `

    entity := &VersionedEntity{}
    err := tx.QueryRowContext(ctx, query, id).Scan(
        &entity.ID,
        &entity.Name,
        &entity.Version,
        &entity.UpdatedAt,
    )
    if err != nil {
        return nil, fmt.Errorf("failed to lock entity: %w", err)
    }

    return entity, nil
}

func (pl *PessimisticLock) Transfer(ctx context.Context, fromID, toID int64, amount float64) error {
    return pl.withTransaction(ctx, func(tx *sql.Tx) error {
        var fromBalance float64
        query := `SELECT balance FROM accounts WHERE id = ? FOR UPDATE`
        if err := tx.QueryRowContext(ctx, query, fromID).Scan(&fromBalance); err != nil {
            return fmt.Errorf("failed to get from balance: %w", err)
        }

        if fromBalance < amount {
            return fmt.Errorf("insufficient balance")
        }

        query = `UPDATE accounts SET balance = balance - ? WHERE id = ?`
        if _, err := tx.ExecContext(ctx, query, amount, fromID); err != nil {
            return fmt.Errorf("failed to deduct balance: %w", err)
        }

        query = `UPDATE accounts SET balance = balance + ? WHERE id = ?`
        if _, err := tx.ExecContext(ctx, query, amount, toID); err != nil {
            return fmt.Errorf("failed to add balance: %w", err)
        }

        return nil
    })
}

func (pl *PessimisticLock) withTransaction(ctx context.Context, fn func(*sql.Tx) error) error {
    tx, err := pl.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,
    })
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }

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

    if err := fn(tx); err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

五、死锁处理

5.1 死锁检测与处理

package database

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "time"
)

type DeadlockHandler struct {
    db *sql.DB
    maxRetries int
}

func NewDeadlockHandler(db *sql.DB, maxRetries int) *DeadlockHandler {
    return &DeadlockHandler{
        db:         db,
        maxRetries: maxRetries,
    }
}

func (dh *DeadlockHandler) ExecuteWithDeadlockRetry(ctx context.Context, fn func() error) error {
    var lastErr error

    for i := 0; i < dh.maxRetries; i++ {
        err := fn()
        if err == nil {
            return nil
        }

        if !dh.isDeadlockError(err) {
            return err
        }

        lastErr = err
        waitTime := time.Duration(i+1) * 100 * time.Millisecond

        log.Printf("Deadlock detected, retrying in %v (attempt %d/%d): %v",
            waitTime, i+1, dh.maxRetries, err)

        select {
        case <-ctx.Done():
            return ctx.Err()
        case <-time.After(waitTime):
        }
    }

    return fmt.Errorf("operation failed after %d deadlock retries: %w", dh.maxRetries, lastErr)
}

func (dh *DeadlockHandler) isDeadlockError(err error) bool {
    if err == nil {
        return false
    }

    errStr := err.Error()
    deadlockIndicators := []string{
        "Deadlock found",
        "deadlock",
        "Lock wait timeout",
    }

    for _, indicator := range deadlockIndicators {
        if contains(errStr, indicator) {
            return true
        }
    }

    return false
}

func contains(s, substr string) bool {
    return len(s) >= len(substr) && (s == substr || len(s) > 0 && containsHelper(s, substr))
}

func containsHelper(s, substr string) bool {
    for i := 0; i <= len(s)-len(substr); i++ {
        if s[i:i+len(substr)] == substr {
            return true
        }
    }
    return false
}

5.2 避免死锁的策略

package database

type DeadlockPrevention struct {
    db *sql.DB
}

func NewDeadlockPrevention(db *sql.DB) *DeadlockPrevention {
    return &DeadlockPrevention{db: db}
}

func (dp *DeadlockPrevention) TransferInOrder(ctx context.Context, fromID, toID int64, amount float64) error {
    if fromID > toID {
        fromID, toID = toID, fromID
    }

    query := `
        UPDATE accounts
        SET balance = CASE
            WHEN id = ? THEN balance - ?
            WHEN id = ? THEN balance + ?
        END
        WHERE id IN (?, ?)
    `

    _, err := dp.db.ExecContext(ctx, query, fromID, amount, toID, amount, fromID, toID)
    return err
}

func (dp *DeadlockPrevention) BatchUpdate(ctx context.Context, updates []AccountUpdate) error {
    query := `UPDATE accounts SET balance = balance + ? WHERE id = ?`

    _, err := dp.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }

    for _, update := range updates {
        _, err := dp.db.ExecContext(ctx, query, update.Delta, update.ID)
        if err != nil {
            return err
        }
    }

    return dp.db.Commit()
}

type AccountUpdate struct {
    ID    int64
    Delta float64
}

六、分布式事务

6.1 Saga模式实现

package saga

import (
    "context"
    "fmt"
)

type Saga struct {
    steps    []SagaStep
    name     string
}

type SagaStep struct {
    Name    string
    Forward func(ctx context.Context) error
    Backward func(ctx context.Context) error
}

func NewSaga(name string) *Saga {
    return &Saga{
        steps: make([]SagaStep, 0),
        name:  name,
    }
}

func (s *Saga) AddStep(step SagaStep) {
    s.steps = append(s.steps, step)
}

func (s *Saga) Execute(ctx context.Context) error {
    executed := make([]int, 0)

    for i, step := range s.steps {
        if err := step.Forward(ctx); err != nil {
            for j := len(executed) - 1; j >= 0; j-- {
                executedStep := s.steps[executed[j]]
                if err := executedStep.Backward(ctx); err != nil {
                    return fmt.Errorf("saga %s failed to rollback at step %s: %w",
                        s.name, executedStep.Name, err)
                }
            }
            return fmt.Errorf("saga %s failed at step %s: %w", s.name, step.Name, err)
        }
        executed = append(executed, i)
    }

    return nil
}

type OrderSaga struct {
    saga *Saga
}

func NewOrderSaga() *OrderSaga {
    return &OrderSaga{
        saga: NewSaga("order-creation"),
    }
}

func (os *OrderSaga) WithDeduction(accountID int64, amount float64) *OrderSaga {
    os.saga.AddStep(SagaStep{
        Name: "deduct-inventory",
        Forward: func(ctx context.Context) error {
            fmt.Printf("Deducting inventory for account %d: %.2f\n", accountID, amount)
            return nil
        },
        Backward: func(ctx context.Context) error {
            fmt.Printf("Restoring inventory for account %d: %.2f\n", accountID, amount)
            return nil
        },
    })
    return os
}

func (os *OrderSaga) WithPayment(paymentID string, amount float64) *OrderSaga {
    os.saga.AddStep(SagaStep{
        Name: "process-payment",
        Forward: func(ctx context.Context) error {
            fmt.Printf("Processing payment %s: %.2f\n", paymentID, amount)
            return nil
        },
        Backward: func(ctx context.Context) error {
            fmt.Printf("Refunding payment %s: %.2f\n", paymentID, amount)
            return nil
        },
    })
    return os
}

func (os *OrderSaga) WithNotification(orderID string) *OrderSaga {
    os.saga.AddStep(SagaStep{
        Name: "send-notification",
        Forward: func(ctx context.Context) error {
            fmt.Printf("Sending notification for order %s\n", orderID)
            return nil
        },
        Backward: func(ctx context.Context) error {
            fmt.Printf("No need to rollback notification for order %s\n", orderID)
            return nil
        },
    })
    return os
}

func (os *OrderSaga) Execute(ctx context.Context) error {
    return os.saga.Execute(ctx)
}