Cannot turn off auto commit?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Cannot turn off auto commit?

list+org.hsqldb.user
Hello.

The following trivial example seems like it should not commit anything
to the database, but it does.

  public static void main(
    final String[] args)
    throws Exception
  {
    final Properties props = new Properties();
    props.setProperty("user", "SA");
    props.setProperty("password", "");
    props.setProperty("create", "true");
    props.setProperty("shutdown", "false");
    props.setProperty("sql.enforce_types", "true");
    props.setProperty("sql.enforce_names", "true");
    props.setProperty("sql.regular_names", "true");
    props.setProperty("sql.enforce_refs", "true");
    props.setProperty("sql.enforce_size", "true");
    props.setProperty("sql.enforce_types", "true");
    props.setProperty("sql.enforce_types", "true");
    props.setProperty("hsqldb.applog", "3");
    props.setProperty("hsqldb.sqllog", "3");

    final JDBCDataSource ds = new JDBCDataSource();
    ds.setUrl("jdbc:hsqldb:file:/tmp/example");
    ds.setProperties(props);

    try (final Connection conn = ds.getConnection()) {
      conn.setAutoCommit(false);

      try (final PreparedStatement st =
             conn.prepareStatement("CREATE CACHED TABLE broken (x INTEGER)")) {
        st.execute();
      }
    }
  }

I can add explicit calls to conn.rollback(), but the commit still
occurs. If I run the program twice, the second execution throws
an exception because the "broken" table exists. It should not!

What am I doing wrong?

M

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

_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

attachment0 (837 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Cannot turn off auto commit?

Fred Toussi-2
A DDL statement is not transactional. A commit is executed implicitly
both before and after the statement execution.

If you want to avoid the exception when the statement is executed the
second time, use:

CREATE CACHED TABLE IF NOT EXISTS broken ...

Statements such as INSERT or DELETE are transactional and will be rolled
back when you call rollback() on the connection.

Fred

On Wed, Sep 7, 2016, at 21:18, [hidden email] wrote:

> Hello.
>
> The following trivial example seems like it should not commit anything
> to the database, but it does.
>
>   public static void main(
>     final String[] args)
>     throws Exception
>   {
>     final Properties props = new Properties();
>     props.setProperty("user", "SA");
>     props.setProperty("password", "");
>     props.setProperty("create", "true");
>     props.setProperty("shutdown", "false");
>     props.setProperty("sql.enforce_types", "true");
>     props.setProperty("sql.enforce_names", "true");
>     props.setProperty("sql.regular_names", "true");
>     props.setProperty("sql.enforce_refs", "true");
>     props.setProperty("sql.enforce_size", "true");
>     props.setProperty("sql.enforce_types", "true");
>     props.setProperty("sql.enforce_types", "true");
>     props.setProperty("hsqldb.applog", "3");
>     props.setProperty("hsqldb.sqllog", "3");
>
>     final JDBCDataSource ds = new JDBCDataSource();
>     ds.setUrl("jdbc:hsqldb:file:/tmp/example");
>     ds.setProperties(props);
>
>     try (final Connection conn = ds.getConnection()) {
>       conn.setAutoCommit(false);
>
>       try (final PreparedStatement st =
>              conn.prepareStatement("CREATE CACHED TABLE broken (x
>              INTEGER)")) {
>         st.execute();
>       }
>     }
>   }
>
> I can add explicit calls to conn.rollback(), but the commit still
> occurs. If I run the program twice, the second execution throws
> an exception because the "broken" table exists. It should not!
>
> What am I doing wrong?
>
> M
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> Email had 1 attachment:
> + Attachment1.2
>   1k (application/pgp-signature)

------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Cannot turn off auto commit?

list+org.hsqldb.user
Hello!

On 2016-09-07T22:08:06 +0100
Fred Toussi <[hidden email]> wrote:

>
> A DDL statement is not transactional. A commit is executed implicitly
> both before and after the statement execution.
>
> If you want to avoid the exception when the statement is executed the
> second time, use:
>
> CREATE CACHED TABLE IF NOT EXISTS broken ...
>
> Statements such as INSERT or DELETE are transactional and will be rolled
> back when you call rollback() on the connection.
That's bizarre. Has this always been the case?

I remember a few years back trying to decide between HSQLDB and H2, and
picking HSQLDB because DDL statements appeared to be transactional.

Without transactional DDL, how do I guarantee that my database is set
up correctly and atomically? With implicit commits, any failure means
that I end up with a half configured database that has to then be
destroyed and created from scratch.

M

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

_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

attachment0 (837 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Cannot turn off auto commit?

Fred Toussi-2
Non-transactional DDL has always been the case.

If your DDL statements do not contain syntax errors or references to
non-existent database objects, then they won't fail the first time they
are executed.

The IF NOT EXISTS clause can be used with all database objects.

You can query the INFORMATION_SCHEMA views to check if a table or other
object exists, before creating database objects.

You can always drop the whole schema if you want to recreate it.

Fred

On Wed, Sep 7, 2016, at 22:17, [hidden email] wrote:

> Hello!
>
> On 2016-09-07T22:08:06 +0100
> Fred Toussi <[hidden email]> wrote:
> >
> > A DDL statement is not transactional. A commit is executed implicitly
> > both before and after the statement execution.
> >
> > If you want to avoid the exception when the statement is executed the
> > second time, use:
> >
> > CREATE CACHED TABLE IF NOT EXISTS broken ...
> >
> > Statements such as INSERT or DELETE are transactional and will be rolled
> > back when you call rollback() on the connection.
>
> That's bizarre. Has this always been the case?
>
> I remember a few years back trying to decide between HSQLDB and H2, and
> picking HSQLDB because DDL statements appeared to be transactional.
>
> Without transactional DDL, how do I guarantee that my database is set
> up correctly and atomically? With implicit commits, any failure means
> that I end up with a half configured database that has to then be
> destroyed and created from scratch.
>
> M
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> Email had 1 attachment:
> + Attachment1.2
>   1k (application/pgp-signature)

------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Cannot turn off auto commit?

Daniel Stoner
Just for reference in case it came across a deficiency of hsqldb in particular, I am not aware of any databases (Oracle, mysql, hsqldb, h2, or indeed non-relational dbs such as dynamo) that provide transactional DDL. Data is committed but schemas are definitely not.

Here is a helpful guide to the DDL basics: https://en.wikipedia.org/wiki/Data_definition_language.

If you are a Java developer then frameworks such as Hibernate can be configured to check and create tables as they are needed (EG You just worrying about inserting and deleting rows and if the table isn't there it'll be created with all the right fields. Equally if the table requires an change like a new column it can do this too) and are a superb abstraction from dealing with JDBC datasources directly. Typically it is not required to be so low level to the mechanism of connection as this and code will look cleaner if you take advantage of existing solutions.

Thanks kindly,
Daniel Stoner

On 7 September 2016 at 23:00, Fred Toussi <[hidden email]> wrote:
Non-transactional DDL has always been the case.

If your DDL statements do not contain syntax errors or references to
non-existent database objects, then they won't fail the first time they
are executed.

The IF NOT EXISTS clause can be used with all database objects.

You can query the INFORMATION_SCHEMA views to check if a table or other
object exists, before creating database objects.

You can always drop the whole schema if you want to recreate it.

Fred

On Wed, Sep 7, 2016, at 22:17, [hidden email] wrote:
> Hello!
>
> On 2016-09-07T22:08:06 +0100
> Fred Toussi <[hidden email]> wrote:
> >
> > A DDL statement is not transactional. A commit is executed implicitly
> > both before and after the statement execution.
> >
> > If you want to avoid the exception when the statement is executed the
> > second time, use:
> >
> > CREATE CACHED TABLE IF NOT EXISTS broken ...
> >
> > Statements such as INSERT or DELETE are transactional and will be rolled
> > back when you call rollback() on the connection.
>
> That's bizarre. Has this always been the case?
>
> I remember a few years back trying to decide between HSQLDB and H2, and
> picking HSQLDB because DDL statements appeared to be transactional.
>
> Without transactional DDL, how do I guarantee that my database is set
> up correctly and atomically? With implicit commits, any failure means
> that I end up with a half configured database that has to then be
> destroyed and created from scratch.
>
> M
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> Email had 1 attachment:
> + Attachment1.2
>   1k (application/pgp-signature)

------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user



--
Daniel Stoner | Senior Software Engineer UtopiaIT | Ocado Technology


Notice:  This email is confidential and may contain copyright material of members of the Ocado Group. Opinions and views expressed in this message may not necessarily reflect the opinions and views of the members of the Ocado Group. 

 

If you are not the intended recipient, please notify us immediately and delete all copies of this message. Please note that it is your responsibility to scan this message for viruses. 

 

Fetch and Sizzle are trading names of Speciality Stores Limited and Fabled is a trading name of Marie Claire Beauty Limited, both members of the Ocado Group.

 

References to the “Ocado Group” are to Ocado Group plc (registered in England and Wales with number 7098618) and its subsidiary undertakings (as that expression is defined in the Companies Act 2006) from time to time.  The registered office of Ocado Group plc is Titan Court, 3 Bishops Square, Hatfield Business Park, Hatfield, Herts. AL10 9NE.


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

_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Cannot turn off auto commit?

list+org.hsqldb.user
In reply to this post by Fred Toussi-2
Hello.

On 2016-09-07T23:00:17 +0100
Fred Toussi <[hidden email]> wrote:

> Non-transactional DDL has always been the case.
>
> If your DDL statements do not contain syntax errors or references to
> non-existent database objects, then they won't fail the first time they
> are executed.

Right.

>
> The IF NOT EXISTS clause can be used with all database objects.
>
> You can query the INFORMATION_SCHEMA views to check if a table or other
> object exists, before creating database objects.
>
> You can always drop the whole schema if you want to recreate it.

Dropping the schema is fine for initial installation, but what about
schema upgrades? If I can't reliably and atomically update the structure
of tables, then it would seem that every upgrade has a risk of getting
the database into an inconsistent state.

M

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

_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

attachment0 (837 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Cannot turn off auto commit?

list+org.hsqldb.user
In reply to this post by Daniel Stoner
Hello.

On 2016-09-08T08:28:36 +0100
Daniel Stoner <[hidden email]> wrote:

> Just for reference in case it came across a deficiency of hsqldb in
> particular, I am not aware of any databases (Oracle, mysql, hsqldb, h2, or
> indeed non-relational dbs such as dynamo) that provide transactional DDL.
> Data is committed but schemas are definitely not.

Roughly half of the modern SQL database implementations support
transactional DDL. Unfortunately, none of those implemented in Java
support it. The most popular implementations with transactional DDL are
probably PostgreSQL and SQLite.

M

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

_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

attachment0 (837 bytes) Download Attachment