Atopos

1
SELECT * FROM information_schema.INNODB_TRX;

1
// ❌ 问题代码：使用默认的 REPEATABLE READ
2
await prisma.$transaction(async (tx) => {
3
  // 1. 查询转出账户
4
  const from = await tx.account.findUnique({
5
    where: { id: fromId }
6
  });
7

8
  // 2. 查询转入账户
9
  const to = await tx.account.findUnique({
10
    where: { id: toId }
11
  });
12

13
  // 3. 扣减转出账户
14
  await tx.account.update({
15
    where: { id: fromId },
16
    data: { balance: from.balance - amount }
17
  });
18

19
  // 4. 增加转入账户
20
  await tx.account.update({
21
    where: { id: toId },
22
    data: { balance: to.balance + amount }
23
  });
24

25
  // 5. 插入转账记录 ← 问题在这：RR 级别下插入会被间隙锁阻塞
26
  await tx.transferRecord.create({
27
    data: {
28
      fromId,
29
      toId,
30
      amount,
31
      createdAt: new Date()
32
    }
33
  });
34
});

1
┌─────────────────────────────────────────────────────────────────────────────────┐
2
│                    间隙锁导致插入排队                                            │
3
├─────────────────────────────────────────────────────────────────────────────────┤
4
│                                                                                 │
5
│  事务A 插入 id=101 → 持有间隙锁                                                │
6
│  事务B 插入 id=102 → 等待事务A 释放间隙锁                                      │
7
│  事务C 插入 id=103 → 等待事务B 获取锁                                          │
8
│  ...                                                                            │
9
│  事务N 插入 id=200 → 等待前面所有事务                                          │
10
│                                                                                 │
11
│  结果：所有插入操作串行化，并发能力归零                                         │
12
│                                                                                 │
13
└─────────────────────────────────────────────────────────────────────────────────┘

1
// ✅ 方案一：在事务中设置隔离级别
2
await prisma.$transaction(async (tx) => {
3
  // 先设置隔离级别
4
  await tx.$executeRaw`SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED`;
5

6
  // 然后执行业务逻辑
7
  const from = await tx.account.findUnique({ where: { id: fromId } });
8
  const to = await tx.account.findUnique({ where: { id: toId } });
9
  // ... 后续业务
10
});

1
Deadlock found when trying to get lock; try restarting transaction

1
SHOW ENGINE INNODB STATUS\G

1
事务 1: UPDATE account SET balance = balance - 100 WHERE id = 1
2
事务 2: UPDATE account SET balance = balance - 100 WHERE id = 2
3
事务 1: UPDATE account SET balance = balance + 100 WHERE id = 2  -- 等待
4
事务 2: UPDATE account SET balance = balance + 100 WHERE id = 1  -- 等待

1
// ✅ 先统一加锁顺序，再执行业务
2
await prisma.$transaction(async (tx) => {
3
  // 设置隔离级别
4
  await tx.$executeRaw`SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED`;
5

6
  // 关键：统一按 ID 升序加锁
7
  const firstId = Math.min(fromId, toId);
8
  const secondId = Math.max(fromId, toId);
9

10
  // 使用 FOR UPDATE 按顺序锁定账户
11
  const lockedAccounts = await tx.$queryRaw`
12
    SELECT * FROM account WHERE id IN (${firstId}, ${secondId}) ORDER BY id FOR UPDATE
13
  `;
14

15
  const first = lockedAccounts.find(a => a.id === firstId);
16
  const second = lockedAccounts.find(a => a.id === secondId);
17

18
  // 然后根据实际 from/to 执行转账
19
  if (fromId === firstId) {
20
    await tx.account.update({
21
      where: { id: firstId },
22
      data: { balance: first.balance - amount }
23
    });
24
    await tx.account.update({
25
      where: { id: secondId },
26
      data: { balance: second.balance + amount }
27
    });
28
  } else {
29
    await tx.account.update({
30
      where: { id: secondId },
31
      data: { balance: second.balance - amount }
32
    });
33
    await tx.account.update({
34
      where: { id: firstId },
35
      data: { balance: first.balance + amount }
36
    });
37
  }
38

39
  // 插入转账记录
40
  await tx.transferRecord.create({
41
    data: { fromId, toId, amount, createdAt: new Date() }
42
  });
43
});

1
// ✅ 在应用层实现重试机制
2
async function transferWithRetry(fromId: number, toId: number, amount: number) {
3
  const MAX_RETRIES = 3;
4

5
  for (let attempt = 1; attempt <= MAX_RETRIES; attempt++) {
6
    try {
7
      await prisma.$transaction(async (tx) => {
8
        // 转账业务逻辑
9
      });
10
      return; // 成功，退出
11
    } catch (error) {
12
      // 检查是否是死锁错误
13
      if (error.message?.includes('Deadlock') && attempt < MAX_RETRIES) {
14
        console.log(`死锁发生，第 ${attempt} 次重试...`);
15
        await sleep(100 * attempt); // 指数退避
16
        continue;
17
      }
18
      throw error;
19
    }
20
  }
21
}
22

23
function sleep(ms: number) {
24
  return new Promise(resolve => setTimeout(resolve, ms));
25
}

1
// ❌ 问题代码：所有账户在一个事务中处理
2
await prisma.$transaction(async (tx) => {
3
  const allAccounts = await tx.account.findMany();  // 10 万条
4

5
  for (const account of allAccounts) {
6
    const interest = account.balance * rate;
7
    await tx.account.update({
8
      where: { id: account.id },
9
      data: {
10
        balance: account.balance + interest
11
      }
12
    });
13
  }
14
});

1
// ✅ 分批处理：每批一个独立事务
2
async function monthlyInterest() {
3
  const BATCH_SIZE = 1000;
4
  let offset = 0;
5
  let totalProcessed = 0;
6

7
  while (true) {
8
    // 每批只查 1000 条
9
    const batch = await prisma.account.findMany({
10
      skip: offset,
11
      take: BATCH_SIZE
12
    });
13

14
    if (batch.length === 0) break;
15

16
    // 每批一个独立事务
17
    await processBatch(batch);
18

19
    totalProcessed += batch.length;
20
    offset += BATCH_SIZE;
21
    console.log(`已处理 ${totalProcessed} 条记录`);
22
  }
23
}
24

25
async function processBatch(batch: Account[]) {
26
  await prisma.$transaction(async (tx) => {
27
    for (const account of batch) {
28
      const interest = account.balance * 0.003;  // 月利率
29
      await tx.account.update({
30
        where: { id: account.id },
31
        data: { balance: account.balance + interest }
32
      });
33
    }
34
  });
35
}

1
// ❌ 这样读不加锁，可能读到未提交的数据（脏读）
2
const account = await tx.account.findUnique({
3
  where: { id: 1 }
4
});

1
// ✅ 使用原生 SQL 加锁
2
const accounts = await tx.$queryRaw`
3
  SELECT * FROM account WHERE id IN (${fromId}, ${toId}) FOR UPDATE
4
`;

1
// ❌ 错误：Prisma 不支持嵌套事务
2
await prisma.$transaction(async (tx1) => {
3
  await tx1.account.update(...);
4
  await prisma.$transaction(async (tx2) => {  // 报错！
5
    await tx2.account.update(...);
6
  });
7
});

1
// ✅ 正确：所有操作在同一个事务中完成
2
await prisma.$transaction(async (tx) => {
3
  await tx.account.update(...);
4
  await tx.account.update(...);
5
});

1
// ✅ 在事务中设置隔离级别
2
await prisma.$transaction(async (tx) => {
3
  await tx.$executeRaw`SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED`;
4
  // 业务逻辑
5
});