Rationalize the usage of `Connection` in the JDBC appender

[ppkarwasz]

The JDBC appender takes a very conservative approach to SQL connection sharing:

1. as most appenders, each append call is synchronized,
2. each event (or batch of events) is logged using a different Connection object, which is closed at the end of the call,
3. to prevent the high performance hit from creating many Connections, users are advised to use a connection pool like DBCP2.

This approach has many disadvantages:

• it does not profit from the connection pool, since at any time at most one connection will be borrowed from the pool,
• on the other hand users that use DriverManager get a performance hit, even if 1. ensures that no Connection object will be used concurrently on multiple threads.

[garydgregory]

We already do sharing through the log4j-jdbc-dbcp2 module. The default behavior should be as simple as possible IMO. Or, we can bake in the use of Commons DBCP and always offer pooling through that. I don't think we should provide a third behavior. Either you get the stock KISS behavior or you get pooling through Commons DBCP.

To muddle the waters further, some JDBC Driver do their own connection pooling; I was the co-author of such a Driver.

[ppkarwasz]

We already do sharing through the log4j-jdbc-dbcp2 module. The default behavior should be as simple as possible IMO.

I am OK with using one Connection per log event or batch, but in that case we should allow multiple Connections to be used by multiple threads. Currently all database appenders have the same limitations that OutputStream appenders, with synchronization in AbstractDatabaseManager.write():

logging-log4j2/log4j-core/src/main/java/org/apache/logging/log4j/core/appender/db/AbstractDatabaseManager.java

Lines 297 to 303 in d229eda

	public final synchronized void write(final LogEvent event, final Serializable serializable) {
	if (isBuffered()) {
	buffer(event);
	} else {
	writeThrough(event, serializable);
	}
	}

Currently this limitation is only due to the fact that Connection and PreparedStatement created in the connectAndStart() method are passed to writeInternal through instance fields of JdbcDatabaseManager:

logging-log4j2/log4j-core/src/main/java/org/apache/logging/log4j/core/appender/db/AbstractDatabaseManager.java

Lines 325 to 332 in d229eda

	protected void writeThrough(final LogEvent event, final Serializable serializable) {
	this.connectAndStart();
	try {
	this.writeInternal(event, serializable);
	} finally {
	this.commitAndClose();
	}
	}

We can easily:

• Use ThreadLocal in 2.x.
• Change the API in 3.x to pass that context data as method parameters.