Re: [Hsqldb-user] AUTOCOMMIT

[fredt <fr...@us...>]

When autocommit is enabled, an implicit commit statement is issued after
each insert or delete. You cannot do a rollback.

With autocommit disabled, you insert the row into B. At this point this row
is visible, i.e. will be included in the result of queries. You can then
either rollback (to remove the row) or continue with the next row insert
into table A.

You then insert the row into A and this row becomes visible too. If you
issue ROLLBACK at any point, the work you have done since the last commit is
lost. Alternatively, if you are happy with the results, issue COMMIT.

If you don't issue COMMIT, at the end of the session (when the connection is
closed) all your work since the last commit statement will be lost. All the
statements in a transaction should be issued from the same connection to the
database.

Fred Toussi

-------------

Dale Erwin wrote

I am using hsqldb 1.7.0, and it seems that maybe I don't understand
just how Autocommit works.  When it is enabled, it seems that it is not
possible to do a rollback.  When it is not enabled, referential integrity
constraints are useless.  Let me explain with an example:

Table A has a column which must be present in Table B.
I insert a row first into Table B, then a corresponding row into Table A.
This is a single unit of work.  If autocommit is enabled, a successful
insert into Table B cannot be rolled back if a problem is encountered
before the row can be inserted into Table A.  With autocommit disabled,
the insert into Table A cannot be done at all, because the row inserted
into Table B is not found until the commit is done.

At least, that is what appears to be happening.  Is this indeed the way
autocommit works?