CockroachDB行级锁无法锁定非冲突行，本地PostgreSQL锁失效求助

问题描述 #

我正尝试使用Node、React和PostgreSQL构建预订应用的锁功能，因此选用了CockroachDB，但发现CockroachDB似乎不允许对非冲突行授予行级锁。

使用**'pg'**库连接应用与数据库，API端点代码如下：

app.patch("/book", async (req, res) => {
  const slots = req.body;

  const queryString = slots.join(",");

 const query = `select * from bookings where id in ($1) FOR UPDATE`;

  const dbQuery = query.replace("$1", queryString);

  console.log(dbQuery);

  try {
    await client.query("BEGIN");

    const bookings = await client.query(dbQuery);

    console.log(bookings.rows);

    if (!bookings.rows) {
      console.log("TX rolled back");
      await client.query("ROLLBACK");
    }

    await new Promise((resolve) => setTimeout(resolve, 3000));

    await client.query("COMMIT");
    console.log("TX commited to the db");
    res.status(200).json({
      status: "success",
      message: "slots booked",
      data: bookings.rows,
    });
  } catch (error) {
    res.status(400).json({
      status: "failure",
      error: new Error(error).message,
    });
  }
});

当前问题：当用户A尝试锁定ID为1和2的行时可正常授予锁，但用户B尝试锁定ID为3（或其他非1、2的行）时，会报错：

error: there is already a transaction in progress

但根据行级锁的逻辑，这种错误不应出现，因为两者操作的是非冲突行。

同时在本地pgadmin数据库中测试锁功能时完全不起作用，想了解如何使用Node.js和pg库在本地数据库实现锁功能。

解决方案 #

1. 错误根源：客户端连接复用问题 #

你遇到的报错不是CockroachDB的行级锁机制问题，而是代码中客户端连接的使用方式错误。

如果你的client是全局复用的单例连接，那么当用户A发起请求开启事务后，用户B的请求会复用同一个连接，此时连接上已经有未完成的事务，自然会触发报错。这和行级锁本身无关，是连接管理的问题。

2. 代码修复方案 #

使用pg库的**客户端池（Pool）**来管理连接，每个请求获取独立的连接，避免事务交叉：

const { Pool } = require('pg');
const pool = new Pool({ /* 你的数据库配置，比如host、user、password、database等 */ });

app.patch("/book", async (req, res) => {
  const slots = req.body;
  let client;

  try {
    // 从池中获取独立连接
    client = await pool.connect();
    
    // 开启事务
    await client.query("BEGIN");

    // 使用参数化查询，自动处理IN子句参数，避免SQL注入
    const bookings = await client.query(
      `select * from bookings where id in ($1) FOR UPDATE`,
      [slots] // pg库支持直接传入数组作为IN参数
    );

    console.log(bookings.rows);

    if (bookings.rows.length === 0) {
      console.log("TX rolled back");
      await client.query("ROLLBACK");
      return res.status(404).json({
        status: "failure",
        message: "No slots found"
      });
    }

    // 模拟业务处理延迟
    await new Promise((resolve) => setTimeout(resolve, 3000));

    await client.query("COMMIT");
    console.log("TX commited to the db");
    res.status(200).json({
      status: "success",
      message: "slots booked",
      data: bookings.rows,
    });
  } catch (error) {
    // 出错时回滚事务（如果已开启）
    if (client) await client.query("ROLLBACK");
    res.status(400).json({
      status: "failure",
      error: error.message,
    });
  } finally {
    // 释放连接回池，避免资源泄漏
    if (client) client.release();
  }
});

3. 关键修复点说明 #

• 使用连接池：每个请求获取独立的客户端连接，事务在独立连接中执行，不会互相干扰。
• 参数化查询：直接将数组传入client.query的第二个参数，pg库会自动处理IN子句的参数绑定，避免手动拼接SQL导致的注入风险和语法错误。
• 完善事务回滚逻辑：在finally块释放连接，确保无论成功失败都不会占用连接；出错时必须回滚事务，避免连接处于异常状态。

4. 本地PostgreSQL锁功能测试验证 #

在本地PostgreSQL中测试锁功能，需要注意：

• 用独立连接测试不同用户的请求（比如两个psql终端、两个Postman请求）。
• 第一个连接执行SELECT * FROM bookings WHERE id = 1 FOR UPDATE;后，第二个连接执行SELECT * FROM bookings WHERE id = 3 FOR UPDATE;应该可以正常获取锁，不会阻塞；但如果尝试锁定id=1，则会被阻塞直到第一个事务提交/回滚。
• 可以通过SELECT * FROM pg_locks;查看当前锁的状态，验证行级锁是否生效。

内容的提问来源于stack exchange，提问作者nilay pophalkar