[whatwg] Comments on updated SQL API

[Brady Eidson]

On Oct 16, 2007, at 11:18 PM, Ian Hickson wrote:

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

I've seen about 10 different interpretations of the implicit  
transaction and the effect of closeTransaction() - I don't think it's  
clear yet  ;)

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

The above case with sql1-3 and sql4-6 - what would happen?  Would the  
sql4-6 calls wait until the sql1-3's complete or would they error out?
The spec is ambiguous on this *because* of the implicit transaction.

Also, the "implicit transaction" from the JS API's standpoint turns  
into an "explicit transaction for every individual statement" in SQL  
reality.  This is almost certainly going to translate into a lock held  
for a longer period of time or more code execution for a single  
statement.  If a common use-case turns out to be individual statements  
one at a time, we're inflicting a mandatory penalty on the web  
developer.

Additionally, I sent an email a bit over a week about about the  
complexities of changeVersion() along with the implicit transactions.   
If there is one of these transactions open, how do we rectify  
changeVersion() blocking the JS thread when a callback on the JS  
thread would be needed to address closing out the mandatory implicit  
transaction?

Granted this is a detail that can be worked out independently of  
resolving the implicit transaction problem, but it is a complication I  
don't like.

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

An admirable goal - one that I agree with.  Which is why I think the  
wisdom of the implicit transaction is dubious.  Developers that will  
be using SQL will know they can say "BEGIN TRANSACTION;" and "COMMIT;"  
or "ROLLBACK;" so the utility of having transactions will not be  
lost.  Ditching it would help thin the API further, clearing up this  
confusion and complexity.

My $.02, anyway  ;)

~Brady