clsql Connection Pool

Question

clsql seems to support connection pooling, since the connect method has the :pool key, and the cliki makes note of it being thread-safe when using with-database. I can't find an example of this being used online and I'm really not sure I'm using it right.

Currently I do something like this:

(defvar connection-string '("localhost" "database" "user" "password"))
(loop repeat 4 do (clsql:connect connection-string :pool t :database-type :mysql))

(defun called-on-seperate-thread (the-query)
  (clsql:with-database (db connection-string :pool t :database-type :mysql)
    (clsql:query the-query :database db)))

but only 2 of the 4 database connections ever get used. I've been running my application for about a week, and it seems to be thread-safe as the cliki suggested, but I'm not sure I could prove it and I'm confused as to why it only uses some of my connections when it should be selecting them randomly from the pool.

How do you correctly use connection pools in clsql?

Answer 1

This is the description of the :pool keyword argument of clsql:connect, taken from https://www.quicklisp.org/beta/UNOFFICIAL/docs/clsql/doc/connect.html:

A boolean flag. If T, acquire connection from a pool of open connections. If the pool is empty, a new connection is created. The default is NIL.

On https://quickref.common-lisp.net/clsql.html, it is said that:

If POOL is t the connection will be taken from the general pool, if POOL is a CONN-POOL object the connection will be taken from this pool.

I guess that means that when you do

(loop repeat 4 do (clsql:connect connection-string :pool t :database-type :mysql))

only the first call returns a new connection; the second, third and fourth calls to clsql:connect merely return the connection created on the first iteration, which was on the "general pool".

Though I didn't test it, I suppose that if you pass nil to the :pool argument, all four connections will actually be established.