I am implementing database schema based multitenancy strategy. I use PostgreSQL for db, HikariCP for pool, and it's a SpringBoot application. I will need to set the schema after getting the connection, like the code below. (this.ds is a hikari pool)
@Override public Connection getConnection() throws SQLException {
Connection connection = this.ds.getConnection();
// reset target schema to...
String schema = determineSchema();
connection.createStatement().execute("SET search_path to "+schema);
return connection;
}
So my question is, say two API calls with tenant1 and tenant2 comes nearly the same time visiting getConnection() function and set schema. I am wondering how will the code handle it? Ideally, I would like to see Hikari will give them 2 different connections and there would be no conflict in schema set. But is it the real case? Do we need a read/write lock here? Thanks ahead.
A connection pool keeps track of what connections it has checked out; once a connection is handed off to a thread it doesn’t give that same connection out again until the connection is handed back. If a connection isn’t handed back then you have a connection leak. The leak happens because the pool hasn’t gotten notified that the user of the connection is finished with it so it refuses to hand it out. It errs on the side of not handing out a connection if there is any doubt about if the connection is in use.
In the event the pool did mess up and hand out the same connection twice there is no locking you can do that would fix the problem, you would end up with one of the two threads writing to the wrong schema (unless both just happened to ask for the same schema, of course). If Hikari made this error it wouldn’t be useable. Fortunately connection pools don’t seem to have a problem getting this right.