Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #35268 > unrolled thread

Not closing connections and statements

Back to article view | Back to comp.lang.java.programmer

Contents

  Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-12 13:59 +0200
    Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 09:15 -0400
      Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 09:39 -0400
      Re: Not closing connections and statements Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-12 22:31 +0200
        Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 22:04 -0400
          Re: Not closing connections and statements Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-13 07:55 +0200
    Re: Not closing connections and statements Martin Gregorie <martin@address-in-sig.invalid> - 2016-08-12 19:01 +0000
      Re: Not closing connections and statements Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-12 22:35 +0200
        Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 21:52 -0400
      Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-12 23:55 +0200
        Re: Not closing connections and statements Eric Sosman <esosman@comcast-dot-net.invalid> - 2016-08-12 18:16 -0400
          Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 01:19 +0200
            Re: Not closing connections and statements Martin Gregorie <martin@address-in-sig.invalid> - 2016-08-13 00:21 +0000
              Re: Not closing connections and statements Gunter Herrmann <gunter.herrmann@earthlink.net> - 2016-08-17 16:38 -0400
        Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 21:58 -0400
          Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 12:50 +0200
        Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 21:59 -0400
        Re: Not closing connections and statements Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-13 08:47 +0200
          Re: Not closing connections and statements Andreas Leitgeb <avl@logic.at> - 2016-08-13 08:05 +0000
            Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 12:21 +0200
            Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-13 09:54 -0400
          Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 12:19 +0200
          Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-13 10:04 -0400
            log4j RollingFileAppender (was: Not closing connections and statements) Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-14 22:24 +0200
              Re: log4j RollingFileAppender Arne Vajhøj <arne@vajhoej.dk> - 2016-08-14 21:01 -0400
          Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-13 11:58 -0400
            logging (was: Not closing connections and statements) Marcel Mueller <news.5.maazl@spamgourmet.org> - 2016-08-14 23:46 +0200
      Re: Not closing connections and statements Arne Vajhøj <arne@vajhoej.dk> - 2016-08-12 21:44 -0400
    Re: Not closing connections and statements Florian Weimer <fw@deneb.enyo.de> - 2016-08-13 16:33 +0200
      Re: Not closing connections and statements Cecil Westerhof <Cecil@decebal.nl> - 2016-08-13 17:34 +0200

Page 1 of 2  [1] 2  Next page →

#35268 — Not closing connections and statements

As I understood it you should close your SQL connections and
statements before quitting a program. How bad is this when that does
not happen?

At the moment I am writing a general function to log memory usage to a
SQLite database to be used by different applications. How bad would it
be if a program would exit without cleanly closing the connection and
statement?

The beginning of my code is at the moment:
    public static boolean freeMemoryThread(final String className, final String database) {
        final long                     divideValue  = 1024 * 1024;
        final Runtime                  rt           = Runtime.getRuntime();
        final ScheduledExecutorService ses          = Executors.newSingleThreadScheduledExecutor();

        Connection  conn = null;
        ResultSet   rs   = null;
        Statement   stmt = null;

        logMessage(className + ", " + database);
        try {
            Class.forName("org.sqlite.JDBC");
            conn    = DriverManager.getConnection("jdbc:sqlite:" + database);
            stmt    = conn.createStatement();
            rs      = stmt.executeQuery("SELECT SQLITE_VERSION() AS SQLiteVersion;");
            rs.next();
            logMessage(String.format("Using SQLite %s", rs.getString("SQLiteVersion")));
            rs      = null;
        } catch (Exception e) {
            logMessage("Could not connect with the database: " + e.getMessage());
            return false;
        }
        ses.scheduleAtFixedRate(new Runnable() {

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [next] | [standalone]

#35270

On 8/12/2016 7:59 AM, Cecil Westerhof wrote:
> As I understood it you should close your SQL connections and
> statements before quitting a program. How bad is this when that does
> not happen?
>
> At the moment I am writing a general function to log memory usage to a
> SQLite database to be used by different applications. How bad would it
> be if a program would exit without cleanly closing the connection and
> statement?

Not closing the connection is a very bad practice.

In a long running program then it can cause your application/database
to run out of connections without needing to.

In a standalone program that exits quickly, then the practical
impact may be little, but it is a very bad habit.

And when people get that habit for standalone programs that exits
quickly then they sometimes carry it over to long running programs.

Don't do it!

Not closing statements is not so bad. I believe that somewhere
in the JDBC spec it promise that all statements associated with
a connection get closed when the connection is closed.

I still recommend closing statements though. Just in case
the JDBC driver implementor forgot this little quirk.

> The beginning of my code is at the moment:
>     public static boolean freeMemoryThread(final String className, final String database) {
>         final long                     divideValue  = 1024 * 1024;
>         final Runtime                  rt           = Runtime.getRuntime();
>         final ScheduledExecutorService ses          = Executors.newSingleThreadScheduledExecutor();
>
>         Connection  conn = null;
>         ResultSet   rs   = null;
>         Statement   stmt = null;
>
>         logMessage(className + ", " + database);
>         try {
>             Class.forName("org.sqlite.JDBC");
>             conn    = DriverManager.getConnection("jdbc:sqlite:" + database);
>             stmt    = conn.createStatement();
>             rs      = stmt.executeQuery("SELECT SQLITE_VERSION() AS SQLiteVersion;");
>             rs.next();
>             logMessage(String.format("Using SQLite %s", rs.getString("SQLiteVersion")));
>             rs      = null;
>         } catch (Exception e) {
>             logMessage("Could not connect with the database: " + e.getMessage());
>             return false;
>         }
>         ses.scheduleAtFixedRate(new Runnable() {

With a ScheduledExecutor then I assume that it may not be exiting
that quickly.

(but on the good side then SQLite is an embedded database, so the
resource leak may be less critical)

Anyway - why not just close?

And if you are on Java 1.7 or later then use the AutoCloseable
feature:

try(Connection con = DriverManager.getConnection(conurl)) {
     // use con and don't worry about closing
}

You do not even need to call close anywhere (normal flow,
exception flow).

Arne

[toc] | [prev] | [next] | [standalone]

#35272

On 8/12/2016 9:15 AM, Arne Vajhøj wrote:
> Not closing statements is not so bad. I believe that somewhere
> in the JDBC spec it promise that all statements associated with
> a connection get closed when the connection is closed.
>
> I still recommend closing statements though. Just in case
> the JDBC driver implementor forgot this little quirk.

 From the JDBC 4.2 specs:

9.6.4.1 Connection.close
An application calls the method Connection.close() to indicate that it has
finished using a connection. All Statement objects created from a given
Connection object will be closed when the close method for the Connection
object is called.

Arne

[toc] | [prev] | [next] | [standalone]

#35276

On 12.08.16 15.15, Arne Vajhøj wrote:
> Not closing the connection is a very bad practice.
>
> Don't do it!

+1, however, one of the most critical points can be deadlocks. Depending 
on your database lock model even select statements can prevent other 
transactions from proceeding, especially when the statements and readers 
are also not closed.


> Not closing statements is not so bad. I believe that somewhere
> in the JDBC spec it promise that all statements associated with
> a connection get closed when the connection is closed.

True, but you may get deadlocks with you own statements if locks are 
still pending. E.g. a pending reader may prevent other updates from 
proceeding. Some databases also reject more than one open data reader. 
You may get exceptions at subsequent statements in this case.

> And if you are on Java 1.7 or later then use the AutoCloseable
> feature:

again +1


Marcel

[toc] | [prev] | [next] | [standalone]

#35290

On 8/12/2016 4:31 PM, Marcel Mueller wrote:
> On 12.08.16 15.15, Arne Vajhøj wrote:
>> Not closing the connection is a very bad practice.
>>
>> Don't do it!
>
> +1, however, one of the most critical points can be deadlocks. Depending
> on your database lock model even select statements can prevent other
> transactions from proceeding, especially when the statements and readers
> are also not closed.
>
>> Not closing statements is not so bad. I believe that somewhere
>> in the JDBC spec it promise that all statements associated with
>> a connection get closed when the connection is closed.
>
> True, but you may get deadlocks with you own statements if locks are
> still pending. E.g. a pending reader may prevent other updates from
> proceeding. Some databases also reject more than one open data reader.
> You may get exceptions at subsequent statements in this case.

I am not sure that I understand the deadlock thing.

SQLTransactionRollbackException like other SQLTransientException can
be retried.

If the commit was successful then I would expect all locks
to be released and no SQLTransactionRollbackException.

Am I missing something?

Arne

[toc] | [prev] | [next] | [standalone]

#35291

On 13.08.16 04.04, Arne Vajhøj wrote:
>> True, but you may get deadlocks with you own statements if locks are
>> still pending. E.g. a pending reader may prevent other updates from
>> proceeding. Some databases also reject more than one open data reader.
>> You may get exceptions at subsequent statements in this case.
>
> I am not sure that I understand the deadlock thing.
>
> SQLTransactionRollbackException like other SQLTransientException can
> be retried.
>
> If the commit was successful then I would expect all locks
> to be released and no SQLTransactionRollbackException.
>
> Am I missing something?

No, but there is only a very small step from not closing a statement to 
not calling rollback. And it is another not too large step to not 
closing data readers. So it is rather likely that pending statements 
keep some DB resources until the GC happens to catch them. Especially if 
an exception (e.g. duplicate key) raises in the middle of a transaction.
At this point there is still no deadlock so far, only tremendously bad 
performance because the dependency on the GC. But if your application 
deals with parallel connections or instances the probability of 
deadlocks is significantly increased.
I have found many SQL based applications that are not free of 
distributed race conditions (involving the database server), especially 
when the DB deals with page locks which widens the locks unexpectedly to 
more or less randomly chosen rows. Who really operates at serializable 
isolation level in a larger application? Mostly these deadlocks are that 
unlikely that it might not even happen once in their life time. 
Introducing larger delays in transactions or locks respectively may 
increase this probability significantly, similarly to the birthday paradox.

As others mentioned it is no big deal for short running, single threaded 
applications, of course. It is usually also no big impact for 
applications without transactional integrity, i.e with auto-commit. 
However, databases without snapshot isolation may run into trouble with 
open data readers too.


Marcel

[toc] | [prev] | [next] | [standalone]

#35274

On Fri, 12 Aug 2016 13:59:37 +0200, Cecil Westerhof wrote:

> As I understood it you should close your SQL connections and statements
> before quitting a program. How bad is this when that does not happen?
>
If you're going to use an SQL connection, it should be opened during 
initialisation and closed as one of the last actions of the process: 
opening and closing DB connections is generally pretty slow. Once opened, 
the connection should always be closed as one of the last actions in the 
process - putting the close() inside a finally{} block is a good idea.

Apart from that, the amount of harm caused by failure to close the 
connection depends on two things: how you configure the connection and 
how the jdbc calls that access data in the database are handled:

- if the database autocommits after every SQL statement and each JDBC
  call executes a single SQL statement, then failures can't
  do much damage to the database, but your program may end up quite
  limited on what you can do with the data.

- if the database autocommits after every SQL statement and these all 
  execute stored procedures rather than SQL DML statements, then you 
  can do pretty much whatever you want with the data but you may find
  you're pretty much locked you into a particular flavour of RDBMS.

- if you disable autocommit and execute several SQL DML statements 
  and/or stored procedures in each transaction, then you *MUST* 
  structure your code so that no matter what exceptions are thrown or
  conditions are violated after you've started a transaction, your 
  code will always commit or rollback the last transaction before it
  terminates. 

Done correctly, which means being rigorous in the way your code detects 
and handles JDBC/SQL exceptions, following any of the above options will 
make database corruption unlikely to happen, pretty much regardless of 
what hardware or software failure occurs. 

Done badly, and/or with insufficiently tested code, you'd better hope 
that DB backups are done frequently and reliably *and* make sure that 
data that has been accepted but not committed to the DB is securely 
stored where it will be replayed after a restart because for sure both 
the cached uncommitted data and the backups will get used.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#35277

On 12.08.16 21.01, Martin Gregorie wrote:
> If you're going to use an SQL connection, it should be opened during
> initialisation and closed as one of the last actions of the process:
> opening and closing DB connections is generally pretty slow.

No, not generally. The JDBC driver may use internal connection pooling 
that reduces the delay of opening a connection significantly. AFAIK the 
MS SQL driver works this way.

> Once opened,
> the connection should always be closed as one of the last actions in the
> process - putting the close() inside a finally{} block is a good idea.

AutoClosable is even better.


Marcel

[toc] | [prev] | [next] | [standalone]

#35287

On 8/12/2016 4:35 PM, Marcel Mueller wrote:
> On 12.08.16 21.01, Martin Gregorie wrote:
>> If you're going to use an SQL connection, it should be opened during
>> initialisation and closed as one of the last actions of the process:
>> opening and closing DB connections is generally pretty slow.
>
> No, not generally. The JDBC driver may use internal connection pooling
> that reduces the delay of opening a connection significantly. AFAIK the
> MS SQL driver works this way.

It should not.

The JDBC specs define:
- drivers provide DataSource and Connection for non-pooled connections
- drivers provide ConnectionPoolDataSource and PooledConnection to
   deliver connections to a pool
- the application server provides the pool

Documentation seems to indicate that the MS JDBC driver complies.

https://msdn.microsoft.com/en-us/library/ms378484(v=sql.110).aspx

<quote>
The Microsoft JDBC Driver for SQL Server provides support for Java 
Platform, Enterprise Edition (Java EE) connection pooling. The JDBC 
driver implements the JDBC 3.0 required interfaces to enable the driver 
to participate in any connection-pooling implementation that is provided 
by middleware vendors and is JDBC 3.0-compliant. Middleware such as Java 
EE application servers frequently provides compliant connection-pooling 
facilities. The JDBC driver will participate in pooled connections in 
these environments.

Note

Although the JDBC driver supports Java EE connection pooling, it does 
not provide its own pooling implementation. The driver relies on 
third-party Java Application Servers to manage the connections.
</quote>

Arne

[toc] | [prev] | [next] | [standalone]

#35278

On Friday 12 Aug 2016 21:01 CEST, Martin Gregorie wrote:

> On Fri, 12 Aug 2016 13:59:37 +0200, Cecil Westerhof wrote:
>
>> As I understood it you should close your SQL connections and
>> statements before quitting a program. How bad is this when that
>> does not happen?
>>
> If you're going to use an SQL connection, it should be opened during
> initialisation and closed as one of the last actions of the process:
> opening and closing DB connections is generally pretty slow. Once
> opened, the connection should always be closed as one of the last
> actions in the process - putting the close() inside a finally{}
> block is a good idea.

That is the case. As long as the program is running I need the
database connection. Only when the program is terminated the database
connection is not used anymore. I show the code at the end of this
message.

As I understood it the execution of finally is not guaranteed.


> Apart from that, the amount of harm caused by failure to close the
> connection depends on two things: how you configure the connection
> and how the jdbc calls that access data in the database are handled:
>
> - if the database autocommits after every SQL statement and each
> JDBC call executes a single SQL statement, then failures can't do
> much damage to the database, but your program may end up quite
> limited on what you can do with the data.

That is what I use yes. I just write one record an hour to log the
memory usage of the application. But maybe I am going to log more in
the future.


> - if the database autocommits after every SQL statement and these
> all execute stored procedures rather than SQL DML statements, then
> you can do pretty much whatever you want with the data but you may
> find you're pretty much locked you into a particular flavour of
> RDBMS.
>
> - if you disable autocommit and execute several SQL DML statements 
> and/or stored procedures in each transaction, then you *MUST* 
> structure your code so that no matter what exceptions are thrown or
> conditions are violated after you've started a transaction, your 
> code will always commit or rollback the last transaction before it
> terminates. 

I know. If I would use transactions I would take care of the commits
and/or rollbacks.


> Done correctly, which means being rigorous in the way your code
> detects and handles JDBC/SQL exceptions, following any of the above
> options will make database corruption unlikely to happen, pretty
> much regardless of what hardware or software failure occurs.
>
> Done badly, and/or with insufficiently tested code, you'd better
> hope that DB backups are done frequently and reliably *and* make
> sure that data that has been accepted but not committed to the DB is
> securely stored where it will be replayed after a restart because
> for sure both the cached uncommitted data and the backups will get
> used.

And the code:
    public static boolean freeMemoryThread(final String className, final String database) {
        final Connection                conn;
        final long                      divideValue  = 1024 * 1024;
        final String                    insertLine   =
            "INSERT INTO memUsageLine\n" +
            "(className, PID, freeAfter, freeBefore, maxMemory, totalAfter, totalBefore)\n" +
            "VALUES\n"                          +
            "(?, ?, ?, ?, ?, ?, ?);\n"                                                      +
            "";
        final String                    insertRun    =
            "INSERT INTO javaApplicationRun\n"                                              +
            "(className, PID, maxMemory)\n" +
            "VALUES\n"                                                                      +
            "(?, ?, ?);\n"                      +
            "";
        final long                      pid          =
            Long.parseLong(java.lang.management.ManagementFactory.getRuntimeMXBean().getName().split("@")[0]);
        final Runtime                   rt           = Runtime.getRuntime();
        final ScheduledExecutorService  ses          = Executors.newSingleThreadScheduledExecutor();

        logMessage(className + ", " + database);
        try {
            PreparedStatement   ps;
            ResultSet           rs;
            Statement           stmt;

            Class.forName("org.sqlite.JDBC");
            conn    = DriverManager.getConnection("jdbc:sqlite:" + database);
            stmt    = conn.createStatement();
            rs      = stmt.executeQuery("SELECT SQLITE_VERSION() AS SQLiteVersion;");
            rs.next();
            logMessage(String.format("Using SQLite %s", rs.getString("SQLiteVersion")));
            logMessage(Long.toString(pid));
            ps      = conn.prepareStatement(insertRun);
            ps.setString(1, className);
            ps.setLong(  2, pid);
            ps.setLong(  3, rt.maxMemory() / divideValue);
            ps.executeUpdate();
        } catch (Exception e) {
            logMessage("Could not connect with the database: " + e.getMessage());
            return false;
        }
        ses.scheduleAtFixedRate(new Runnable() {
                @Override
                public void run() {
                    try {
                        PreparedStatement   ps          = conn.prepareStatement(insertLine);
                        long                freeBefore  = rt.freeMemory()  / divideValue;
                        long                totalBefore = rt.totalMemory() / divideValue;
                        System.gc();
                        long                freeAfter   = rt.freeMemory()  / divideValue;
                        long                totalAfter  = rt.totalMemory() / divideValue;

                        ps.setString(1, className);
                        ps.setLong(  2, pid);
                        ps.setLong(  3, freeAfter);
                        ps.setLong(  4, freeBefore);
                        ps.setLong(  5, rt.maxMemory() / divideValue);
                        ps.setLong(  6, totalAfter);
                        ps.setLong(  7, totalBefore);
                        ps.executeUpdate();
                    } catch (Exception e) {
                        logMessage("Could not write memory line: " + e.getMessage());
                    }
                }
            }, 15, 60, TimeUnit.MINUTES);
        return true;
    }

The only thing that could go wrong is when someone exits the program
during the ps.executeUpdate. I think that is very unlikely and only
the last log record will be lost. That is not a big problem.

I heard a few times that it was very bad when your program terminates
without closing. I thought it a little bit strange: but better save as
sorry, that is why I wanted to check.


It works great: it is much better to get you log information out of a
(SQLite) database as from different files.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#35279

On 8/12/2016 5:55 PM, Cecil Westerhof wrote:
> On Friday 12 Aug 2016 21:01 CEST, Martin Gregorie wrote:
>
>> [...] the connection should always be closed as one of the last
>> actions in the process - putting the close() inside a finally{}
>> block is a good idea.
>[...]
> As I understood it the execution of finally is not guaranteed.

     Code in a `finally' block is guaranteed to execute, no matter how
the matching `try' block completes: return, break, continue, throw, or
run to the closing brace.  (Well, the "guarantee" is only as solid as
the JVM itself: If somebody shuts down the JVM with System.exit() or
similar, the no-longer-running JVM cannot execute `finally' blocks.
And if the JVM goes completely haywire and can no longer execute Java,
all bets are off.  But in the absence of such catastrophes, execution
of a `finally' block is guaranteed at the same level of confidence as
constructor chaining.)

     Perhaps you've confused `finally' with `finalize()'?

-- 
esosman@comcast-dot-net.invalid
"Don't be afraid of work. Make work afraid of you." -- TLM

[toc] | [prev] | [next] | [standalone]

#35281

On Saturday 13 Aug 2016 00:16 CEST, Eric Sosman wrote:

> On 8/12/2016 5:55 PM, Cecil Westerhof wrote:
>> On Friday 12 Aug 2016 21:01 CEST, Martin Gregorie wrote:
>>
>>> [...] the connection should always be closed as one of the last
>>> actions in the process - putting the close() inside a finally{}
>>> block is a good idea.
>> [...]
>> As I understood it the execution of finally is not guaranteed.
>
> Code in a `finally' block is guaranteed to execute, no matter how
> the matching `try' block completes: return, break, continue, throw,
> or run to the closing brace. (Well, the "guarantee" is only as solid
> as the JVM itself: If somebody shuts down the JVM with System.exit()
> or similar, the no-longer-running JVM cannot execute `finally'
> blocks. And if the JVM goes completely haywire and can no longer
> execute Java, all bets are off. But in the absence of such
> catastrophes, execution of a `finally' block is guaranteed at the
> same level of confidence as constructor chaining.)
>
> Perhaps you've confused `finally' with `finalize()'?

Yes, my bad: I did not read correctly. :'-(

But the finally block is no use. As long as the program is running the
connection is used. Only when the program terminates the connection
could/should be closed. But I do not really know when it is closed.
(Certainly not when others use the code.) With the information I have
now it looks like it is not really a problem when the connection is
not closed when it is used during the complete run time of the
program.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#35283

On Sat, 13 Aug 2016 01:19:25 +0200, Cecil Westerhof wrote:

> But the finally block is no use. As long as the program is running the
> connection is used.
>
No sir! As Eric said, by putting the DB close statement in a finally 
block you guarantee that the connectioin will be closed, causing the DBMS 
to flush buffers, release connection resources etc even if the program 
crashes horribly.


> Only when the program terminates the connection
> could/should be closed. But I do not really know when it is closed.
> (Certainly not when others use the code.)
>
Indeed, but I don't know any good reason for letting a user open or close 
a DB connection unless the user can choose which of a number of database 
instances to access: even in a utility like an SQL client that a 
developer or DBA might use to inspect and manipulate a database its 
unusual fore the user to open or close the connection - all the ones I';ve 
used expect the DB name, user name and password to be on the command line 
or prompted for. After that the program opens the connection without 
waiting for a command and closes it when the user gives the quit/exit 
command.

Open should be handled by the program's initialisation code which should 
end the run if the DB failed to open. Otherwise it should close the 
connection before the program exits, no matter what caused the exit.

Exactly the same considerations apply regardless of whether the DB client 
program is a single user interactive client or a multi-user application 
server.

> With the information I have
> now it looks like it is not really a problem when the connection is not
> closed when it is used during the complete run time of the program.
>
Maybe its not a problem for that application, but IMO not making sure 
that the connection(s) are closed when the program or user session 
terminates is a bad habit to get into: at the very least it may clog up 
the DB server if the unclosed connections persist and at worst it will 
bite you when least expected. 

Murphy is never mocked.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#35383

Martin Gregorie wrote:
> Maybe its not a problem for that application, but IMO not making sure
> that the connection(s) are closed when the program or user session
> terminates is a bad habit to get into: at the very least it may clog up
> the DB server if the unclosed connections persist and at worst it will
> bite you when least expected.
>
> Murphy is never mocked.

If you use an embedded SQL database such as Derby and you exit the 
application without closing the database first the JVM will not exit.
This will create a memory leak

-- 
Gunter Herrmann
Orlando, Fl

[toc] | [prev] | [next] | [standalone]

#35288

On 8/12/2016 5:55 PM, Cecil Westerhof wrote:
> That is the case. As long as the program is running I need the
> database connection. Only when the program is terminated the database
> connection is not used anymore. I show the code at the end of this
> message.

> And the code:
>     public static boolean freeMemoryThread(final String className, final String database) {
>         final Connection                conn;

>         final ScheduledExecutorService  ses          = Executors.newSingleThreadScheduledExecutor();

>         try {
>             PreparedStatement   ps;
>             ResultSet           rs;
>             Statement           stmt;
>
>             Class.forName("org.sqlite.JDBC");
>             conn    = DriverManager.getConnection("jdbc:sqlite:" + database);

>         } catch (Exception e) {
>             logMessage("Could not connect with the database: " + e.getMessage());
>             return false;
>         }
>         ses.scheduleAtFixedRate(new Runnable() {
>                 @Override
>                 public void run() {
>                     try {
>                         PreparedStatement   ps          = conn.prepareStatement(insertLine);

>                         ps.executeUpdate();
>                     } catch (Exception e) {
>                         logMessage("Could not write memory line: " + e.getMessage());
>                     }
>                 }
>             }, 15, 60, TimeUnit.MINUTES);
>         return true;
>     }

I would find it tempting to open the connection in main with a try
resource and pass the connection object to this method.

Arne

[toc] | [prev] | [next] | [standalone]

#35302

On Saturday 13 Aug 2016 03:58 CEST, Arne Vajhøj wrote:

> I would find it tempting to open the connection in main with a try
> resource and pass the connection object to this method.

But this is an utility function to be used by a lot of programs. I do
not want to need to have to do more as:
    freeMemoryThread(this.getClass().getSimpleName());

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#35289

On 8/12/2016 5:55 PM, Cecil Westerhof wrote:
> It works great: it is much better to get you log information out of a
> (SQLite) database as from different files.

I would use log4j and then be able to switch between FileAppender and
JDBCAppender via configuration.

Arne

[toc] | [prev] | [next] | [standalone]

#35293

On 12.08.16 23.55, Cecil Westerhof wrote:
> That is what I use yes. I just write one record an hour to log the
> memory usage of the application. But maybe I am going to log more in
> the future.

If it is just for logging with auto commit I would not care much.
However, it might or might not be a good advice to intentionally keep a 
DB connection idle for an hour. I would prefer to close the connection 
after each log write. In this scenario your question is pointless.

But as already mentioned, log4j might be a good advice.
But from my experience any logging to a database is at most the 
second-best solution. In critical situations it might not be reliable 
enough and you loose exactly the log records that you really need. Of 
course, it depends on your use case. Logging only once an hour might not 
fall into this category.


> I heard a few times that it was very bad when your program terminates
> without closing. I thought it a little bit strange: but better save as
> sorry, that is why I wanted to check.

/Terminating/ without closing is no big deal. Almost any existing 
operating system will clean up resources in this case. And almost any 
database will simply rollback any incomplete transaction.

> It works great: it is much better to get you log information out of a
> (SQLite) database as from different files.

As soon as your program runs twice for some reason, you might think 
different. ;-)
However, appending to the same file from different JVMs portably and 
atomically is also a challenge, albeit possible in Java.

In our company we ended up to do almost all logging into simple CSV 
files with a robust format that cannot be compromised if someone 
accidentally logs binary content.
We wrote a small library for this purpose and ported it to at least 
half-dozen programming languages. So any application can log in a 
thread-safe and atomic way to a log file (or in fact anything else as 
well), even if two applications or instances write to the same log for 
some reason. It is similar to log4j and in fact also usable as appender 
but with more features.
The library also handles switching the logs from time to time to limit 
their size as well as the cleanup of old logs. So no disk will get full 
after years, because no one cleaned up the logs. In contrast to log4j 
files will never get renamed because this could confuse concurrently 
reading applications or even cause locks (depending on the OS and file 
system). Instead we use (partial) time-stamps in the log name, e.g. the 
date in yyyymmdd format.

A reader application can browse this log files similarly to a database, 
including group by operations, parametric search and Regex search. The 
reader uses a small in memory database with deduplication, so even many 
logs with millions of entries can be searched in only a few seconds 
without much system resources.

Because of the common format of all these logs it turned out to be 
straight forward to write another small applications which scans all 
servers for theses logs and this way get simple usage statistics about 
all these applications and an overview of all used instances with a link 
to their logs using the reader above.


Marcel

[toc] | [prev] | [next] | [standalone]

#35294

Marcel Mueller <news.5.maazl@spamgourmet.org> wrote:
> However, it might or might not be a good advice to intentionally keep a 
> DB connection idle for an hour.

I've seen too many firewalls shut off connections after an hour or
even earlier to leave the "might"-option uncontradicted.

Well, it "might" be that somewhere else there is no such firewall,
but judging it as a piece of advice, I see no way it could possibly
be a good one.

[toc] | [prev] | [next] | [standalone]

#35301

On Saturday 13 Aug 2016 10:05 CEST, Andreas Leitgeb wrote:

> Marcel Mueller <news.5.maazl@spamgourmet.org> wrote:
>> However, it might or might not be a good advice to intentionally
>> keep a DB connection idle for an hour.
>
> I've seen too many firewalls shut off connections after an hour or
> even earlier to leave the "might"-option uncontradicted.

SQLite uses a local file, so I do not have to worry about firewalls.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.java.programmer

csiph-web