I was asked this question in an interview. Why is it important to close a database connection? Is it just good practice because it might be wasting resources or there is something more to it?
Advertisement
Answer
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.