Threads and database connection

In my program I create several thread. Each thread holds a database connection, it means, each thread instantiates the "database" class which allows them to work with the database.

When the first thread is "run", then all queries to the database work fine. When the second thread in "run", then all queries to the database work too, but the first thread cannot use the database anymore.

1. Is this a correct approach of having each thread a connection to the database, or should I have a general instance of a "database" and have each thread use it ?

2. If it is correct to have each thread with an instance of the "database", what could be wrong ?

Can we see some code?

The class that initializes the "database" class

Code:

---

void Client::run(){
  cout << "Client thread has enter the thread execution\n";
  message = new Message(this);
  database = new Database(this);
  message->knowDatabase(database);
  tcpSocket = new QTcpSocket();
  if(!tcpSocket->setSocketDescriptor(_socketDescriptor))
    cout << "Error - setting socket descriptor\n";
  connect(tcpSocket, SIGNAL(readyRead()), this, SLOT(readMessage()));
  connect(tcpSocket, SIGNAL(disconnected()), this, SLOT(clientDisconnected()));
  exec();
}

---

The database class:

Code:

---

Database::Database(Client *client){
  cout << "Initializing database connection\n";
  _client = client;
  db = QSqlDatabase::addDatabase("QMYSQL3");
  db.setHostName("localhost");
  db.setDatabaseName("infospeed");
  db.setUserName("santiago");
  db.setPassword("santipass");
}
 
void Database::connectToDatabase(){
  bool ok = db.open();
  if(ok)
    cout << "Database connection successful\n";
  else
    cout << "Error connecting to the database\n";
}
 
 
QString Database::getName(QString userId, QString password){
  connectToDatabase();
  QString myQuery("SELECT name, lastname FROM user WHERE id=\"");
  myQuery += userId;
  myQuery += "\" and password=\"";
  myQuery += password;
  myQuery += "\"";
  QSqlQuery query(myQuery, db);
  query.next();
  QString name = query.value(0).toString();
  QString lastName = query.value(1).toString();
  QString userName = name + " " + lastName;
  cout << "User name " << userName.toStdString() << endl;
  disconnectFromDatabase();
  return userName;
}

---

I hope this helps to solve the issue !!!

Can't you use a single connection to the database for all threads?

yes, I can create just one instance for the database and use it from all threads.

Is this a better approach than using one connection per thread ?

No (the docs even specify you can't do it), but if it works then why not?

What is the content of disconnectFromDatabase()?

And just to make sure - are we talking Qt4 here?

Quote:

---

Originally Posted by probine

yes, I can create just one instance for the database and use it from all threads.

Is this a better approach than using one connection per thread ?

---

Well, actually you are using only one instance from all threads. Since you are sharing the connection.


If you would like to use one connection per thread you have to use a construct such as this (pseudocode, not compiled):

Code:

---

QSqlDatabase Database::getDatabase(){
  QString connectionName = QLaint1String("infospeed@");
  connectionName += QString::number(QThread::currentThread());
  if (QSqlDatabase::contains(connectionName, true)) {
      return QSqlDatabase::database(connectionName);
  } else {
      QSqlDatabase db = QSqlDatabase::addDatabase(QLatin1String("QMYSQL3"), connectionName);
      db.setHostName(QLatin1String("localhost"));
      db.setDatabaseName(QLatin1String("infospeed"));
      db.setUserName(QLatin1String("santiago"));
      db.setPassword("santipass");
      bool ok = db.open();
      if(ok)
           cout << "Database connection successful\n";
      else
           cout << "Error connecting to the database\n";
      return db;
   }
}

---

I am not quite sure if I understood.

I am not using only one instance...

I am not sharing the connection...

Each thread holds its own connection to the database... this connection is initialized in the "run" part of each thread, so the database connection should be unique for each thread.

The problem as I see it is in this line

Code:

---

db = QSqlDatabase::addDatabase("QMYSQL3");

---

addDatabase is a static function, which implies that is does not care in which Database object it was called; and you do not specify a specific second argument, the connectionName. The defaultvalue for this argument is "QLatin1String( defaultConnection )".

This means all your database connections will have the same connectionName. Keep this in mind when you read the following warning, copied straigt from the docs:

Quote:

---

Warning: If you add a database with the same name as an existing database, the new database will replace the old one. This will happen automatically if you call this function more than once without specifying connectionName.

---

The way I read that is: as soon as your second thread initializes its database object, it will create a new default connection and delete the old one. The SqlDatabase object in the first thread will point to the old now stale connection and should thus cease to work. Which is rather consistent with your symptoms.

Changing the creation of database objects to the above mentioned method (meaning assuring that each connection/thread gets their own connectionName) should work. (Hopefully, not fully tested as I said ;-)

You would typically handle this situation using some sort of database connection pool, which can manage the lifetime of your database connections for you.