[whatwg] Database feedback

[Scott Hess]

On Wed, Nov 26, 2008 at 8:58 AM, Aaron Boodman <aa at google.com> wrote:
> On Wed, Nov 26, 2008 at 3:46 AM, Ian Hickson <ian at hixie.ch> wrote:
>> We could have a .writeTransaction() and a .readTransaction(), where the
>> former always run in isolation.
>>
>> Any preferences?
>
> My preference is for separating read transactions from write
> transactions. Then the API could throw if you tried to write in a read
> transaction.

In SQLite the lock is upgraded as-needed, and it does cause confusion.
 The following can fail to get the write lock at the marked statement:

  BEGIN DEFERRED;
     SELECT x, y, z FROM t WHERE q = ?;
     INSERT INTO j VALUES (?, ?, ?);   -- *mark*
  COMMIT;

In SQLite BEGIN is the same as BEGIN DEFERRED.  Gears changes the
default for BEGIN to BEGIN IMMEDIATE, which acquires the lock right
away.  If a developer wants to do a read-only transaction, they can
explicitly execute BEGIN DEFERRED.

In any case, my point is that upgrading mid-stream seems to be a point
of confusion for people.  You can explain what's happening, and they
will understand it, but the case comes up seldom enough that most
people never internalize it.  Requiring an explicit decision up front
makes things clear, and doesn't absurdly widen the API.

I would lean towards .transaction() and .readTransaction(), though.
Acquiring the lock immediately is almost always the right thing to do.
 You can code a join to get consistent data from multiple tables, but
you cannot code a single statement to insert data consistently into
multiple tables (ignoring triggers), which is why read-only explicit
transactions are rare, and write-only explicit transactions are
common.  I'd say that write-mostly transactions (one or two reads
followed by a bunch of writes) are probably also more common than
read-only transactions.

-scott