基于node-postgres：单连接能否并行处理两个事务？

Short answer: No, a single PostgreSQL client connection cannot handle two parallel transactions at the same time.

Let me break this down clearly, based on how PostgreSQL and node-postgres work under the hood:

• PostgreSQL connection state rules
  Every client connection to PostgreSQL maintains a single, sequential transaction state at any moment. A connection is either in auto-commit mode (the default, where each query runs as its own tiny transaction) or inside an active, unclosed transaction. You cannot start a new transaction while another is already active on the same connection—PostgreSQL will throw an explicit error if you try to run BEGIN again before ending the current transaction with COMMIT or ROLLBACK.

• Your scenario explained
  In your code, you start a transaction with BEGIN, build a promise chain (promiseChain1) for SQL steps, and plan to commit/rollback on completion. If you attempt to start a second transaction before the first one finishes (i.e., before COMMIT/ROLLBACK executes successfully), you’ll hit a database error like ERROR: current transaction is active; cannot begin another transaction.

  For example, this code will fail:

  const client = await pool.connect();
  try {
    // Start first transaction
    await client.query('BEGIN');
    const promiseChain1 = client.query('INSERT INTO users (name) VALUES ($1)', ['Alice'])
      .then(() => client.query('UPDATE stats SET user_count = user_count + 1'));
  
    // Try to start a second transaction in parallel—THIS WILL FAIL
    await client.query('BEGIN'); 
    await client.query('INSERT INTO logs (message) VALUES ($1)', ['Transaction 2 started']);
    await client.query('COMMIT');
  
    // Wait for first chain to finish
    await promiseChain1;
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
  

• How to run parallel transactions
  If you need parallel transactions, you must acquire separate clients from your connection pool. Each client maps to an independent PostgreSQL connection, so each can manage its own transaction without interfering with others.

  Here’s a working example:

  const pool = new Pool(/* your database config */);
  
  // Transaction 1 on its own client
  const runTransaction1 = async () => {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      await client.query('INSERT INTO users (name) VALUES ($1)', ['Alice']);
      await client.query('COMMIT');
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  };
  
  // Transaction 2 on a separate client
  const runTransaction2 = async () => {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      await client.query('INSERT INTO logs (message) VALUES ($1)', ['Transaction 2 completed']);
      await client.query('COMMIT');
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  };
  
  // Execute both transactions in parallel
  await Promise.all([runTransaction1(), runTransaction2()]);
  

To wrap up: A single node-postgres client is bound to one PostgreSQL connection, which can only handle one transaction at a time. Parallel transactions require separate client instances.

内容的提问来源于stack exchange，提问作者Ming Zhu