Question: transactions and concurrency

[olalonde]

From the Wiki:

client.query('BEGIN', function(err, result) {
  if(err) return rollback(client);
  client.query('INSERT INTO account(money) VALUES(100) WHERE id = $1', [1], function(err, result) {
    if(err) return rollback(client);
    client.query('INSERT INTO account(money) VALUES(-100) WHERE id = $1', [2], function(err, result) {
      if(err) return rollback(client);
      //disconnect after successful commit
      client.query('COMMIT', client.end.bind(client));
    });
  });
});

In this example, I noticed that the callbacks refer to the same global client object. What if while a query is being executed another query is executed from somewhere else in the code? How does node-postgres know which queries belong to the same transaction?

For example:

client.query('BEGIN', function(err, result) {
  client.query('INSERT INTO account(money) VALUES(100) WHERE id = $1', [1], function(err, result) {
      // Let's say an error happens here.... will the other transaction below be rolled back as well?
      if(err) return rollback(client);
      client.query('COMMIT', client.end.bind(client));
});

client.query('BEGIN', function(err, result) {
  client.query('INSERT INTO account(money) VALUES(200) WHERE id = $1', [1], function(err, result) {
      if(err) return rollback(client);
      client.query('COMMIT', client.end.bind(client));
});

It seems I could maybe solve this problem by using different clients for each transactions?

[olalonde]

Ah ok, if I understand correctly I should use pg.connect(...) and call done() when I'm done with the client to prevent the problem explained above from happening.

[brianc]

olalonde - that example in the wiki isn't really meant to represent a global client. It's just a single instance of a client. You can create an instance manually vai the new pg.Client constructor. Commonly I do this in one off scripts, ETL, or when doing COPYIN/COPYOUT type of things where using a pooled client isn't really needed. You just need to be sure you feed queries into the client in the order you want them executed. If you're doing to different transactions you'll definitely want to use a different client for each transaction. A transaction is local to the individual client connection and that's how they're managed in the PostgreSQL server as well.