Why Always Close Database Connection

Why always close Database connection?

You should not leave connections open.

You should:

1. Open connections as late as possible
2. Close connections as soon as possible

The connection itself is returned to the connection pool. Connections are a limited and relatively expensive resource. Any new connection you establish that has exactly the same connection string will be able to reuse the connection from the pool.

We strongly recommend that you always
close the connection when you are
finished using it so that the
connection will be returned to the
pool. You can do this using either the
Close or Dispose methods of the
Connection object, or by opening all
connections inside a using statement
in C#, or a Using statement in Visual
Basic. Connections that are not
explicitly closed might not be added
or returned to the pool. For more
information, see using Statement (C#
Reference) or How to: Dispose of a
System Resource for Visual Basic. Ref.

You should appropriately wrap anything that implements IDisposable in a using statement block:

 using (SqlConnection connection = new SqlConnection(connectionString))
 {
     connection.Open();

     ...

     command.ExecuteNonQuery();
 }

Why should we always close a connection to a database?

You already mentioned first reason: resource leaks. This would mean that the usage of memory, sockets and file descriptors on your system is constantly increasing until your program or the database crashes, gets killed or brings down the operating system to its knees. Even before that happens, your system would likely become unresponsive, slow and prone to various timeouts, network disconnects and so on.

If your code depends on implicit commits (which is a bad idea anyway), you would be losing the data that your application writes to the database.

Not closing a connection could also leave locks and transactions in the database, which would mean that other connections get stuck while waiting on a lock held by the zombie connection. For example, if you have an external reporting system, it might stop working. Database backups might also stop working, leaving you vulnerable to loss of data.

Depending on circumstances, unfinished transactions could also fill up database transaction logs and/or temporary space, potentially bringing the database offline in a state that requires manual intervention.

If you are using connection pools, not closing a connection could be preventing a connection being returned to the pool. This would mean that connection pool would eventually get depleted, preventing your program from opening new connections.

When should we close database connection

Ok so actually based on Gray's comment this is how you do it with Spark :

JdbcPooledConnectionSource connectionSource = JdbcPooledConnectionSource(databasePath, username, password);

What I'm doing is using JdbcPooledConnectionSource instead of JdbcConnectionSource so we don't need to create a new connection for every reqeust or a single connection for all of the reqeusts.

But this is not all of it, we need some additional things for better performance :

connectionSource.setMaxConnectionsFree(10)
connectionSource.setMaxConnectionAgeMillis(20000)
connectionSource.setCheckConnectionsEveryMillis(5000)

By doing this you don't need to close connections by your self.

BTW if you think that this parameters need to change please let me know ;)

How often should I close database connections?

If you have connection pooling on (http://en.wikipedia.org/wiki/Connection_pool) its ok to be grabbing a new connection when you need it. HOWEVER, I'd say to be in the habit of treating any resource as "limited" and if you open the db handle keep it around for as long as possible.

When should I close database connection?

Connections are implicitly closed when a script has finished executing. The only reason you may want to close a connection is if you plan on opening another one; even then, PDO supports multiple concurrent connections.

Use of closing database connection in php

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

Freeing resources

Thanks to the reference-counting system introduced with PHP 4's Zend Engine, a resource with no more references to it is detected automatically, and it is freed by the garbage collector. For this reason, it is rarely necessary to free the memory manually.

Hope this helps you more.

(source)

edit:

The purpose of mysql_close() is also to save computer resources, but another key reason for using it is because there is a limited number of connections that a MySQL server can accept, and if you have several clients holding connections open for no reason then the server may well need to turn away other, waiting clients. Naturally this is a bad thing, so, as with mysql_free_result(), it is good to call mysql_close() if you think there will be some time between your last database use and your script ending.

Should a database connection stay open all the time or only be opened when needed?

The database connection must be opened only when its needed and closed after doing all the necessary job with it. Code sample:

• Prior to Java 7:

    Connection con = null;
    try {
        con = ... //retrieve the database connection
        //do your work...
    } catch (SQLException e) {
        //handle the exception
    } finally {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException shouldNotHandleMe) {
            //...
        }
    }
  

• Java 7:

    try (Connection con = ...) {
    } catch (SQLException e) {
    }
    //no need to call Connection#close since now Connection interface extends Autocloseable
  

But since manually opening a database connection is too expensive, it is highly recommended to use a database connection pool, represented in Java with DataSource interface. This will handle the physical database connections for you and when you close it (i.e. calling Connection#close), the physical database connection will just be in SLEEP mode and still be open.

Related Q/A:

• Java Connection Pooling

Some tools to handle database connection pooling:

• BoneCP
• c3po
• Apache Commons DBCP
• HikariCP

Do I need always close connection with db before return some value from function

No. There's very rarely a need to close a DB connection until you're actually done using it. Usually you don't even have to disconnect anyways, as PHP will do that automatically as part of the script's shutdown/cleanup routines.

Doing a connect-query->disconnect cycle repeatedly in your code is highly wasteful. The connect/disconnect sequences are just a waste of CPU cycles and memory resources. Connect once at the beginning, do however many DB operations are needed, then... just ignore the connection. PHP will handle the cleanup at the end.

About the only time you would want to force a disconnect is if your script is long-running, and you don't need the DB for the duration of that "long" section. Freeing up the DB connection may be useful in that case, but only if you have "many" other DB-using operations occurring in other parallel scripts. There's no point in holding a connection slot open and then not using it.

Similarly, you generally only ever need one database connection in your script, UNLESS you need to connect with different credentials to perform some sensitive operation, or have to connect to two or more completely separate DB engines.

Is it okay to always leave a database connection open?

I would say it's fine in this case, since there will only ever be one user and the database is hosted on the same machine (more likely in the same memory space, as I think SQLite just loads as a DLL with the main application) as the application. Constantly opening and closing the connection is unnecessary.

One possible exception might be if you need to have multiple threads of your application accessing the database at the same time. Then you could either force them to wait and share a single connection object, OR you could try to create new connections for the different threads. I have never actually tried this in SQLite. This is one situation where closing the main connection and opening/closing multiple connections might be better for a desktop app.

For web applications, or client/server desktop apps, I'd suggest against leaving connections open.

---

Related Topics