On Thu, Sep 12, 2019 at 10:19:52AM -0500, xxxxxx@ancell-ent.com wrote:
> [explicit transaction object] was discussed in the SRFI 167 Ordered
> Key Value Store (OKVS) mailing list, see e.g. "Unstructured
> transactions considered harmful" by John at:
> https://srfi-email.schemers.org/srfi-167/msg/11596319/

Perhaps I'm not completely following the argumentation, but it seems
like this is arguing against having a start-transaction/commit-transaction
etc and in favor of a more high-level with-transaction kind of procedure.

I would agree with that API (though you can't stop users from running
a BEGIN/COMMIT query manually), but I don't see why this requires
having the transaction be "reified" into an object.

I think this can only lead to more strange behaviour if the transaction
object escapes the dynamic extent of the procedure passed to
with-transaction.  Just like with-input-from-port dynamically rebinds
the current-input-port to a new port and then resets it when the
procedure exits, with-transaction could do the same thing.

How this interacts with continuations (jumping back in after or
jumping out before the procedure has completed) remains to be
discussed, but having explicit transaction objects makes it even
more confusing IMO, because they'd need to store state on whether
they are still usable or not and so on:

(with-transaction db
  (lambda (t1)
    (with-transaction db
      (lambda (t2)
        (query t1 "blablabla")))))

It's unclear to me what this should do.  Probably the only sane thing
one could do is raise an error.  It would be simpler as:

(with-transaction db
  (lambda ()
    (with-transaction db
      (lambda ()
        (query db "blablabla")))))

> One thing noted to be careful about, especially if folding
> transactions into the db object, is that you must make sure it
> works if you have two databases open, for example to copy data
> from one to the other.

That's simple; you'd simply have two connections, so two "db" objects
which are completely separate from eachother.  In fact, the postgresql
egg for CHICKEN has a few tests for transaction isolation modes which
do exactly this: open two connections and verify that one connection
can or cannot see the data at different points in a transaction.

> Another concept I bought up is that the API should not
> necessarily require formally requesting a transaction for single
> isolated statements.

Having the API I proposed above would make this point moot: there
is only one API for performing queries, which accepts the connection
object.

> And should not turn such from 1 to 3
> rounds trips to the database, that is, begin transaction, the
> action, close transaction, if instead you can have your database
> connection in an auto traction mode.

Agreed.  I think autocommit is the default for most databases anyway,
until you start a transaction manually.

Cheers,
Peter