mysql如何在事务中处理异常

try-catch

ROLLBACK

COMMIT

Duplicate entry for key 'PRIMARY'

Cannot add or update a child row: a foreign key constraint fails

COMMIT

COMMIT

Deadlock found when trying to get lock; try restarting transaction

try-catch

try-catch

// 伪代码示例 (Java风格)
Connection conn = null;
try {
    conn = dataSource.getConnection();
    conn.setAutoCommit(false); // 禁用自动提交

    // 事务逻辑开始
    // 1. 执行第一个SQL操作
    PreparedStatement stmt1 = conn.prepareStatement("INSERT INTO users (name) VALUES (?)");
    stmt1.setString(1, "Alice");
    stmt1.executeUpdate();

    // 2. 执行第二个SQL操作，这里可能出现异常
    PreparedStatement stmt2 = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE user_id = ?");
    stmt2.setDouble(1, 100.00);
    stmt2.setInt(2, getUserIdByName("Bob")); // 假设Bob不存在或这里有其他错误
    int affectedRows = stmt2.executeUpdate();
    if (affectedRows == 0) {
        // 如果没有更新到任何行，可能意味着目标不存在，这也可以被视为业务异常
        throw new RuntimeException("Account not found or balance update failed.");
    }

    // ... 更多操作

    // 如果所有操作都成功，提交事务
    conn.commit();

} catch (SQLException e) {
    // 捕获数据库层面的异常
    System.err.println("Database error occurred: " + e.getMessage());
    if (conn != null) {
        try {
            conn.rollback(); // 发生异常时回滚
            System.out.println("Transaction rolled back due to SQLException.");
        } catch (SQLException rbEx) {
            System.err.println("Error during rollback: " + rbEx.getMessage());
        }
    }
    // 可以选择重新抛出异常，让上层调用者处理
    throw new RuntimeException("Transaction failed due to database error.", e);
} catch (RuntimeException e) {
    // 捕获业务逻辑层面的异常
    System.err.println("Business logic error occurred: " + e.getMessage());
    if (conn != null) {
        try {
            conn.rollback(); // 业务异常也回滚
            System.out.println("Transaction rolled back due to business logic error.");
        } catch (SQLException rbEx) {
            System.err.println("Error during rollback: " + rbEx.getMessage());
        }
    }
    throw e; // 重新抛出
} finally {
    // 确保连接被关闭，无论事务成功与否
    if (conn != null) {
        try {
            conn.setAutoCommit(true); // 恢复自动提交模式
            conn.close();
        } catch (SQLException closeEx) {
            System.err.println("Error closing connection: " + closeEx.getMessage());
        }
    }
}

SAVEPOINT

START TRANSACTION;

-- 处理第一条记录
INSERT INTO main_table (id, name) VALUES (1, 'Item A');
SAVEPOINT sp1; -- 设置保存点1

-- 尝试插入关联数据，这里可能失败
INSERT INTO sub_table (main_id, detail) VALUES (1, 'Detail for A');
-- 假设这里发生了错误，例如主键冲突或外键约束失败

-- 如果出错，回滚到sp1，只撤销Item A关联数据的更改
-- ROLLBACK TO SAVEPOINT sp1;
-- 然后可以尝试处理下一条记录，或者记录错误并继续

-- 处理第二条记录
INSERT INTO main_table (id, name) VALUES (2, 'Item B');
SAVEPOINT sp2; -- 设置保存点2
INSERT INTO sub_table (main_id, detail) VALUES (2, 'Detail for B');

-- 如果一切顺利，可以释放保存点（可选）
RELEASE SAVEPOINT sp1;
RELEASE SAVEPOINT sp2;

COMMIT;

SAVEPOINT

try-catch

ROLLBACK