transaction-management

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Transaction Management

数据库事务管理

Overview

概述

Implement robust transaction management with ACID compliance, concurrency control, and error handling. Covers isolation levels, locking strategies, and deadlock resolution.

实现符合ACID合规性、具备并发控制和错误处理能力的可靠事务管理。涵盖隔离级别、锁定策略和死锁解决方法。

When to Use

适用场景

ACID transaction implementation
Concurrent data modification handling
Isolation level selection
Deadlock prevention and resolution
Transaction timeout configuration
Distributed transaction coordination
Financial transaction safety

ACID事务实现
并发数据修改处理
隔离级别选择
死锁预防与解决
事务超时配置
分布式事务协调
金融交易安全保障

Transaction Basics

事务基础

PostgreSQL Transactions

PostgreSQL 事务

Simple Transaction:

sql

-- Start transaction
BEGIN;

-- Multiple statements
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit changes
COMMIT;

-- Or rollback
ROLLBACK;

Transaction with Error Handling:

sql

BEGIN;

-- Savepoint for partial rollback
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;

-- If error detected
IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
  ROLLBACK TO sp1;
  -- Handle negative balance
END IF;

COMMIT;

简单事务：

sql

-- 启动事务
BEGIN;

-- 多条语句
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交更改
COMMIT;

-- 或回滚
ROLLBACK;

带错误处理的事务：

sql

BEGIN;

-- 保存点用于部分回滚
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;

-- 若检测到错误
IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
  ROLLBACK TO sp1;
  -- 处理余额为负的情况
END IF;

COMMIT;

MySQL Transactions

MySQL 事务

MySQL Transaction:

sql

-- Start transaction
START TRANSACTION;

-- Or
BEGIN;

-- Statements
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit
COMMIT;

-- Or rollback
ROLLBACK;

MySQL Savepoints:

sql

START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (123, 99.99);
SAVEPOINT after_insert;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 456;

-- If inventory check fails
IF (SELECT quantity FROM inventory WHERE product_id = 456) < 0 THEN
  ROLLBACK TO after_insert;
END IF;

COMMIT;

MySQL事务：

sql

-- 启动事务
START TRANSACTION;

-- 或使用
BEGIN;

-- 执行语句
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交
COMMIT;

-- 或回滚
ROLLBACK;

MySQL保存点：

sql

START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (123, 99.99);
SAVEPOINT after_insert;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 456;

-- 若库存检查不通过
IF (SELECT quantity FROM inventory WHERE product_id = 456) < 0 THEN
  ROLLBACK TO after_insert;
END IF;

COMMIT;

Isolation Levels

隔离级别

PostgreSQL Isolation Levels

PostgreSQL 隔离级别

Read Uncommitted (not fully implemented):

sql

-- PostgreSQL treats as READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN;
-- Can read uncommitted changes from other transactions
SELECT COUNT(*) FROM orders WHERE user_id = 123;
COMMIT;

Read Committed (Default):

sql

-- Default PostgreSQL isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
-- Read committed data only
-- Allows phantom reads and non-repeatable reads
SELECT * FROM accounts WHERE id = 1;

-- May see different data if other transactions modify rows
SELECT * FROM accounts WHERE id = 1;
COMMIT;

Repeatable Read:

sql

-- Higher isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
-- Snapshot of data at transaction start
SELECT COUNT(*) as count_1 FROM orders;

-- Other transaction inserts order
-- Will still see same count
SELECT COUNT(*) as count_2 FROM orders;
COMMIT;

Serializable:

sql

-- Highest isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
-- Transactions execute as if serially
-- Prevents all anomalies (serialization failures may occur)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- May fail with serialization_failure error

读未提交（未完全实现）：

sql

-- PostgreSQL将其视为读已提交
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN;
-- 可以读取其他事务未提交的更改
SELECT COUNT(*) FROM orders WHERE user_id = 123;
COMMIT;

读已提交（默认）：

sql

-- PostgreSQL默认隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
-- 仅读取已提交的数据
-- 允许幻读和不可重复读
SELECT * FROM accounts WHERE id = 1;

-- 若其他事务修改行数据，可能会看到不同结果
SELECT * FROM accounts WHERE id = 1;
COMMIT;

可重复读：

sql

-- 更高的隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
-- 事务启动时的数据快照
SELECT COUNT(*) as count_1 FROM orders;

-- 其他事务插入订单
-- 仍会看到相同的计数
SELECT COUNT(*) as count_2 FROM orders;
COMMIT;

可串行化：

sql

-- 最高隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
-- 事务如同串行执行
-- 防止所有异常（可能出现序列化失败）
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 可能因serialization_failure错误失败

MySQL Isolation Levels

MySQL 隔离级别

MySQL Isolation Level Configuration:

sql

-- Check current isolation level
SHOW VARIABLES LIKE 'transaction_isolation';

-- Set for current session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set for all new connections
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set for specific transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- Statements
COMMIT;

Isolation Level Comparison:

sql

-- READ UNCOMMITTED (dirty reads possible)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- READ COMMITTED (repeatable reads, phantom reads possible)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ (phantom reads possible, MySQL default)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE (no anomalies)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MySQL隔离级别配置：

sql

-- 查看当前隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 为当前会话设置
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 为所有新连接设置
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 为特定事务设置
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- 执行语句
COMMIT;

隔离级别对比：

sql

-- READ UNCOMMITTED（可能出现脏读）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- READ COMMITTED（可重复读，可能出现幻读）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ（可能出现幻读，MySQL默认级别）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE（无异常）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Locking Strategies

锁定策略

PostgreSQL Explicit Locking

PostgreSQL 显式锁定

Row-Level Locks:

sql

-- FOR UPDATE: exclusive lock for update
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions cannot UPDATE/DELETE/SELECT FOR UPDATE this row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR SHARE: shared lock
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Other transactions can SELECT FOR SHARE but not FOR UPDATE
COMMIT;

-- FOR UPDATE NOWAIT: error if locked instead of waiting
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
EXCEPTION WHEN OTHERS THEN
  -- Row is locked
END;
COMMIT;

Table-Level Locks:

sql

-- Exclusive table lock
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- No other transactions can access table

-- Share lock
LOCK TABLE accounts IN SHARE MODE;
-- Other transactions can read but not write

-- Exclusive for user access
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;

行级锁：

sql

-- FOR UPDATE：用于更新的排他锁
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 其他事务无法对该行执行UPDATE/DELETE/SELECT FOR UPDATE操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR SHARE：共享锁
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- 其他事务可以执行SELECT FOR SHARE，但无法执行FOR UPDATE
COMMIT;

-- FOR UPDATE NOWAIT：若已锁定则直接报错而非等待
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
EXCEPTION WHEN OTHERS THEN
  -- 该行已被锁定
END;
COMMIT;

表级锁：

sql

-- 排他表锁
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- 其他事务无法访问该表

-- 共享锁
LOCK TABLE accounts IN SHARE MODE;
-- 其他事务可以读取但无法写入

-- 用户访问排他锁
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;

MySQL Locking

MySQL 锁定

Row-Level Locking:

sql

-- Implicit locking on UPDATE/DELETE
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Row is locked until transaction ends
COMMIT;

-- SELECT FOR UPDATE: explicit lock
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Exclusive lock acquired
UPDATE accounts SET balance = 100 WHERE id = 1;
COMMIT;

-- SELECT FOR SHARE: read lock
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Shared lock (blocks FOR UPDATE)
COMMIT;

Gap Locking (InnoDB):

sql

-- InnoDB locks gaps between rows
START TRANSACTION;
-- Locks rows and gaps where id between 1 and 100
SELECT * FROM products WHERE id BETWEEN 1 AND 100 FOR UPDATE;
-- Prevents phantom rows in range
COMMIT;

行级锁定：

sql

-- UPDATE/DELETE时的隐式锁定
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 该行会被锁定直到事务结束
COMMIT;

-- SELECT FOR UPDATE：显式锁定
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 获取排他锁
UPDATE accounts SET balance = 100 WHERE id = 1;
COMMIT;

-- SELECT FOR SHARE：读锁
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- 共享锁（会阻塞FOR UPDATE操作）
COMMIT;

间隙锁定（InnoDB）：

sql

-- InnoDB会锁定行之间的间隙
START TRANSACTION;
-- 锁定id在1到100之间的行和间隙
SELECT * FROM products WHERE id BETWEEN 1 AND 100 FOR UPDATE;
-- 防止范围内出现幻行
COMMIT;

Concurrency Control

并发控制

Optimistic Concurrency

乐观并发

PostgreSQL with Version Numbers:

sql

-- Add version column
ALTER TABLE accounts ADD COLUMN version INT DEFAULT 1;

-- Update with version check
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;

-- Application checks affected rows
-- If 0 rows updated, version mismatch (try again)

PostgreSQL with Timestamps:

sql

-- Add last modified timestamp
ALTER TABLE accounts ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();

-- Update with timestamp validation
UPDATE accounts
SET balance = balance - 100, updated_at = NOW()
WHERE id = 1 AND updated_at = '2024-01-15 10:00:00';

-- If no rows updated, data was modified by another transaction

基于版本号的PostgreSQL实现：

sql

-- 添加版本列
ALTER TABLE accounts ADD COLUMN version INT DEFAULT 1;

-- 带版本检查的更新
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;

-- 应用程序检查受影响的行数
-- 若更新行数为0，说明版本不匹配（需重试）

基于时间戳的PostgreSQL实现：

sql

-- 添加最后修改时间戳列
ALTER TABLE accounts ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();

-- 带时间戳验证的更新
UPDATE accounts
SET balance = balance - 100, updated_at = NOW()
WHERE id = 1 AND updated_at = '2024-01-15 10:00:00';

-- 若更新行数为0，说明数据已被其他事务修改

Pessimistic Concurrency

悲观并发

PostgreSQL - Lock and Modify:

sql

BEGIN;
-- Lock row before modification
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Safe to modify (no other transactions can update)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

PostgreSQL - 锁定后修改：

sql

BEGIN;
-- 修改前先锁定行
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 可以安全修改（其他事务无法更新该行）
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Deadlock Prevention

死锁预防

PostgreSQL - Deadlock Detection:

sql

-- PostgreSQL automatically detects deadlocks
-- Kills one transaction and raises error

-- Example deadlock scenario
-- Transaction 1: Lock A, then try Lock B
-- Transaction 2: Lock B, then try Lock A
-- Result: One transaction rolled back with deadlock error

-- Retry logic
DO $$
DECLARE
  retry_count INT := 0;
BEGIN
  LOOP
    BEGIN
      BEGIN;
      UPDATE accounts SET balance = balance - 100 WHERE id = 1;
      UPDATE accounts SET balance = balance + 100 WHERE id = 2;
      COMMIT;
      EXIT;
    EXCEPTION WHEN deadlocked_table THEN
      ROLLBACK;
      retry_count := retry_count + 1;
      IF retry_count > 3 THEN
        RAISE;
      END IF;
      -- Wait before retry
      PERFORM pg_sleep(0.1);
    END;
  END LOOP;
END $$;

MySQL - Deadlock Prevention:

sql

-- Prevent deadlock by consistent lock ordering
-- Always lock in same order: table1 id=1, then table2 id=2

START TRANSACTION;
-- Always lock account 1 first, then account 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- Safe order prevents deadlock
COMMIT;

Deadlock Recovery Handling:

javascript

// Application-level deadlock retry (Node.js)
async function transferMoney(fromId, toId, amount, retries = 3) {
  for (let i = 0; i < retries; i++) {
    try {
      await db.query('BEGIN');
      await db.query(
        'UPDATE accounts SET balance = balance - $1 WHERE id = $2 FOR UPDATE',
        [amount, fromId]
      );
      await db.query(
        'UPDATE accounts SET balance = balance + $1 WHERE id = $2 FOR UPDATE',
        [amount, toId]
      );
      await db.query('COMMIT');
      return { success: true };
    } catch (error) {
      if (error.code === '40P01') { // Deadlock detected
        await db.query('ROLLBACK');
        if (i === retries - 1) throw error;
        // Exponential backoff
        await new Promise(r => setTimeout(r, 100 * Math.pow(2, i)));
      } else {
        throw error;
      }
    }
  }
}

PostgreSQL - 死锁检测：

sql

-- PostgreSQL会自动检测死锁
-- 终止其中一个事务并抛出错误

-- 死锁场景示例
-- 事务1：先锁定A，再尝试锁定B
-- 事务2：先锁定B，再尝试锁定A
-- 结果：其中一个事务会因死锁错误回滚

-- 重试逻辑
DO $$
DECLARE
  retry_count INT := 0;
BEGIN
  LOOP
    BEGIN
      BEGIN;
      UPDATE accounts SET balance = balance - 100 WHERE id = 1;
      UPDATE accounts SET balance = balance + 100 WHERE id = 2;
      COMMIT;
      EXIT;
    EXCEPTION WHEN deadlocked_table THEN
      ROLLBACK;
      retry_count := retry_count + 1;
      IF retry_count > 3 THEN
        RAISE;
      END IF;
      -- 重试前等待
      PERFORM pg_sleep(0.1);
    END;
  END LOOP;
END $$;

MySQL - 死锁预防：

sql

-- 通过一致的锁定顺序预防死锁
-- 始终按相同顺序锁定：先table1 id=1，再table2 id=2

START TRANSACTION;
-- 始终先锁定账户1，再锁定账户2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
-- 安全的顺序可预防死锁
COMMIT;

死锁恢复处理：

javascript

// 应用层死锁重试（Node.js）
async function transferMoney(fromId, toId, amount, retries = 3) {
  for (let i = 0; i < retries; i++) {
    try {
      await db.query('BEGIN');
      await db.query(
        'UPDATE accounts SET balance = balance - $1 WHERE id = $2 FOR UPDATE',
        [amount, fromId]
      );
      await db.query(
        'UPDATE accounts SET balance = balance + $1 WHERE id = $2 FOR UPDATE',
        [amount, toId]
      );
      await db.query('COMMIT');
      return { success: true };
    } catch (error) {
      if (error.code === '40P01') { // 检测到死锁
        await db.query('ROLLBACK');
        if (i === retries - 1) throw error;
        // 指数退避
        await new Promise(r => setTimeout(r, 100 * Math.pow(2, i)));
      } else {
        throw error;
      }
    }
  }
}

Distributed Transactions

分布式事务

Two-Phase Commit Pattern:

sql

-- Prepare phase: acquire locks, validate
BEGIN;
SAVEPOINT prepare_phase;

-- Prepare writes on both databases
INSERT INTO account_shadow SELECT * FROM accounts WHERE id = 1;

-- Check if both databases are ready
-- If any fails, ROLLBACK TO prepare_phase

-- Commit phase: finalize
RELEASE SAVEPOINT prepare_phase;
COMMIT;

Eventual Consistency Pattern:

javascript

// Asynchronous transaction across services
async function transferAcrossServices(fromId, toId, amount) {
  // 1. Debit from first service (transactional)
  await service1.debit(fromId, amount);

  // 2. Queue credit for second service (reliable queue)
  await queue.publish({
    type: 'credit',
    toId,
    amount,
    requestId: uuid()
  });

  // 3. Service 2 processes asynchronously
  queue.subscribe('credit', async (msg) => {
    try {
      await service2.credit(msg.toId, msg.amount);
      await queue.ack(msg.requestId);
    } catch (error) {
      // Retry mechanism
      await queue.retry(msg.requestId);
    }
  });
}

两阶段提交模式：

sql

-- 准备阶段：获取锁，验证
BEGIN;
SAVEPOINT prepare_phase;

-- 在两个数据库上准备写入操作
INSERT INTO account_shadow SELECT * FROM accounts WHERE id = 1;

-- 检查两个数据库是否就绪
-- 若任意一个失败，执行ROLLBACK TO prepare_phase

-- 提交阶段：最终确认
RELEASE SAVEPOINT prepare_phase;
COMMIT;

最终一致性模式：

javascript

-- 跨服务的异步事务
async function transferAcrossServices(fromId, toId, amount) {
  // 1. 在第一个服务中扣款（事务性操作）
  await service1.debit(fromId, amount);

  // 2. 将第二个服务的入队操作加入可靠队列
  await queue.publish({
    type: 'credit',
    toId,
    amount,
    requestId: uuid()
  });

  // 3. 服务2异步处理该请求
  queue.subscribe('credit', async (msg) => {
    try {
      await service2.credit(msg.toId, msg.amount);
      await queue.ack(msg.requestId);
    } catch (error) {
      // 重试机制
      await queue.retry(msg.requestId);
    }
  });
}

Transaction Monitoring

事务监控

PostgreSQL - Active Transactions:

sql

-- View active transactions
SELECT
  pid,
  usename,
  application_name,
  state,
  query,
  query_start,
  xact_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

-- View transaction locks
SELECT
  l.locktype,
  l.relation::regclass,
  l.mode,
  l.granted,
  a.usename,
  a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

MySQL - Active Transactions:

sql

-- View active transactions
SELECT *
FROM INFORMATION_SCHEMA.INNODB_TRX
ORDER BY trx_started DESC;

-- View locks
SELECT *
FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- Kill long-running transaction
KILL QUERY process_id;
KILL CONNECTION process_id;

PostgreSQL - 活跃事务：

sql

-- 查看活跃事务
SELECT
  pid,
  usename,
  application_name,
  state,
  query,
  query_start,
  xact_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

-- 查看事务锁
SELECT
  l.locktype,
  l.relation::regclass,
  l.mode,
  l.granted,
  a.usename,
  a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

MySQL - 活跃事务：

sql

-- 查看活跃事务
SELECT *
FROM INFORMATION_SCHEMA.INNODB_TRX
ORDER BY trx_started DESC;

-- 查看锁
SELECT *
FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- 终止长时间运行的事务
KILL QUERY process_id;
KILL CONNECTION process_id;

Best Practices

最佳实践

✅ DO use appropriate isolation levels for use case ✅ DO keep transactions short ✅ DO commit frequently ✅ DO handle transaction errors ✅ DO use consistent lock ordering ✅ DO monitor transaction performance ✅ DO document transaction requirements

❌ DON'T hold transactions during user input ❌ DON'T use SERIALIZABLE for high-concurrency systems ❌ DON'T ignore deadlock errors ❌ DON'T lock too many rows ❌ DON'T use READ UNCOMMITTED for critical data

✅ 针对具体场景使用合适的隔离级别 ✅ 保持事务简短 ✅ 频繁提交事务 ✅ 处理事务错误 ✅ 使用一致的锁定顺序 ✅ 监控事务性能 ✅ 记录事务需求文档

❌ 不要在用户输入过程中持有事务 ❌ 高并发系统中不要使用SERIALIZABLE级别 ❌ 不要忽略死锁错误 ❌ 不要锁定过多行 ❌ 关键数据不要使用READ UNCOMMITTED级别

transaction-management

Original

Translation

Transaction Management

数据库事务管理

Overview

概述

When to Use

适用场景

Transaction Basics

事务基础

PostgreSQL Transactions

PostgreSQL 事务

MySQL Transactions

MySQL 事务

Isolation Levels

隔离级别

PostgreSQL Isolation Levels

PostgreSQL 隔离级别

MySQL Isolation Levels

MySQL 隔离级别

Locking Strategies

锁定策略

PostgreSQL Explicit Locking

PostgreSQL 显式锁定

MySQL Locking

MySQL 锁定

Concurrency Control

并发控制

Optimistic Concurrency

乐观并发

Pessimistic Concurrency

悲观并发

Deadlock Prevention

死锁预防

Distributed Transactions

分布式事务

Transaction Monitoring

事务监控

Best Practices

最佳实践

Resources

参考资源