Read transactions in parallel

[brettz9]

The WebSQL transaction steps list:

Open a new SQL transaction to the database, and create a SQLTransaction object that represents that transaction. If the mode is read/write, the transaction must have an exclusive write lock over the entire database. If the mode is read-only, the transaction must have a shared read lock over the entire database. The user agent should wait for an appropriate lock to be available.

Since the read lock is "shared", it appears that read transactions can run in parallel with other read transactions--something which could be helpful to performance. (Note that this is different from your already existing readOnly check which avoids the commit step.)

[nolanlawson]

Currently I do a soft transaction -- i.e. doing all the reads at once outside of a transaction, and guaranteeing no writes using a queue.

Technically this is incorrect if there is more than one user of the system, but I also assumed only one user at a time. You are right that there should be a global read transaction while all reads are being made, but it only matters if there is more than one reader from more than one thread. I don't see this as a very high-priority task but it would hew closer to the spec, yes.

[brettz9]

The greater significance of the read lock, as I see it, is to ensure no writes occur during the read transaction, and that the "shared" nature of the lock means that subsequently initiated read transactions be allowed to overlap with this one--another read transaction could begin or even end while the first read transaction may yet be ongoing.

My concern is thus less with any independent processes that may be operating on the same database, than with the performance problem of all of the read transactions being done currently in series (with an "immediate" timeout in between each (read) transaction), potentially impacting performance within a single thread.

Take this code for example:

var openDatabase = require('websql');
var db = openDatabase(':memory:', '1.0', 'description', 1);
db.transaction(function (t) {
    t.executeSql('CREATE TABLE docids (id, name)', [], function (t) {
        for (var i = 0; i < 10; i++) {
            t.executeSql('INSERT INTO docids (id, name) VALUES (' + i + ', "' +
                String.fromCharCode(i + 97) + '")');
        }
    });
}, null, beginReads);

function beginReads () {
    for (var i = 0; i < 100; i++) {
        (function (i) {
            db.readTransaction(function (t) {
                t.executeSql('SELECT name FROM docids WHERE id = ' + i, [], function (data) {
                    console.log('Finished: ' + i);
                }, function (tx, err) {
                    console.log(err);
                });
            });
        }(i));
    }
}

The logging for "Finished:" will always show the count in order although this, as I understand the spec in reference to "shared" read locks, should not be necessary since I don't see any requirement that subsequent (read) transactions must wait for previous (read) transactions to complete.

(Providing the optimization should not allow write "starvation", however, whereby writes could be indefinitely prevented from occurring or occurring in a timely fashion if reads were always allowed to begin while another read was in process, and there were an ongoing supply of reads).)

[nolanlawson]

In the current implementation, write starvation is not a problem because
reads don't jump the queue. I see what you mean about the performance
issues and agree there should be one read transaction to bookend all the
reads.

As a side note, I am incredibly busy these days and am unlikely to have
time to submit a fix, but PRs would be welcome. :)

[nolanlawson]

Closing old issues, please reopen as necessary