[whatwg] Comments on updated SQL API

[Scott Hess]

On 10/17/07, Aaron Boodman <aa at google.com> wrote:
> On 10/17/07, Maciej Stachowiak <mjs at apple.com> wrote:
> > - An author mistake (like doing something that causes an exception in
> > the callback) means a stuck lock, quite possibly ruining the whole
> > session.
>
> I can say from experience that this is a very real problem. It is
> probably the number one problem that people run into with the current
> Gears API.

I agree with Aaron.  Having transactions explicitly in the API is
useful, because langauge features can be integrated (for instance,
unhandled JavaScript exceptions can result in a ROLLBACK), and because
it's very easy to make a mistake that locks things up.

> Another problem is that developers actually don't realize they need to
> use BEGIN and COMMIT and they end up writing extremely slow code and
> wondering why. Making the transactions be automatic is a big win for
> making the API performant by default.

Here I'm mixed.  There are multiple reasons to use transactions.  The
most primary use is for correctness reasons.  Using them to improve
performance is an implementation detail, in a high-concurrency system
a bunch of bare statements is likely to allow more performance than
the same statements in a transaction.  I'm concerned that making
transactions implicit to address an implementation detail like
performance may cause unforseen correctness issues.

On 10/16/07, Ian Hickson <ian at hixie.ch> wrote:
> On Fri, 5 Oct 2007, Scott Hess wrote:
> > 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?

Brady's point about holding the locks excessively is one.

How would the current system interact with nested transactions?  The
obvious answer is that each executeSql() would create a new nested
transaction, but if you logically want a nested transaction, and want
to do a ROLLBACK, you want to ROLLBACK the logical nested transaction,
not the one associated with the enclosing executeSql().  I was
thinking you could call closeTransaction() [which would COMMIT the
immediately local executeSql()], then throw an exception to cause a
ROLLBACK, but as currently spec'ed I think that will poison the entire
transaction.

I must note that often enough databases don't support real nested
transactions anyhow.  By "real", I mean that you can ROLLBACK a nested
transaction yet continue operating within the next transaction out.
There's also "fake" nested transactions, where you just count how deep
you are on the stack and any ROLLBACK implies a global ROLLBACK.  It
depends on whether the spec is targetting SQL or a specific
implementation of SQL.

I can't even begin to say whether this will interact badly with
savepoints, mainly because I don't have experience with them.  Maybe
someone else can comment in that area.

I think I generally agree with Maciej, but differ that I think the
conservative stance would be to not introduce implicit transactions
and later add them, rather than introduce them and later add a way to
not have them.  I do think real experience will be useful, because
combining transactions and asynchronous operation makes it hard to see
through to which issues are real.  It may be that convoluted
nested-transaction systems are just impossible to understand when
phrased asynchronously, so making the API work with them is not
valuable.

-scott