current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 5 down vote favorite
2

We have an existing SQL database, and I'm writing a node.js server that accesses it using straight SQL, using this postgres driver module:

https://github.com/brianc/node-postgres

So far I can't find a transaction management node module that works with postgres. Does anyone know of one? Preferably with some real-world use?

Second, at a higher level, we're evaluating whether node.js can actually replace Java as a real-world solution for a server potentially handling volume. Transaction management was one of the issues we'd have to solve. So some insight into that would also be useful.

For the moment, I'm simply issuing a sql BEGIN at the start of a node server request and a ROLLBACK or COMMIT at the end. However, I'm (perhaps obviously) unfamiliar with the real-world issues surrounding SQL transaction management. If someone could briefly explain the issues that the transaction management frameworks solve, I'd find it useful.

EDIT: I'm using the built-in connection pooling mechanism of the postgres driver, and all queries within an http request are issued on the same connection obtained from the pool. First the BEGIN is issued, then whatever the specific http request does, then the COMMIT or ROLLBACK.

Thanks.

share|improve this question
up vote 3 down vote accepted

Transaction management is a pretty large subject. For what I imagine you are doing, you will want to use AUTOCOMMIT mode. This basically means that you will rely on PostgreSQL to BEGIN/COMMIT all of your statements (or in other words, that all your statements will run in their own transaction with no relation to each other). An easy way to decide that AUTOCOMMIT mode is right for you is to decide you don't need to use ROLLBACK. A huge benefit of AUTOCOMMIT mode is that even the stupidest connection pooling tool can't screw up.

For the gritty details around transaction management, start by taking a look at http://www.postgresql.org/docs/9.1/static/transaction-iso.html What ever you do, make sure you don't use or write a naive framework that leaves you in "IDLE in transaction" land. And finally, since you mentioned "high volume", I should ask what your balance of reads to writes is. If it is strongly favoring read behavior, then you should consider writing your code to use memcached. The easiest (but far from the most effective) way to do this is to use PQC.

share|improve this answer
    
Thanks for all the info. We do need ROLLBACK, because our requests issue a number of update statements that need to be an atomic unit. I will look at that document and PQC. – Jake Apr 18 '12 at 15:35
up vote 2 down vote

pg-promise library takes care of the transaction management nicely:

db.tx(function () {
        return this.batch([
            this.query("update users set active = $1 where id = $2", [true, 123]),
            this.query("insert into audit(event, id) values($1, $2)", ['activate', 123])
        ]);
    })
    .then(function (data) {
        // success;
    })
    .catch(function (error) {
        // error;
    });
share|improve this answer
    
do you know how to set the transaction level using this method? – Derek Dec 9 '15 at 4:25
    
@Derek See Configurable Transactions – vitaly-t Dec 9 '15 at 9:02
    
Thanks for the amazing library vitaly-t!!! – Derek Dec 10 '15 at 5:40
up vote 0 down vote

Look for Sequelize http://docs.sequelizejs.com/en/latest/api/transaction/

The possible isolations levels to use when starting a transaction:

{
  READ_UNCOMMITTED: "READ UNCOMMITTED",
  READ_COMMITTED: "READ COMMITTED",
  REPEATABLE_READ: "REPEATABLE READ",
  SERIALIZABLE: "SERIALIZABLE"
}

Pass in the desired level as the first argument:

return sequelize.transaction({
  isolationLevel: Sequelize.Transaction.SERIALIZABLE
}, function (t) {

 // your transactions

}).then(function(result) {
  // transaction has been committed. Do something after the commit if required.
}).catch(function(err) {
  // do something with the err.
});
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.