Leave a open database connection throughout the execution of the aplication, or for each time a operation will be executed a new connection will be created?

Open throughout the execution: Open aplication -> Open connection; Operations -> use the opened connection -> commit; Close aplication -> close connection;

For each time a operation will be executed: Open aplication -> ...; Operations -> create connection, execute operation, commit, close connection; Close aplication -> ...;

  • Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask – gnat Apr 17 '13 at 14:46
  • 2
    As with most things in software development, it depends. In the ASP.NET and SQL Server world, database connections are pooled, so newing up a connection can be extremely fast; seldom do we hold onto one. I have no idea how it works in the PHP MySQL world. – Robert Harvey Apr 17 '13 at 14:47
  • Same in the Java world as @RobertHarvey. Connections are normally pooled, "opening" a connection requests an open connection from the pool, "closing" a connection returns an open connection back into the pool. – Matt Apr 17 '13 at 14:57

The best practice for database connections is the same regardless of the language used.

You have a pool of open connections at the start of your application. You take a connection from the pool as late as possible. Return it to the pool as soon as possible.

Never hold a connection out of the pool unless it is being used. Don't have Connections as member variables in a class. I've seen lots of horrible data layer wrappers that do nothing more than duplicate the API interface while introducing unnecessary state with the Connection.

The API you program against may hide the pooling implementation. But you will still be responsible for upholding the take late, return soon rule.

You should code in a style that guarantees the connection is returned to the pool in the event of an exception. This could be achieved with try/finally constructs in Java. "using" blocks in C#. RAII in C++.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.