CodePark/ questions / Transactions & ACID
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Transactions & ACID

What is wrong with this transfer using a node-postgres Pool?

await pool.query('BEGIN');
await pool.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
await pool.query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
await pool.query('COMMIT');
Each pool.query can run on a different connection, so the statements are not in one transaction; check out a client with pool.connect(), run everything on it and release it in finally

The statements do not run in one transaction. A transaction belongs to one database connection. pool.query borrows any free connection for each call. So BEGIN may open a transaction on one connection, while the updates run on other connections. On those connections, each update autocommits: it saves on its own, right away. The connection that ran BEGIN also goes back to the pool still inside an open transaction.

The fix: check out one client with pool.connect(). Run every statement on that client, and release it in finally. An ORM's transaction helper does this for you.

"Nothing; the pool keeps the transaction open across calls" is wrong, because the pool does not tie your calls to one connection.