Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Transaction API #3

Open
kkaefer opened this issue Feb 25, 2011 · 10 comments

Comments

@kkaefer
Copy link
Contributor

commented Feb 25, 2011

// Flat interface.
var trx = db.begin();
trx.on('commit', function() {});
trx.on('rollback', function() {});


trx.rollback();
trx.commit(); // No implicit commit when calling db.begin();

// Creates a new stack
db.transaction(function(trx) {
    trx == this
    // implicit begin

    // transaction objects are like database objects:

    trx.run("CREATE TABLE foo (id INT PRIMARY KEY, bar)");
    trx.run("INSERT INTO foo VALUES(?, ?)", 1, "first text");
    trx.run("INSERT INTO foo VALUES(?, ?)", 1, "second text"); // will fail
    trx.run("INSERT INTO foo VALUES(?, ?)", 1, "third text", function(err) {
        if (err) {
            // Do nothing and the error will be ignored

            // See below for more explanation
            trx.clear();
            trx.rollback();
            trx.commit();
        }
    });

    trx.on('rollback', function() {
        // This is emitted after the transaction was rolled back.
    });

    trx.on('commit', function() {
        // This is emitted after the transaction was committed successfully.
    });

    trx.on('error', function() {
        // This will be called when a statement execution doesn't have a callback
        // and no error handlers.

        // removes all further statements from the stack. You can now add more
        // statements to the stack. the automatic commit on empty stack persists.
        // Does not remove the error handler, you have to do that manually.
        trx.clear();

        // removes all further statements from the stack and does a rollback of the
        // entire transaction.
        trx.rollback();

        // removes all further statements from the stack and commits the transaction
        trx.commit();
    });

    // when no error handler is attached and an error bubbles up to the transaction's
    // error event, the transaction is rolled back and the rollback event is emitted.

    // implicit commit when the stack is empty.
});
@Pita

This comment has been minimized.

Copy link

commented Apr 30, 2011

Yes, that would be awesome +1

@kkaefer

This comment has been minimized.

Copy link
Contributor Author

commented May 1, 2011

@Pita: Please note that transactions are already possible with node-sqlite3, there's just no specific interface for it. Nothing stops you from running db.run("BEGIN"); /* some queries inside the transaction */ db.run("COMMIT"). You only have to pay attention that no other code holding the database handle injects queries inside the transaction that shouldn't be there.

@Pita

This comment has been minimized.

Copy link

commented May 1, 2011

I know that it's possible. I'm already doing it like this. But it's a ugly way cause I have to build a huge sql string manually.

var sql = "BEGIN TRANSACTION;\n";
  for(var i in bulk)
  {
    if(bulk[i].type == "set")
    {
      sql+="REPLACE INTO store VALUES (" + escape(bulk[i].key) + ", " + escape(bulk[i].value) + ");\n";
    }
    else if(bulk[i].type == "remove")
    {
      sql+="DELETE FROM store WHERE key = " + escape(bulk[i].key) + ";\n";
    }
  }
  sql += "END TRANSACTION;";

   this.db.exec(sql, function(err){ ...
@kkaefer

This comment has been minimized.

Copy link
Contributor Author

commented May 1, 2011

You don't have to build a single string; you can just use the regular APIs between db.run("BEGIN") and db.run("COMMIT")

@miccolis

This comment has been minimized.

Copy link
Member

commented Sep 15, 2011

I'd also really love to see this API implemented. I'm refactoring https://github.com/developmentseed/couch-sqlite to work with long lived connections and it would be great to use this, rather than a pool of one connection.

/cc @gundersen

@kkaefer

This comment has been minimized.

Copy link
Contributor Author

commented Sep 15, 2011

2 eggrolls.

@ghost

This comment has been minimized.

Copy link

commented Jul 17, 2012

You only have to pay attention that no other code holding the database handle injects queries inside the transaction that shouldn't be there.

What's the best way to do this? Is it sufficient to put the transaction inside db.serialize? Will that block other non-db things like http server events?

springmeyer pushed a commit that referenced this issue Jun 20, 2014

@JoshuaWise

This comment has been minimized.

Copy link

commented Feb 24, 2016

You only have to pay attention that no other code holding the database handle injects queries inside the transaction that shouldn't be there.

What's the best way to do this? Is it sufficient to put the transaction inside db.serialize? Will that block other non-db things like http server events?

I'd like an answer to ghost's question, please. It's not clear in the docs.

@koistya

This comment has been minimized.

Copy link

commented May 20, 2016

If this library is used with ES2015+ async/await (via sqlite), how can you make sure that some extraneous queries are not being injected into the transaction?

  await db.run('BEGIN');
  try {
    await db.exec(sql);
    await db.run('COMMIT');
  } catch (err) {
    await db.run('ROLLBACK');
    throw err;
  }
@bpasero

This comment has been minimized.

Copy link

commented Oct 5, 2018

I would like to know under which condition a ROLLBACK should be issues? It is not clear to me if this is done automatically by the engine. In other words, I would assume that upon any failure, the entire transaction is being rolled back to the state before. Otherwise, what would be the motivation to use a transaction in the first place?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
6 participants
You can’t perform that action at this time.