DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Because the DevOps movement has redefined engineering responsibilities, SREs now have to become stewards of observability strategy.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Related

  • Unlocking the Power of Configuration Management Database (CMDB)
  • Common Mistakes to Avoid When Writing SQL Code
  • Data-Based Decision-Making: Predicting the Future Using In-Database Machine Learning
  • Managed MQTT Broker Comparison — Console/Dashboard Features

Trending

  • Prioritizing Cloud Security Risks: A Developer's Guide to Tackling Security Debt
  • How Kubernetes Cluster Sizing Affects Performance and Cost Efficiency in Cloud Deployments
  • Optimizing Serverless Computing with AWS Lambda Layers and CloudFormation
  • Next Evolution in Integration: Architecting With Intent Using Model Context Protocol
  1. DZone
  2. Data Engineering
  3. Databases
  4. Close Your Database Connections! And Use Connection Pools While You're at It

Close Your Database Connections! And Use Connection Pools While You're at It

Ensuring your database connections are closed in a timely fashion can go a long way in keeping your database performant and available.

By 
Duncan Brown user avatar
Duncan Brown
·
Mar. 29, 16 · Analysis
Likes (4)
Comment
Save
Tweet
Share
143.5K Views

Join the DZone community and get the full member experience.

Join For Free

A wise man once said to me, "If you open a resource, make sure you close it when you're done."

Notice, he didn't say, "If you open a resource, make sure you close it when you're done, but if you don't, it's ok since the library/driver/runtime you're working with will eventually close it automatically."

If I had a nickel for every time I've seen resources opened and just assumed to be closed/eventually timed out, I'd be a rich man.  No example is more acute than that of database connections.

We are all guilty of doing it, but the effects of improperly closed database connections can be felt very strongly... and when you least expect or want them.  Everything will be humming along nicely in your newly-released production system until sometime in the not-too-distant future you get a panicked call from a customer/client wondering why their application/system isn't working anymore.  Or, possibly worse, the issue is intermittent with some users able to make use of the system while others cannot.

There's good news, though.  These kinds of issues can be dealt with using a little foresight and discipline.

Connection Pools Rule

Whenever possible, don't rely on your database's own raw connection sources.  These are typically configured in the database itself and don't lend themselves well to scaling or to configuring for different clients, which can be especially painful in multi-tenant situations.

Connection pools' greatest strengths lie in the ability to manage connections efficiently (thus freeing up the database to do more important things like query and serve up data) and to set a configurable maximum number of connections from which your application can draw.

There is no excuse not to use a connection pool.

Close What You Open When You Write the Open

Image title

(Courtesy: frinkiac.com)

Put more simply (but far less comically), write that connection.close(); a few lines below the connection.open(); when you first write that open command.  Write the intervening code between the two lines.  This way, you're forced to see and remember the connection close.

Be Exceptional

The use of try/catch blocks is a common pattern when dealing with resources.  Database connections are no different.  You can't always know when or where your code is going to fail, but you should still be prepared for it.

Make sure that you release any potentially open connections inside the appropriate catch blocks.  It's also a good idea to first check to see if you have a valid, open connection before trying to close it, lest you invoke another exception!

Testing, Diagnosing, and Troubleshooting

Most RDBMS systems have the ability to help you see if your code is properly closing the connection it is using.  For example, in MySQL, all you need to do is connect to the server via the command line (or your favorite client) as an admin (or user with appropriate permissions) and type,

SHOW PROCESSLIST;

... which can reveal information such as the following:

+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  4 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist | 
|  6 | root | localhost:3306  | webapp | Sleep   |  208 |       | NULL             | 
|  8 | root | localhost:3307  | webapp | Sleep   |  208 |       | NULL             | 
|  9 | root | localhost:3309  | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

From this information, we can quickly tell that not all connections are being properly closed.

I am frequently amazed (or at least very surprised) at the number of developers I have come across that aren't, at least, aware of this command.  Know it, use it, and love it!

Conclusion

While it may be true that libraries are getting better and smarter at managing these database connections, it's still no excuse for being lazy, so don't think that it's ok to just skip over these tips.  Resource leaks are the scourge of the development world.

Keep your customers happy by making sure that there are always enough connections available to service requests. And by keeping the number of open connections to a minimum, you also help improve your database's performance.

So, remember to always close those open database connections!

Connection (dance) Database IT

Opinions expressed by DZone contributors are their own.

Related

  • Unlocking the Power of Configuration Management Database (CMDB)
  • Common Mistakes to Avoid When Writing SQL Code
  • Data-Based Decision-Making: Predicting the Future Using In-Database Machine Learning
  • Managed MQTT Broker Comparison — Console/Dashboard Features

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!