[whatwg] Comments on updated SQL API

[Ian Hickson]

On Fri, 5 Oct 2007, Scott Hess wrote:
> >
> > 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.

Indeed, that's also possible.


> 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.

What are the disadvantages of implicit transactions?

What do other people think?


> 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).

Yes, that's possible. Let me know if the spec is still unclear on this; I 
tried to clarify it. (I'll be adding examples in due course, by the way.)


> 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) {
>       });
>     });
>   });

That's a possibility, though we're trying to keep the API as thin as 
possible.

-- 
Ian Hickson               U+1047E                )\._.,--....,'``.    fL
http://ln.hixie.ch/       U+263A                /,   _.. \   _\  ;`._ ,.
Things that are impossible just take longer.   `._.-(,_..'--(,_..'`-.;.'