[whatwg] Comments on updated SQL API

[Scott Hess]

On 9/24/07, Ian Hickson <ian at hixie.ch> wrote:
> On Sat, 22 Sep 2007, Timothy Hatcher wrote:
> > The callback syntax is nice but the implicit thread-global transaction
> > is confusing and can lead to programmer error and unneeded database
> > locking.
>
> There isn't really a thread-global transaction, the transactions are
> per-sequence-of-executeSql-calls, as in:
>
>    executeSql('sql1', function (result) {
>      executeSql('sql2', function (result) {
>        executeSql('sql3', function (result) {
>          // this is all in one transaction
>        });
>      });
>    });
>    executeSql('sql4', function (result) {
>      executeSql('sql5', function (result) {
>        executeSql('sql6', function (result) {
>          // this is all in another transaction
>        });
>      });
>    });
>
> ...where the likely order of execution is sql1 and sql4 at the same time,
> then sql2 and sql5, then sql3 and sql6, assuming all the statements take
> the same amount of time.

At least for SQLite, the implicit transaction, as I understand it,
would result in sql1/2/3 executing in a transaction, and the
transaction for sql4/5/6 either waiting for the sql1/2/3 transaction
to complete, or failing due to locking errors.  I don't think they can
interleave if either of them opens a transaction, because SQLite locks
at the database level.

In general, I am of the opinion that you should either have no
transactions, or explicit transactions, but never implicit
transactions.  This is a little different from a case like mysql,
where you might make an explicit decision to do things without
transactions in the interest of scalability, with the associated need
to be very careful in how your system orders your execution.  At least
for SQLite, having a transaction for multiple statements is more
efficient than not, and scalability isn't a huge issue.  Even so, I
think that implicit transactions are making a decision on behalf of
the developer which the developer should be explicit about.

I wasn't clear from the spec, but I think it would be possible to do
the sequence-of-statements case like:

  executeSql('sql1', function (result) {
    closeTransaction();
    executeSql('sql2', function (result) {
      closeTransaction();
      executeSql('sql3', function (result) {
      });
    });
  });

If not, then I think this would be a useful addition (if implicit
transactions are retained).

Explicit transactions could be as easy as:

  executeSql('sql1', function (result) {
    executeSqlInTransaction('sql2', function (result) {
      // Everything in here is in a transaction.
      executeSql('sql3', function (result) {
      });
    });
  });

-scott