| | | | | Browse by category |
How does the DBTools connection pool work? How and when are connections created, opened, and closed?
Action
The connection pool is set to 1 by default. You can create connections both explicitly (by using RWDBDatabase::connection()), and implicitly (e.g. by calling RWDBInserter::execute() without passing a connection argument. In this case the inserter will create/grab a connection).
Whenever you create a database instance, it instantiates a connection pool, meaning a connection pool with one default connection is opened between DBTools.h++ and your database.
RWDBDatabase mydatabase = RWDBManager::database(, , , , );
Now instatiate a connection object as follows
RWDBConnection conn1 = mydatabase.connection();
This connection conn1 will use the already opened default connection and won't create a new connection. If you create another connection conn2, as there are no open connections available, conn2 creates a new connection between DBTools.h++ and the database.
If one of these two connections, e.g. conn2, goes out of scope, the connection will be put into the connection pool since the pool currently does not have any available connections and its size is one.
If conn1 then goes out of scope, it will be closed, since there is no room left in the connection pool.
How can I have more connections opened throughout my application?
Let's say your database is capable of 50 connections and you want to use 10 of them.
By default the RWDBConnection pool is set to 1. Now increase the connection pool size to 10 as follows:
mydatabase.defaultConnections(10);
Next create 10 explicit connections:
RWDBConnection conn1 = mydatabase.connection();
RWDBConnection conn2 = mydatabase.connection();
RWDBConnection conn3 = mydatabase.connection();
...
RWDBConnection conn10 = mydatabase.connection();
At this point you have 10 connections open to the database.
Now force them to go out of scope, that is, let conn1, conn2 ... conn10 go out of scope. You will still have 10 connections open in the connection pool.
If you use a default connection, e.g.:
RWDBReader rdr = myselector.reader(); // not passing connection
this will first try to grab a connection from the connection pool. If there are none available, it will try to establish a new connection to the database. (This is why using default connections can slow down your program considerably.)
When a statement using a default connection is finished, it will attempt to return the connection to the connection pool. If there is space available it will be inserted; however, if there is no space available the connection will be closed. This can lead to connection thrashing, slowing your program down by several orders of magnitude.
Hint: For production code, always use explicit connections.
If you use explicit connections, e.g.:
RWDBReader rdr = myselector.reader(conn);
there is less chance of continually establishing and closing connections to the database since you are more likely to reuse this connection throughout your code block (instead of getting a new connection for every operation).
The same return/close connection algorithm is followed when an explicit connection goes out of scope.
