HTML 5 Web SQL Database Transaction commit or rollback when refreshing page

Question

As written in the Safari Client-Side Storage and Offline Applications Programming Guide, rolling back a HTML 5 Web SQL Database transaction is done by returning true in the callback function provided as an error callback to a transaction the executeSql method:

The per-query error-handling callback is rather straightforward. If the callback returns true, the entire transaction is rolled back. If the callback returns false, the transaction continues as if nothing had gone wrong. Thus, if you are executing a query that is optional—if a failure of that particular query should not cause the transaction to fail—you should pass in a callback that returns false. If a failure of the query should cause the entire transaction to fail, you should pass in a callback that returns true.

For example if I have the following transaction (suppose the 'users' table has a UNIQUE constraint on the 'username' field and username 'test' already exists - which I'm trying to insert again, which should result in a constraint error):

database.transaction(function(transaction) {
    transaction.executeSql(
        "INSERT INTO users (username) VALUES('test')",
        null,
        dataCallback,
        errorCallback
     );
});

function errorCallback() {
    return true; //this causes the rollback
}

I have two questions:

1. If I have to include many operations inside a transaction (for example I have to send some data using ajax to a server and wait for the response, etc.) and the user reloads the page before the response had arrived (which means that the errorCallback won't be called), will the transaction be commited or will it fail ?

2. Does anyone know how to rollback a Web SQL transaction manually ? For example if I want to rollback a transaction based on the result of an ajax call, how can it be done ? Should I run a query which contains an error to make sure that the error callback is called ?

Thanks.

Matchmark

Answer 1

1. The transaction will be committed.
2. Yes, to rollback explicitly, you must invoke invalid query explicitly. This is recommended workaround since the quick-and-dirty API is missing abort method.

Regarding AJAX, be ready all data, before you start a write transaction. You won't have any problem as you described. Use database constraint (UNIQUE, FOREIGNKEY) as much possible.

Rato

Answer 2

Have you found a way to do AJAX calls during the transaction? I haven't finished reading the whole spec, but so far it looks like once your SQLTransactionCallback or SQLTransactionSyncCallback returns, you can’t add any more stuff to the transaction — or can you? Maybe from the results callback?

Edit: Now that I look again, the spec (which contains many fewer errors than the Apple document you linked to, but is not as easy to read) says this:

1. If the method [executeSql] was not invoked during the execution of a SQLTransactionCallback, SQLStatementCallback, or SQLStatementErrorCallback then raise an INVALID_STATE_ERR exception.

So I think that means there's no way to do it.

Further edit: No, wait! As long as the SQLStatementCallback takes some time to get called, you could busy-wait doing select 3 + 4 over and over again, each time from the statement callback of the previous select 3 + 4, until your AJAX call sets a flag somewhere that has the data you want. This is terrible programming (it'll eat lots of CPU for no good reason, might block lower-priority tasks like redisplaying the page) but I think it's probably the only way to keep a transaction open for an arbitrary period of time. Too bad you can't select 3 + 4, sleep(1) in SQLite.

In general SQLite (the underlying storage engine here) rolls back uncompleted transactions. I haven’t yet tested the page-reload error case you're asking about. I would be very surprised if it was committed.

By the way, thank you very much for posting this question. I had been trying to figure out how to get the transaction to rollback, even though it is meticulously documented in the original spec.

Hickey

I tried saving the transaction object into a variable before the AJAX call and after it returns use it to execute another query. But I get a DOM exception stating that the transaction object cannot be used anymore. This only occurs after AJAX calls, with synchronous functions/callbacks I can pass it as parameter and run multiple queries in a single transaction. I haven't tried calling the AJAX synchronously (using jQuery for example), maybe that would maintain the transaction object in a valid state. – Matchmark

See my edit above about holding the transaction open. I don't think jQuery supports synchronous AJAX; it freezes the page and (outside of Chrome) the whole browser until complete. – Hickey

This is how you can do an AJAX or any timeout call during a transaction: linkedin.com/pulse/… – Franzen