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

Groups > comp.lang.java.programmer > #35274

Re: Not closing connections and statements

Show all headers | View raw

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       |

Back to comp.lang.java.programmer | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web