QSqlDatabase pool on the QThreadPool

Question

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?

Paul-Sebastian Manole · Accepted Answer · 2017-11-21 04:09:47Z

1

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;
}

edited Nov 21, 2017 at 4:09

Paul-Sebastian Manole

2,7481 gold badge37 silver badges38 bronze badges

answered May 18, 2017 at 12:53

Kuba hasn't forgotten Monica

99.1k17 gold badges168 silver badges333 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Apin Over a year ago

Hi @Kuba, Btw which one do you prefer the best? using QThreadStorage or using pool (bottom one)?

Kuba hasn't forgotten Monica Over a year ago

@Apin I always prefer when less is more, so the answer should be obvious then :) I only included the pool solution to demonstrate that it's trickier to get right.

Collectives™ on Stack Overflow

QSqlDatabase pool on the QThreadPool

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related