[DB-SIG] DBAPI-2.0 clarifications

[hme@informatik.uni-rostock.de]

You (brian zimmer) wrote:
> Thanks for asking some of the same questions I have while writing zxJDBC (http://www.ziclix.com/zxjdbc).  Below I'll add a bit of
> follow up to what you asked.
> 
> > Scavenging the mail folder uncovered M.-A. Lemburg's letter:
> > > Federico Di Gregorio wrote:
> > > >
> > > >   if a db support transactions, how two cursors derived from the same
> > > >   connection are supposed to see changes done to the db? if cursor A
> > > >   does an insert, is cursor B supposed to see the change suddenly or
> > > >   only after a commit() on the connection? imho, it would be better
> > >
> > > This is left undefined in the DB API spec since database usually
> > > have their own specific idea about how to handle such a situation
> > > (it is called transaction isolation level in the ODBC docs). The
> > > transaction isolation level can be specified on a per connection
> > > basis if the ODBC driver supports this.
> >
> > yes, i know what isolation level is. my question was about cursors
> > *derived from the same connection*. now, as you say, isolation level,
> > where vailable can be set on a "per connection" basis, so, are two
> > cursors created from the *same* connection **required** to see the
> > changes immediately or not?
> >
> 
> I believe they are required to see the changes immediately.

Exactly.

>                                                              Opening
> two connections is the only way to isolate the transactions.

Why would you isolate your program/transaction against what?  The same
program/transaction?  There is no isolation within one thread of execution,
one program or one transaction.  It uses the same set of local variable, ...

> Essentially, and please correct me if I'm wrong, a cursor is only a
> statement from a connection and has no transactional ability on
> it's own, so the connection must handle them as all together or
> commit on each one.

I could not see an useful application of having two connections to the
same database and the same database system from one program.  I mean,
regardless of how many connections or cursors are open you are always
in one program and one transaction.  Okay, this gets different if
multi-threading and nested transactions come into account.

> > > >   the api is not very clear on what a "set" is. it just says that not

[...]

My $.02

Holger

-- 
Holger Meyer, Uni of Rostock, Dpt. of CS, DB Research Group
hm at GUUG.de, http://www.informatik.uni-rostock.de/~hme/