[whatwg] Comments on updated SQL API

[Adam Roben]

I think the conversation in this thread has gotten a little muddled.  
The two main issues I see being discussed are:

1. Should single SQL statements be wrapped in a transaction?
2. Should the SQL API support explicit transactions (and therefore a  
way to not fall into implicit transactions)?

It sounds like Brady is mostly concerned about (1), while Scott is  
mostly concerned about (2). I think it would be helpful to discuss  
these as separate issues.

-Adam

On Oct 17, 2007, at 11:24 AM, Brady Eidson wrote:

>
> On Oct 17, 2007, at 11:04 AM, Scott Hess wrote:
>
>> On 10/17/07, Brady Eidson <beidson at apple.com> wrote:
>>> Assuming using sqlite for the back end, I just wrote a quick little
>>> driver that creates a table with 10 columns, then inserts the exact
>>> same value into the table 20,000 times.
>>> I then ran the exact same test that does the exact same thing, but
>>> wraps each individual insert in a transaction.
>>>
>>> The transaction case is 5% slower.
>>
>> But in this case, if you inserted the values 1,000 per transaction,  
>> it
>> would probably be 10x faster.  Maybe 100x faster if you're dealing
>> with a network filesystem.
>
> I agree completely.  The debate is not whether transactions speed up  
> batch queries.  It's whether they slow down individual queries -  
> which I have evidence saying they do.
> My point is that if we can all end up agreeing it is a performance  
> hit, then it is an agreed upon mark against the *implicit*  
> transaction.
>
>> The performance case for not using implicit transactions for server
>> databases is that it can allow for more concurrency.  If the client
>> sends a statement to the server without an enclosing transaction, the
>> server can minimize the amount of time the transaction has the
>> database/table/row locked.  If the client has to open the  
>> transaction,
>> that means a minimum of two additional round trips back to the client
>> are introduced (and much worse, if either the client or server are
>> very busy).
>
> I'm also concerned about this - the same will be true with SQLite  
> (minimizing the amount of time a write lock is maintained on the  
> database file)
>
>> For an embedded database like SQLite, things are different.  In that
>> case, no matter what, you're going to pay a big cost for fsync.
>> Making the transaction explicit will have an impact, but I'm really
>> surprised that you're seeing 5%.  I would bet that you're doing BEGIN
>> rather than BEGIN IMMEDIATE, which means that your 5% is probably  
>> down
>> to upgrading your database locks.  If so, that can be worked around  
>> by
>> implementing the spec using BEGIN IMMEDIATE rather than BEGIN
>> DEFERRED.
>
> I will run more detailed numbers on this later, but a quick 1-off on  
> changing it to BEING IMMEDIATE still indicates a measurable  
> slowdown, between 1% and 2%
>
>> For the current spec, concurrency isn't a huge issue, because
>> everything will be serialized at some level anyhow.
>
> Nothing in the current spec forces 2 different browsing contexts  
> from operating concurrently, resulting in the possibility of their  
> own transactions stomping each other.
>
>> [Sorry, don't mean to sound like I'm flip-flopping.  My concerns  
>> about
>> implicit transactions aren't really performance related. :-).]
>
> My concerns about them are more than just performance related ones.   
> A forced performance penalty just drives me mad ;)
>
> ~Brady
>