Re: problem with new autocommit config parameter and jdbc

On Wed, 11 Sep 2002, snpe wrote:

> On Tuesday 10 September 2002 11:50 pm, Stephan Szabo wrote:
> > On Tue, 10 Sep 2002, snpe wrote:
> > > On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote:
> > > > What if it's a select for update? IF that failed because of a timout
> > > > on a lock, shouldn't the transaction fail? Or a select into? Either
> > > > of those should make a transaction fail, and they're just selects.
> > >
> > > Ok.Any lock or update,delete, insert (and all ddl command) start
> > > transaction (select for update, too), but simple select no.Select don't
> > > change data and no transaction - this process cannot lost consistency
> > > (any command with error too).
> >
> > At least in serializable isolation level you'll probably get different
> > results if a transaction commits between those two selects based on
> > whether a transaction is started or not. Should two serializable selects
> > in the same session see the same snapshot when autocommit is off?

> It is session, not transaction.My select don't change data and this is not
> transaction.

We're going around in circles.

Does it matter if data is changed? I don't think so, since at least in
serializable isolation level the snapshot that is seen depends on whether
you're in a transaction or not, and given autocommit=off I believe that
you should get a consistent snapshot between them.

If you believe it should matter, you need to give a reason. I don't
think it's a spec reason given that my sql92 spec draft says:

"The following SQL-statements are transaction initiating SQL-
statements, i.e., if there is no current transaction, and a
statement of this class is executed, a transaction is initiated:
...
o <select statement: single row>

o <direct select statement: multiple rows>"
unless it changed.

There might be a compatibility reason, if so, with what and is it stronger
than reasons to start a transaction.

There might be another logical reason, if so, what is it and why does
it matter?

> My abother question, agian : why error (bad typing) start transaction ?

That depends. Given the way the spec is worded, it says nothing about
other statements, so we need to decide those ourselves. I don't see
anything that implies that a select statement that errors would be
any different than a select statement that doesn't as far as starting
a transaction goes in my sql92 spec draft. If you were to type in
foo as a command, I could see a case that maybe that shouldn't be
transaction initiating, but afair that wasn't the case you had, you
had a select command against an invalid table name.