PHP小码农

Appearance

MySQLi 事务处理 ​

概述 ​

MySQL 事务是保证数据库操作原子性和一致性的关键机制。MySQLi 提供了完整的事务 API，支持 ACID 特性、保存点、隔离级别控制和自动提交管理。

事务核心特性

  • Atomicity — 原子性: 要么全部成功，要么全部失败
  • Consistency — 一致性: 事务前后数据库状态一致
  • Isolation — 隔离性: 并发事务互不影响
  • Durability — 持久性: 提交后的数据永久保存

基础概念 ​

MySQL 事务模式 ​

特性说明默认值
autocommit自动提交模式开启（1）
隔离级别事务隔离级别REPEATABLE READ
InnoDB事务引擎支持（MyISAM 不支持）

四种隔离级别 ​

隔离级别脏读不可重复读幻读适用场景
READ UNCOMMITTED可能可能可能极少使用
READ COMMITTED不可能可能可能报表查询
REPEATABLE READ不可能不可能可能默认级别
SERIALIZABLE不可能不可能不可能金融交易

语法与代码 ​

基本事务操作 ​

php
<?php
declare(strict_types=1);

$mysqli = new mysqli('localhost', 'user', 'pass', 'app_db');
$mysqli->set_charset('utf8mb4');

// 关闭自动提交 — 开启事务
$mysqli->autocommit(false);

try {
    // 转账操作
    $mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

    // 检查是否有错误
    if ($mysqli->error) {
        throw new RuntimeException($mysqli->error);
    }

    // 提交事务
    $mysqli->commit();
    echo "转账成功\n";

} catch (Exception $e) {
    // 回滚事务
    $mysqli->rollback();
    echo "转账失败: " . $e->getMessage() . "\n";
} finally {
    // 恢复自动提交
    $mysqli->autocommit(true);
}

begin_transaction 方式 ​

php
<?php
declare(strict_types=1);

$mysqli = new mysqli('localhost', 'user', 'pass', 'app_db');

// begin_transaction() — 更明确的事务控制
$mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);

try {
    $mysqli->query("UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0");

    if ($mysqli->affected_rows === 0) {
        throw new RuntimeException('库存不足');
    }

    $mysqli->query("INSERT INTO order_items (product_id, quantity) VALUES (1, 1)");

    $mysqli->commit();
    echo "订单创建成功\n";

} catch (Exception $e) {
    $mysqli->rollback();
    echo "失败: " . $e->getMessage() . "\n";
}

保存点（Savepoint） ​

php
<?php
declare(strict_types=1);

$mysqli = new mysqli('localhost', 'user', 'pass', 'app_db');
$mysqli->autocommit(false);

try {
    $mysqli->query("INSERT INTO orders (user_id, total) VALUES (1, 500)");
    $mysqli->query("UPDATE accounts SET balance = balance - 500 WHERE id = 1");

    // 创建保存点
    $mysqli->savepoint('order_created');

    try {
        $mysqli->query("INSERT INTO shipments (order_id) VALUES (LAST_INSERT_ID())");
        $mysqli->query("INSERT INTO notifications (message) VALUES ('已发货')");
    } catch (Exception $e) {
        // 部分回滚到保存点
        $mysqli->rollback('order_created');
        echo "发货步骤失败，但订单已创建\n";
    }

    // 释放保存点
    $mysqli->release_savepoint('order_created');

    $mysqli->commit();
} catch (Exception $e) {
    $mysqli->rollback();
    echo "完全回滚: " . $e->getMessage() . "\n";
}

事务隔离级别 ​

php
<?php
declare(strict_types=1);

$mysqli = new mysqli('localhost', 'user', 'pass', 'app_db');

// 查看当前隔离级别
$result = $mysqli->query("SELECT @@transaction_isolation");
echo "隔离级别: " . $result->fetch_row()[0] . "\n";

// 设置隔离级别
// READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
$mysqli->query("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");
$mysqli->begin_transaction();

try {
    // 在 READ COMMITTED 级别下，每次 SELECT 都能读到其他事务已提交的数据
    $result = $mysqli->query("SELECT balance FROM accounts WHERE id = 1");
    $balance = $result->fetch_row()[0];

    $mysqli->commit();
} catch (Exception $e) {
    $mysqli->rollback();
}

// 恢复默认
$mysqli->query("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ");

隔离级别选择

  • 大多数 Web 应用使用默认的 REPEATABLE READ
  • 需要更高一致性时使用 SERIALIZABLE（性能下降）
  • 避免使用 READ UNCOMMITTED（几乎无隔离保护）

自动提交模式 ​

php
<?php
declare(strict_types=1);

$mysqli = new mysqli('localhost', 'user', 'pass', 'app_db');

// 查看自动提交状态
$result = $mysqli->query("SELECT @@autocommit");
echo "自动提交: " . $result->fetch_row()[0] . "\n";

// 方式1: autocommit(false)
$mysqli->autocommit(false);
// 之后每条 SQL 不自动提交，需要手动 commit/rollback
$mysqli->query("INSERT INTO logs (message) VALUES ('test')");
$mysqli->commit();
$mysqli->autocommit(true);

// 方式2: START TRANSACTION（推荐）
$mysqli->query("START TRANSACTION");
$mysqli->query("INSERT INTO logs (message) VALUES ('test2')");
$mysqli->query("COMMIT");

// 方式3: begin_transaction（PHP 5.5+）
$mysqli->begin_transaction();
$mysqli->query("INSERT INTO logs (message) VALUES ('test3')");
$mysqli->commit();

实战示例 ​

事务管理类 ​

php
<?php
declare(strict_types=1);

class MysqliTransactionManager
{
    private mysqli $mysqli;
    private int $level = 0;

    public function __construct(mysqli $mysqli)
    {
        $this->mysqli = $mysqli;
    }

    public function begin(): void
    {
        if ($this->level === 0) {
            $this->mysqli->begin_transaction();
        } else {
            $savepointName = 'sp_' . $this->level;
            $this->mysqli->savepoint($savepointName);
        }
        $this->level++;
    }

    public function commit(): void
    {
        $this->level--;
        if ($this->level === 0) {
            $this->mysqli->commit();
        } else {
            $savepointName = 'sp_' . $this->level;
            $this->mysqli->release_savepoint($savepointName);
        }
    }

    public function rollback(): void
    {
        $this->level--;
        if ($this->level === 0) {
            $this->mysqli->rollback();
        } else {
            $savepointName = 'sp_' . $this->level;
            $this->mysqli->rollback($savepointName);
        }
    }

    /**
     * 在事务中执行闭包
     */
    public function transaction(callable $callback): mixed
    {
        $this->begin();
        try {
            $result = $callback($this->mysqli);
            $this->commit();
            return $result;
        } catch (Exception $e) {
            $this->rollback();
            throw $e;
        }
    }
}

// 使用示例
$txManager = new MysqliTransactionManager($mysqli);

$txManager->transaction(function (mysqli $mysqli) {
    $mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
});

电商事务示例 ​

php
<?php
declare(strict_types=1);

class OrderTransaction
{
    private mysqli $mysqli;

    public function __construct(mysqli $mysqli)
    {
        $this->mysqli = $mysqli;
        $this->mysqli->set_charset('utf8mb4');
    }

    /**
     * 创建订单 — 原子操作
     */
    public function createOrder(int $userId, array $items): int
    {
        $this->mysqli->autocommit(false);

        try {
            $totalAmount = 0;

            // 1. 检查并扣减库存
            foreach ($items as $item) {
                $this->mysqli->query("
                    UPDATE products
                    SET stock = stock - {$item['quantity']}
                    WHERE id = {$item['product_id']} AND stock >= {$item['quantity']}
                ");

                if ($this->mysqli->affected_rows === 0) {
                    throw new RuntimeException("产品 {$item['product_id']} 库存不足");
                }

                $priceResult = $this->mysqli->query(
                    "SELECT price FROM products WHERE id = {$item['product_id']}"
                );
                $price = $priceResult->fetch_row()[0];
                $totalAmount += $price * $item['quantity'];
            }

            // 2. 扣减用户余额
            $this->mysqli->query("
                UPDATE accounts SET balance = balance - {$totalAmount}
                WHERE user_id = {$userId} AND balance >= {$totalAmount}
            ");

            if ($this->mysqli->affected_rows === 0) {
                throw new RuntimeException("用户余额不足");
            }

            // 3. 创建订单
            $this->mysqli->query("
                INSERT INTO orders (user_id, total_amount, status)
                VALUES ({$userId}, {$totalAmount}, 'pending')
            ");
            $orderId = $this->mysqli->insert_id;

            // 4. 创建订单明细
            foreach ($items as $item) {
                $this->mysqli->query("
                    INSERT INTO order_items (order_id, product_id, quantity)
                    VALUES ({$orderId}, {$item['product_id']}, {$item['quantity']})
                ");
            }

            $this->mysqli->commit();
            return $orderId;

        } catch (Exception $e) {
            $this->mysqli->rollback();
            throw $e;
        } finally {
            $this->mysqli->autocommit(true);
        }
    }
}

注意事项 ​

死锁处理 ​

php
<?php
// MySQL 在检测到死锁时自动回滚较小的事务
// 错误码: 1213 (ER_LOCK_DEADLOCK)
// 错误码: 1205 (ER_LOCK_WAIT_TIMEOUT)

$maxRetries = 3;
$retry = 0;

while ($retry < $maxRetries) {
    $mysqli->begin_transaction();
    try {
        $mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
        $mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
        $mysqli->commit();
        break; // 成功，退出循环
    } catch (Exception $e) {
        $mysqli->rollback();
        if (str_contains($e->getMessage(), '1213') || str_contains($e->getMessage(), '1205')) {
            $retry++;
            usleep(100000 * $retry); // 递增等待
            continue;
        }
        throw $e;
    }
}

if ($retry >= $maxRetries) {
    echo "事务重试次数耗尽\n";
}

死锁预防

  1. 按固定顺序访问表和行
  2. 保持事务简短
  3. 使用合适的隔离级别
  4. 设置合理的锁等待超时: innodb_lock_wait_timeout = 50

事务与锁 ​

php
<?php
// SELECT ... FOR UPDATE — 悲观锁
$mysqli->begin_transaction();
$result = $mysqli->query("SELECT * FROM products WHERE id = 1 FOR UPDATE");
$row = $result->fetch_assoc();
// 此时该行被锁定，其他事务不能修改或获取 FOR UPDATE 锁

// SELECT ... LOCK IN SHARE MODE — 共享锁
$result = $mysqli->query("SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE");
// 其他事务可以读，但不能写

// 乐观锁 — 使用版本号
$mysqli->query("
    UPDATE products SET stock = stock - 1, version = version + 1
    WHERE id = 1 AND version = {$currentVersion}
");
if ($mysqli->affected_rows === 0) {
    echo "数据已被其他事务修改\n";
}

最佳实践 ​

1. 事务配置建议 ​

sql
-- my.cnf 事务配置
[mysqld]
innodb_flush_log_at_trx_commit = 1  -- 每次提交都写日志（最安全）
innodb_lock_wait_timeout = 50       -- 锁等待超时 50秒
transaction_isolation = REPEATABLE_READ -- 默认隔离级别
innodb_buffer_pool_size = 4G        -- 缓冲池大小

2. 事务使用原则 ​

php
<?php
// 原则1: 事务尽可能短 — 减少锁定时间
// 原则2: 始终使用异常处理包裹事务
// 原则3: 在 finally 中恢复 autocommit
// 原则4: 按固定顺序访问资源（避免死锁）
// 原则5: 使用 begin_transaction() 而非 autocommit(false)
// 原则6: 考虑使用乐观锁减少锁冲突

参考链接 ​