Path: csiph.com!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Cecil Westerhof Newsgroups: comp.lang.java.programmer Subject: Re: Not closing connections and statements Date: Fri, 12 Aug 2016 23:55:16 +0200 Organization: Decebal Computing Lines: 152 Message-ID: <87k2fllkjf.fsf@Equus.decebal.nl> References: <87shuakxjq.fsf@Equus.decebal.nl> Mime-Version: 1.0 Content-Type: text/plain Injection-Info: mx02.eternal-september.org; posting-host="12baf5125b19322b899c79c2e3878015"; logging-data="4534"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/eWThAG0lmPfroJXUIkcwy2CDwLsUYuCg=" User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux) X-Homepage: http://www.decebal.nl/ Cancel-Lock: sha1:VSCiq0nuBgQrKS+RtZOFWalAP78= sha1:NdBosjh6ORj/yxZ9GQbFkOL3Khk= Xref: csiph.com comp.lang.java.programmer: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