[Info-ingres] Is Ingres supposed to rollback in case of the connection dropping?

[Roy Hann]

nikosv wrote:

> I've noticed that in the environment that these glitches occur has the
> default SET SESSION with ON_ERROR= rollback statement; not SET SESSION
> with ON_ERROR= rollback transaction; which might explain what's happening,
> but I have to investigate further.

If the application is leaving transaction management to Ingres and
counting on it to automatically roll back transactions when an error
occurs, yes, that probably would account for what you are seeing.

> In general, when you open a persistent abf or isql session, what is
> Ingres considering as the beginning of a transaction given the absence
> of the begin/end transaction statements

You are tugging at a loose thread there. I hope you're ready for this...
SQL transaction management is fundamentally broken.

In SQL, transactions start implicitly. If you execute
an INSERT/UPDATE/DELETE/SELECT and you do not already have a
transaction in progress, a transaction is started for you. 

That is an incomprehensible design blunder in SQL. 

Trying to execute a statement outside the context of a transaction
should be an error. Way long ago Ingres treated it as an error. Ingres
had to have that foolishness retrofitted in order to support ANSI SQL.

Why is it a blunder? It's a long but simple story.

A database should always appear to move instantly from a consistent
state to a consistent state. It is in a consistent state when all the
constraints are satisfied. (I do not mean declared constraints. I mean
the constraints that necessarily exist as "laws of nature" in the
entreprise of interest, declared or not.)

Because constraints are rarely declared, and because SQL doesn't
even have syntax to define all the real-world constraints, the burden is
on you (the programmer) to warrant that you have put the database into a
consistent state before you commit. When you code COMMIT you are saying
that in your professional judgement, knowing what you know about the
enterprise of interest and the application, you warrant the transaction
has left the database in a fit state. (You are like a civil engineer
signing off a bridge design as fit for use.)

But no significant application is written by one programmer, and it
certainly isn't maintained by one programmer over decades. And it
could well call externals that do who-knows-what, today _and_ in the
future?

So how are you supposed to put your hand on your heart and say, yep, I
warrant that the database is now consistent? You can't know where the
transaction started. You might have a hunch if you coded the whole thing
today, but what about a change you make to 10 year old code? What if
you insert a call to an external that--unknown to you--does a COMMIT?
What if in future someone changes that external so it COMMITs? What
about deadlock and serialization failure handling--what are you going
to redo? How do you confidently decide?

If an out-of-transaction statement were an error you would very quickly
find out that your changes have broken something. You would also know
exactly what to redo after a deadlock.

But no; some long-forgotten cretin thought it would be cute or
convenient to start transactions implicitly. The statement shouldn't be
COMMIT; it should be WHATEVER. (I'd be a little less angry if an
implicitly started transaction were allowed only to SELECT, i.e. if it
were forced to be read-only.)

> When you manually rollback as in "inquire_ingres (h_rowcount = rowcount,
> h_errorno = errorno)" and "if (h_errorno!=0) then rollback" is the set
> session directive honored?

It's irrelevant. There is a savepoint between statements. The automatic
rollback rolls back to the savepoint. If you explicitly ROLLBACK and
don't qualify it with a savepoint name, the entire transaction is rolled
back, as you (probably) intend.

Roy