We were so far relying on the fact that in ACF (though only tested with ACF 8) any subsequent cfquery to the same datasource from within the same request would re-use the same database connection from the pool and there would be no intermittend use of that connection from other concurrent requests, i.e. multiple requests to same same datasource in the same request would always use the same connection from the pool and only give up that connection after onRequestEnd was completed.
In our use case, we use PostgreSQLs SET SESSION myvar=value;/SELECT current_setting(myvar); to implement a fairly transparent trigger-based auditing/versioning-systems on a fair number of tables. We would issue a statement that would set a couple of such variables in the database backend in onRequestStart and the auditing/versioning triggers run by subsequent DML queries would rely on these values being present and untampered with within a request.
With Lucee I have build a test case that shows a different behaviour under concurrency than ACF, i.e. there may be a different connection from the pool used in a subsequent query inside the very same request.
<!---
# PostgreSQL functions:
CREATE OR REPLACE FUNCTION public.get_setting(
IN param text,
OUT value text)
RETURNS text AS
$BODY$
BEGIN
SELECT current_setting(param) INTO value;
EXCEPTION
WHEN UNDEFINED_OBJECT THEN
value := NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION public.set_setting(
_varname text,
_varval text)
RETURNS void AS
$BODY$
BEGIN
EXECUTE 'SET SESSION ' || quote_ident(_varname) || ' = ' || quote_literal(_varval);
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
--->
<cfscript>
variables.strDatasource = 'mydatasource';
variables.strSessionVar = 'myscope.foo';
variables.strSessionVal = createUUID();
</cfscript>
<cfquery name="variables.setVar" datasource="#variables.strDatasource#">
SELECT set_setting(<cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.strSessionVar#" />, <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.strSessionVal#"/>);
</cfquery>
<cfquery name="variables.getVar" datasource="#variables.strDatasource#">
SELECT get_setting(<cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.strSessionVar#" />) AS myVal;
</cfquery>
<cfif variables.getVar.Recordcount eq 0 || compare(variables.getVar.myVal,variables.strSessionVal) neq 0>
<cfscript>
variables.strMessage = "val not as expected; RecordCount: " & variables.getVar.Recordcount;
if (variables.getVar.Recordcount) {
variables.strMessage &= ' expected ' & variables.strSessionVal & ' got ' & variables.getVar.myVal;
}
</cfscript>
<cfthrow message="problem" detail="#variables.strMessage#" />
<cfelse>
ok
</cfif>
I tested that with ApacheBench:
ab -c 40 -n 1000 "http://localhost/test.cfm"
With my dev server, I get something around 40-70 failed requests with mostly a different value returned from the second query for the 1000 requests issued with a concurrency of 40.
The only workaround that assures the same connection being used for all queries to the same datasource in a sequence of queries is wrapping the whole sequence with a cftransaction.
That is however not very practical, as such data manipulating queries might be in very different parts of the code. Some of that may even already use cftransaction for other purposes. Reworking all the code to pass in these variables explicitly (in our use case a session-id and a user-id) would be extremely painful. The beauty of the current solution is, that the developers writing queries on those tables need not concern themselves at all with the audit/versioning system and thus cannot forget to implement it or make some sort of mistake in the implementation for a part of the code.
Is there some way, maybe some special configuration on the datasource, that would force Lucee to assign a connection from the pool exclusively to a request and only free the connection once the request has run? Without such “sticky” database connections for a request, a database feature such as PostgreSQL SET SESSION … is fairly useless.
I created a new ticket in the issue tracker.