CODEWITHSUNDEEP
c++qt
Apin
Apin

Reputation: 2668

QSqlDatabase pool on the QThreadPool

I am doing a project that uses QSqlDatabase. And now I am using QtConcurrent (QFuture) to execute any SQL command.

Right now, every new command that run using QFuture create new QSqlDatabase connection to my Mysql server. I believe any new connection to Mysql server will have penalty about the handshake. So I have a plan to make a pool QSqlDatabase and from the documentation QSqlDatabase only able to be used by the thread who made it.

So, my idea is to make a QMap pools, which the int is the thread id and the QString is the connection name. So when I want to start a thread from threadpool using qfuture, just get the connection name from the QMap pool and get the QSqlDatabase (which this QSqlDatabase is already connected to server).

Sample code :

//this is static variable
QMap<int, QString> pool;
.....

//At the beginning of sql command to execute
if(pool.contains((int)QThread::currentThreadId()) {
    db = QSqlDatabase::database(pool[(int)QThread::currentThreadId()]);
} else {
    QString key = "someunique" + QString::number((int)QThread::currentThreadId());
    db = QSqlDatabase::add(key)
    ... // some Qsql connection code
    pool.insert((int)QThread::currentThreadId(), key);
}

Maybe my code above not work, but what I want to ask : is my idea will work? Or do I missed something about the QSqlDatabase?

Upvotes: 2

Views: 1072

Answers (1)

Kuba hasn&#39;t forgotten Monica
Kuba hasn&#39;t forgotten Monica

Reputation: 98545

First, an idea that won't work: adding the connection as a QObject property of the thread itself. It won't work because the QObject property system is not thread-safe.

A simple idea that will work is to store the database connection in thread local storage, using QThreadStorage. It'll then be automatically disposed when the thread from the pool goes away:

QThreadStorage<QSqlDatabase> connections;

QSqlDatabase newConnection();

QSqlDatabase getConnection() {
  auto & connection = connections.localData();
  if (! connection.isValid())
    connection = newConnection();
  return connection;
}

Your idea will work, as long as you serialize the concurrent access to the pool. You also need to ensure that connections are cleaned up when the threads finish. You also can use a QThread pointer directly, instead of using the id. There's no need to reference connections by a string key, you can hold them directly since they are values. A QSqlDatabase is a handle, just like a file handle.

QReadWriteLock poolLock;
QMap<QThread*, QSqlDatabase> pool;

struct ConnectionDropper : public QObject {
  void drop() {
    QWriteLocker writeLock{&poolLock};
    pool.remove(qobject_cast<QThread*>(sender()));
  }
}
Q_GLOBAL_STATIC(Dropper, dropper);

QSqlDatabase newConnection();

QSqlDatabase getConnection() {
  auto thread = QThread::currentThread();
  QReadLocker readLock{&poolLock};
  auto it = std::find(pool.begin(), pool.end(), thread);
  if (it != pool.end())
    return it.value();
  readLock.unlock();
  // connecting can take some time, so don't lock the pool while it happens
  auto conn = newConnection();
  // Unique connections to functors are not implemented, thus we need an object.
  QObject::connect(thread, &QThread::finished, &*dropper,
    &ConnectionDropper::drop, Qt::DirectConnection | Qt::UniqueConnection);
  QWriteLocker writeLock{&poolLock};
  pool.insert(thread, conn);
  return conn;
}

Upvotes: 1

Related Questions