Node.js + SQLite asynchronous transactions

Question

I am using node-sqlite3 , but I am sure that this problem also appears in other database libraries. I found an error in my code with transactional mixing and asynchronous code.

function insertData(arrayWithData, callback) {
    // start a transaction
    db.run("BEGIN", function() {
        // do multiple inserts
        slide.asyncMap(
            arrayWithData,
            function(cb) {
                db.run("INSERT ...", cb);
            },
            function() {
                // all done
                db.run("COMMIT");
            }
        );
    });
}

// some other insert
setInterval(
    function() { db.run("INSERT ...", cb); },
    100
);

You can also run the full example .

The problem is that during async pause after beginor insertyou can run other code with the request insertor update. This additional request is then executed in the transaction. This is not a problem when making a transaction. But if the transaction is rolled back, the change made by this additional request also rolls back. Hoops we just unpredictably lost data without error messages.

, , -, , :

• .
• , , .
• .
• , , .

. ? ?

Answer 1

, SQLite. node-sqlite3.

IMHO , . SQLite, "", PostgreSQL, , , . , insertData. :

function insertData(callback, cmds) {
  // start a transaction
  db.serialize(function() {
    db.run("BEGIN;");
    //console.log('insertData -> begin');
    // do multiple inserts
    cmds.forEach(function(item) {
      db.run("INSERT INTO data (t) VALUES (?)", item, function(e) {
        if (e) {
          console.log('error');
          // rollback here
        } else {
          //console.log(item);
        }
      });
    });
    // all done
    //here should be commit
    //console.log('insertData -> commit');
    db.run("ROLLBACK;", function(e) {
      return callback();
    });
  });
}

:

init(function() {
  // insert with transaction
  function doTransactionInsert(e) {
    if (e) return console.log(e);
    setTimeout(insertData, 10, doTransactionInsert, ['all', 'your', 'base', 'are', 'belong', 'to', 'us']);
  }

  doTransactionInsert();

  // Insert increasing integers 0, 1, 2, ...
  var i=0;

  function doIntegerInsert() {
    //console.log('integer insert');
    insertData(function(e) {
      if (e) return console.log(e);
      setTimeout(doIntegerInsert, 9);
    }, [i++]);
  }

  ...

:

• cmds, , (cmds - , SQL)
• db.exec db.run( )
• db.serialize
• ommited callback BEGIN
• slide underscore

.

Answer 2

sqlite3, . , .

https://github.com/Strix-CZ/sqlite3-transactions

Answer 3

IMHO ivoszz:

• db.run , , , . db.run( "ROLLBACK" ) forEach. db.serialize , " ".
• "COMMIT/ROLLBACK" forEach , .

IMHO (obv ): . , db.serialize ( ), db.run, ( ). node -sqlite3, , , , ,