configuring-transaction-isolation

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Transaction Isolation Levels

事务隔离级别

This skill teaches how to configure transaction isolation levels in Prisma to prevent race conditions and handle concurrent database access correctly.

<role> This skill teaches Claude how to configure and use transaction isolation levels in Prisma 6 to prevent concurrency issues like race conditions, dirty reads, phantom reads, and lost updates. </role> <when-to-activate> This skill activates when:

User mentions race conditions, concurrent updates, or dirty reads
Working with financial transactions, inventory systems, or booking platforms
Implementing operations that must maintain consistency under concurrent access
User asks about Serializable, RepeatableRead, or ReadCommitted isolation
Dealing with P2034 errors (transaction conflicts) </when-to-activate>

<overview> Transaction isolation levels control how database transactions interact with each other when running concurrently. Prisma supports setting isolation levels to prevent common concurrency issues.

Key Isolation Levels:

Serializable - Strictest isolation, prevents all anomalies
RepeatableRead - Prevents dirty and non-repeatable reads
ReadCommitted - Prevents dirty reads only (default for most databases)
ReadUncommitted - No isolation (not recommended)

Common Concurrency Issues:

Dirty Reads: Reading uncommitted changes from other transactions
Non-Repeatable Reads: Same query returns different results within transaction
Phantom Reads: New rows appear in repeated queries
Lost Updates: Concurrent updates overwrite each other

When to Set Isolation:

Financial operations (payments, transfers, refunds)
Inventory management (stock reservations, order fulfillment)
Booking systems (seat reservations, room bookings)
Any operation requiring strict consistency </overview>

本技能将讲解如何在Prisma中配置事务隔离级别，以防止竞态条件并正确处理数据库并发访问。

<role> 本技能将指导Claude如何在Prisma 6中配置和使用事务隔离级别，以防止竞态条件、脏读、幻读和更新丢失等并发问题。 </role> <when-to-activate> 在以下场景激活本技能：

用户提及竞态条件、并发更新或脏读
处理财务交易、库存系统或预订平台相关操作
实现需要在并发访问下保持一致性的操作
用户询问Serializable、RepeatableRead或ReadCommitted隔离级别相关问题
处理P2034错误（事务冲突）

</when-to-activate> <overview> 事务隔离级别用于控制数据库事务在并发执行时的相互作用方式。Prisma支持设置隔离级别以防止常见的并发问题。

核心隔离级别：

Serializable - 最严格的隔离级别，可防止所有异常
RepeatableRead - 防止脏读和不可重复读
ReadCommitted - 仅防止脏读（大多数数据库的默认级别）
ReadUncommitted - 无隔离（不推荐使用）

常见并发问题：

脏读： 读取其他事务未提交的更改
不可重复读： 同一事务内重复执行同一查询返回不同结果
幻读： 重复查询时出现新的行
更新丢失： 并发更新相互覆盖

何时设置隔离级别：

财务操作（支付、转账、退款）
库存管理（库存预留、订单履约）
预订系统（座位预订、房间预订）
任何需要严格一致性的操作

</overview> <workflow>

Standard Workflow

标准工作流程

Phase 1: Identify Concurrency Risk

Analyze operation for concurrent access patterns
Determine what consistency guarantees are needed
Choose appropriate isolation level based on requirements

Phase 2: Configure Isolation Level

Set isolation level in transaction options
Implement proper error handling for conflicts
Add retry logic if appropriate

Phase 3: Handle Isolation Conflicts

Catch P2034 errors (transaction conflicts)
Retry with exponential backoff if appropriate
Return clear error messages to users </workflow>

<isolation-level-guide>

阶段1：识别并发风险

分析操作的并发访问模式
确定所需的一致性保障
根据需求选择合适的隔离级别

阶段2：配置隔离级别

在事务选项中设置隔离级别
为冲突实现适当的错误处理
酌情添加重试逻辑

阶段3：处理隔离冲突

捕获P2034错误（事务冲突）
酌情使用指数退避策略进行重试
向用户返回清晰的错误信息

</workflow> <isolation-level-guide>

Isolation Level Quick Reference

隔离级别速查指南

Level	Prevents	Use Cases	Trade-offs
Serializable	All anomalies	Financial transactions, critical inventory	Highest consistency, lowest concurrency, more P2034 errors
RepeatableRead	Dirty reads, non-repeatable reads	Reports, multi-step reads	Good balance, still allows phantom reads
ReadCommitted	Dirty reads only	Standard operations, high-concurrency	Highest concurrency, allows non-repeatable/phantom reads
ReadUncommitted	Nothing	Not recommended	Almost never appropriate

级别	防止的问题	适用场景	权衡
Serializable	所有异常	财务交易、关键库存管理	一致性最高，并发度最低，P2034错误更多
RepeatableRead	脏读、不可重复读	报表、多步骤读取操作	一致性与并发度平衡较好，仍允许幻读
ReadCommitted	仅脏读	标准操作、高并发场景	并发度最高，允许不可重复读/幻读
ReadUncommitted	无	不推荐使用	几乎不适用于任何场景

Serializable Example

Serializable 示例

typescript

await prisma.$transaction(
  async (tx) => {
    const account = await tx.account.findUnique({
      where: { id: accountId }
    });

    if (account.balance < amount) {
      throw new Error('Insufficient funds');
    }

    await tx.account.update({
      where: { id: accountId },
      data: { balance: { decrement: amount } }
    });

    await tx.transaction.create({
      data: {
        accountId,
        amount: -amount,
        type: 'WITHDRAWAL'
      }
    });
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable
  }
);

typescript

await prisma.$transaction(
  async (tx) => {
    const account = await tx.account.findUnique({
      where: { id: accountId }
    });

    if (account.balance < amount) {
      throw new Error('Insufficient funds');
    }

    await tx.account.update({
      where: { id: accountId },
      data: { balance: { decrement: amount } }
    });

    await tx.transaction.create({
      data: {
        accountId,
        amount: -amount,
        type: 'WITHDRAWAL'
      }
    });
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable
  }
);

RepeatableRead Example

RepeatableRead 示例

typescript

await prisma.$transaction(
  async (tx) => {
    const user = await tx.user.findUnique({
      where: { id: userId },
      include: { orders: true }
    });

    const totalSpent = user.orders.reduce(
      (sum, order) => sum + order.amount,
      0
    );

    await tx.user.update({
      where: { id: userId },
      data: {
        tierLevel: calculateTier(totalSpent),
        lastCalculatedAt: new Date()
      }
    });
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.RepeatableRead
  }
);

typescript

await prisma.$transaction(
  async (tx) => {
    const user = await tx.user.findUnique({
      where: { id: userId },
      include: { orders: true }
    });

    const totalSpent = user.orders.reduce(
      (sum, order) => sum + order.amount,
      0
    );

    await tx.user.update({
      where: { id: userId },
      data: {
        tierLevel: calculateTier(totalSpent),
        lastCalculatedAt: new Date()
      }
    });
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.RepeatableRead
  }
);

ReadCommitted Example

ReadCommitted 示例

typescript

await prisma.$transaction(
  async (tx) => {
    await tx.log.create({
      data: {
        level: 'INFO',
        message: 'User logged in',
        userId
      }
    });

    await tx.user.update({
      where: { id: userId },
      data: { lastLoginAt: new Date() }
    });
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.ReadCommitted
  }
);

</isolation-level-guide> <decision-tree>

typescript

await prisma.$transaction(
  async (tx) => {
    await tx.log.create({
      data: {
        level: 'INFO',
        message: 'User logged in',
        userId
      }
    });

    await tx.user.update({
      where: { id: userId },
      data: { lastLoginAt: new Date() }
    });
  },
  {
    isolationLevel: Prisma.TransactionIsolationLevel.ReadCommitted
  }
);

</isolation-level-guide> <decision-tree>

Choosing Isolation Level

选择隔离级别

Follow this decision tree:

Is this a financial operation (money, payments, credits)?

YES → Use
```
Serializable
```
NO → Continue

Does the operation read data multiple times and require it to stay constant?

YES → Use
```
RepeatableRead
```
NO → Continue

Is this a high-concurrency operation where conflicts are expensive?

YES → Use
```
ReadCommitted
```
(or no explicit isolation)
NO → Continue

Does the operation modify data based on a read within the transaction?

YES → Use
```
RepeatableRead
```
minimum
NO → Use
```
ReadCommitted
```
(or no explicit isolation)

Still unsure?

Start with
```
RepeatableRead
```
for safety
Monitor P2034 error rate
Adjust based on actual concurrency patterns </decision-tree>

<error-handling>

遵循以下决策树：

是否为财务操作（资金、支付、信贷）？

是 → 使用
```
Serializable
```
否 → 继续

操作是否需要多次读取数据并要求数据保持不变？

是 → 使用
```
RepeatableRead
```
否 → 继续

是否为高并发操作，且冲突处理成本较高？

是 → 使用
```
ReadCommitted
```
（或不设置显式隔离级别）
否 → 继续

操作是否基于事务内的读取结果修改数据？

是 → 至少使用
```
RepeatableRead
```
否 → 使用
```
ReadCommitted
```
（或不设置显式隔离级别）

仍不确定？

为安全起见，从
```
RepeatableRead
```
开始
监控P2034错误率
根据实际并发模式进行调整

</decision-tree> <error-handling>

Handling Isolation Conflicts

处理隔离冲突

P2034: Transaction Conflict

P2034：事务冲突

When using Serializable isolation, conflicts are common under concurrency:

typescript

async function transferWithRetry(
  fromId: string,
  toId: string,
  amount: number,
  maxRetries = 3
) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      await prisma.$transaction(
        async (tx) => {
          const fromAccount = await tx.account.findUnique({
            where: { id: fromId }
          });

          if (fromAccount.balance < amount) {
            throw new Error('Insufficient funds');
          }

          await tx.account.update({
            where: { id: fromId },
            data: { balance: { decrement: amount } }
          });

          await tx.account.update({
            where: { id: toId },
            data: { balance: { increment: amount } }
          });
        },
        {
          isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
          maxWait: 5000,
          timeout: 10000
        }
      );

      return { success: true };

    } catch (error) {
      if (error.code === 'P2034' && attempt < maxRetries - 1) {
        await new Promise(resolve =>
          setTimeout(resolve, Math.pow(2, attempt) * 100)
        );
        continue;
      }

      throw error;
    }
  }

  throw new Error('Transaction failed after max retries');
}

Key Elements:

Retry loop with attempt counter
Check for P2034 error code
Exponential backoff between retries
maxWait and timeout configuration
Final error if all retries exhausted

使用Serializable隔离级别时，并发场景下冲突很常见：

typescript

async function transferWithRetry(
  fromId: string,
  toId: string,
  amount: number,
  maxRetries = 3
) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      await prisma.$transaction(
        async (tx) => {
          const fromAccount = await tx.account.findUnique({
            where: { id: fromId }
          });

          if (fromAccount.balance < amount) {
            throw new Error('Insufficient funds');
          }

          await tx.account.update({
            where: { id: fromId },
            data: { balance: { decrement: amount } }
          });

          await tx.account.update({
            where: { id: toId },
            data: { balance: { increment: amount } }
          });
        },
        {
          isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
          maxWait: 5000,
          timeout: 10000
        }
      );

      return { success: true };

    } catch (error) {
      if (error.code === 'P2034' && attempt < maxRetries - 1) {
        await new Promise(resolve =>
          setTimeout(resolve, Math.pow(2, attempt) * 100)
        );
        continue;
      }

      throw error;
    }
  }

  throw new Error('Transaction failed after max retries');
}

核心要素：

带尝试计数器的重试循环
检查P2034错误码
重试之间使用指数退避策略
配置maxWait和timeout
所有重试失败后返回最终错误

Timeout Configuration

超时配置

typescript

{
  isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  maxWait: 5000,
  timeout: 10000
}

```
maxWait
```
: Maximum time to wait for transaction to start (milliseconds)
```
timeout
```
: Maximum time for transaction to complete (milliseconds)

Higher isolation levels need higher timeouts to handle conflicts. </error-handling>

typescript

{
  isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  maxWait: 5000,
  timeout: 10000
}

```
maxWait
```
：等待事务启动的最长时间（毫秒）
```
timeout
```
：事务完成的最长时间（毫秒）

隔离级别越高，需要设置更长的超时时间来处理冲突。

</error-handling> <constraints>

Constraints and Guidelines

约束与指南

MUST:

Use Serializable for financial operations
Handle P2034 errors explicitly
Set appropriate maxWait and timeout values
Validate data before starting transaction
Use atomic operations (increment/decrement) when possible

SHOULD:

Implement retry logic with exponential backoff for Serializable
Keep transactions as short as possible
Read all data needed before making decisions
Log isolation conflicts for monitoring
Consider RepeatableRead before defaulting to Serializable

NEVER:

Use ReadUncommitted in production
Ignore P2034 errors
Retry indefinitely without limit
Mix isolation levels in same operation
Assume isolation level is higher than default without setting it </constraints>

必须：

财务操作使用Serializable级别
显式处理P2034错误
设置合适的maxWait和timeout值
启动事务前验证数据
尽可能使用原子操作（递增/递减）

应该：

为Serializable级别实现带指数退避的重试逻辑
尽可能缩短事务时长
做决策前读取所有需要的数据
记录隔离冲突以便监控
在默认使用Serializable前考虑RepeatableRead

禁止：

在生产环境中使用ReadUncommitted
忽略P2034错误
无限制地无限重试
在同一操作中混合使用不同隔离级别
未显式设置时假设隔离级别高于默认值

</constraints> <validation>

Validation

验证

After implementing isolation levels:

Concurrency Testing:
- Simulate concurrent requests to same resource
- Verify no lost updates or race conditions occur
- Expected: Conflicts detected and handled gracefully
Performance Monitoring:
- Monitor P2034 error rate
- Track transaction retry attempts
- If P2034 > 5%: Consider lowering isolation level or optimizing transaction duration
Error Handling:
- Verify P2034 errors return user-friendly messages
- Check retry logic executes correctly
- Ensure transactions eventually succeed or fail definitively </validation>

实现隔离级别后：

并发测试：
- 模拟对同一资源的并发请求
- 验证是否存在更新丢失或竞态条件
- 预期结果：冲突被检测到并得到妥善处理
性能监控：
- 监控P2034错误率
- 跟踪事务重试次数
- 如果P2034错误率>5%：考虑降低隔离级别或优化事务时长
错误处理验证：
- 验证P2034错误返回用户友好的提示信息
- 检查重试逻辑是否正确执行
- 确保事务最终成功或明确失败

</validation>

References

参考资料

For additional details and advanced scenarios, see:

Database-Specific Defaults - PostgreSQL, MySQL, SQLite, MongoDB isolation behaviors
Race Condition Patterns - Lost updates, double-booking, phantom reads
Complete Examples - Banking transfers, inventory reservations, seat bookings

如需了解更多细节和高级场景，请查看：

数据库特定默认值 - PostgreSQL、MySQL、SQLite、MongoDB的隔离行为
竞态条件模式 - 更新丢失、重复预订、幻读
完整示例 - 银行转账、库存预留、座位预订