Connection pool

Clear uses connection pooling to allow multiple transactions to run in parallel over multiple fibers.

By default, each connection is fetched from the connection pool for each fibers. Let's see this example:

begin
Clear::SQL.execute("CREATE TABLE tests (id serial PRIMARY KEY)")
spawn do
# Clear automatically create a connection nº1
Clear::SQL.transaction do
Clear::SQL.insert("tests", {id: 1}).execute
sleep 0.2 #< Wait and do not commit the transaction for now
end
end
@@count = -1
# Spawn a new fiber
spawn do
sleep 0.1 #Wait a bit, to ensure than the first connection is inside a transaction
# execute in connection nº2
@@count = Clear::SQL.select.from("tests").count
end
sleep 0.3 # Let the 2 fiber time to finish...
# The count is zero, because: it has been setup by the second fiber, which
# called AFTER the insert but BEFORE the commit on the connection nº1
@@count.should eq 0
# Now the transaction of connection nº1 is over, count should be 1
count = Clear::SQL.select.from("tests").count
count.should eq 1
ensure
Clear::SQL.execute("DROP TABLE tests;")
end

Each call to SQL is using a new connection, from the free connection pool; if a transaction is in progress, each call will use the same connection during the whole transaction.

If all the connections are busy, the fiber will wait indefinitely until a new connection is freed.

There is currently no way to force a fiber to use the same connection has another fiber. This may be improved in the future.