WebSQL don't rollback

Question

Check this example:

var db = openDatabase( 'test.db', 1, '', 2*1024*1024 );

db.transaction(function(tx) { 
    var success = function(tx,results) {
        console.log( results );
    };
    var error = function(tx,results) {
        console.log( results );
    };
    // clean previous state
    tx.executeSql( 'DROP TABLE IF EXISTS products', [], success, error );
    tx.executeSql( 'CREATE TABLE products ( id INTEGER NULL PRIMARY KEY, name TEXT )', [], success, error );
    tx.executeSql( 'CREATE UNIQUE INDEX name ON products( name )', [], success, error );
    tx.executeSql( 'INSERT INTO products( name ) VALUES ( "Lechuga" )', [], success, error );
    tx.executeSql( 'INSERT INTO products( name ) VALUES ( "Naranja" )', [], success, error );
    tx.executeSql( 'INSERT INTO products( name ) VALUES ( "Naranja" )', [], success, error );
    tx.executeSql( 'INSERT INTO products( name ) VALUES ( "Tomate" )', [], getAll, error );
} );

function getAll(tx, results) {
    db.transaction(function(tx) {
        tx.executeSql( 'SELECT * FROM products', [], function(tx, results) {
            console.assert( results.rows.length === 0 ) // false, why?
        } );
    } );
}

jsfiddle link: http://jsfiddle.net/aBx7E/6/

The insert query that trigger the unique constraint, don't stop to keep websql continuing executing the next query. So at the end of the process you will have 4 rows, that is non sense, because if I am under transaction, when the constraint error is triggered, all the previous changes over the table are discarded.

Why Websql don't triger the rollback mechanism?

Answer 1

The error handler in WebSQL transactions need to return a non-false value for the transaction to be aborted. In this case, the error handler only calls console.log, which in essence allows the transaction to go forward.

If you place this as the error handler:

var error = function(tx,results) {
    console.log( results );
    return true; // added a non-false return value
};

Then the transaction should abort as expected when the uniqueness constraint is violated.

(I'm sure you've already figured this out by now, but I ran into the same issue, saw this question, didn't get an answer, and had to puzzle it out for myself.)

EDIT: Since I don't have comment privileges, this is outlined in http://www.w3.org/TR/webdatabase/#processing-model, Step 6. I haven't needed to manually rollback any queries or change my query statement so far to have the transaction properly aborted. Perhaps there are implementation differences, as I've been working with Web SQL on desktop Safari.