Connection Pooling – Best Library Choice for VM Deploys

connection-pooling

HickariCP docs say:

HikariCP relies heavily on accurate high-resolution timers for both
performance and reliability. It is imperative that your server is
synchronized with a time-source such as an NTP server. Especially if
your server is running within a virtual machine. Do not rely on
hypervisor settings to "synchronize" the clock of the virtual machine.
Configure time-source synchronization inside the virtual machine. If
you come asking for support on an issue that turns out to be caused by
lack time synchronization, you will be taunted publicly on Twitter.

Which other connection pooling libraries would you choose, which are less sensitive to virtual machine time drifts?

Best Answer

Any pool in an environment in which time can flow backwards or leap forwards, which it can in a virtual machine, is going to be subject to spurious failure when it does so.

Moreover, it is not just connection pools that are affected.

Any...

Timed waits
Timed polls from a concurrent collection
Object.wait() with a timeout
Thread.sleep() call
...
Anything in Java that requires the measurment of time is affected.

If you cannot nail down your clock drift, you will encounter unexplained application failures, occurring in your code and third-party libraries.

HikariCP (i.e.me) got sick of spending hours trouble-shooting a user's issue, reading logs and thread dumps, only to discover their clock had jumped backwards or forwards more than a minute.

User: "Why did I get a bunch of connection timeouts?!"

(several days of back-and-forth questions and examining logs later...)

HikariCP: "Well, you asked the pool to try for 30 seconds to give you a connection ... then your clock jumped forward 42 seconds and Java (and the OS) unparked all the threads as if time had really passed."

It is really simple to fix, just configure NTP inside your VM.

Related Solutions

Mysql Connection Pooling Similar To PostgreSQL’s PgBouncer

This started off as a comment but it's getting a bit long.

Perhaps you could provide a better description of what it is you actually want rather than simply say it's like something else.

If you want multiple clients to use the same connection, then it must be implemented in the DBMS, otherwise you have no session management. Further you need to advise how you intend dealing with issues of state spanning more than a single request/response (i.e. transactions).

It would also be helpful to know why you want it. MySQL has a very different setup cost and per-session overhead cost compared to PostgreSQL - why do you think it needs it, and why you think it would actually yeild any benefit.

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

Best Answer

Related Solutions

Mysql Connection Pooling Similar To PostgreSQL’s PgBouncer

Sql-server – connection pooling, transactions, nested transaction and rollback

Related Question