Mssql connection pooling issue. Multiple server round trips per query.

[tmrclark]

The problem

I was looking through OpenTelemtry traces of an application that uses Kysely with mssql and noticed that for every user created Kysley query, 3 SQL queries get sent to the server in succession.

The first is sent when a connection is acquired from the tarn connection pool. Every time this happens, tarn calls the provided validate method, which in this case sends a query.

The seconds is the user defined query.

The third happens with the connection is released back to the connection pool. The releaseConnection method calls the PRIVATE_RELEASE_METHOD, which calls the reset method defined in tedious, which in turn executes this query.

The 2 bookending queries create additional application latency by adding 2 round trips to the database server. I am assuming that this behavior is not expected and that there should only be one round trip to the database per user defined query. If I am wrong, please let me know.

How to reproduce

I created a demo repo with steps to reproduce this with trace logging and example traces.

Possible solution

I think the easiest and safest way to address this is to allow the user to configure the pool validation function and if the tedious reset method should be called when a connection is released. The default behavior can be the same as it is now, but this gives the opportunity to opt out.

[tmrclark]

@igalklebanov @koskimas Any input on ways this could be addressed beyond what I suggest? I hope to implement a fix when time allows some time this week or the next.