SQL query does not work every time

[Mobility]

I've got a server that uses SQL to store data sent by client. There is a support for several simultaneous clients and I've implemented "lock" field to the database so that other clients won't be able to update the same record at the same time. So client uses this field when taking the record into edit state. Client can reset this field by sending one specific command which starts a function at the server side that does the trick. Problem is that the release command executed in the release function seem not to work every time because it gives following error occasionally: No query Unable to fetch row. How is this possible if the very same command is used every time?


The release function:

void serverthread::release_lock(QSslSocket *ptr_socket)
{
emit thread_trace_in_main("-> release_lock(QSslSocket *ptr_socket)", false); // this signal is just for tracing purposes

QSqlQuery query;

if (!db.open()) {
emit thread_trace_in_main("ERROR: "+DB_NAME, true);
ptr_socket->write("ERROR\n");
emit thread_errors();
return;
}

QString command="";

command= QString("UPDATE init SET locked='not_locked' WHERE locked='%1'").arg(client);
emit thread_trace_in_main(command, false);
query.prepare( command);
if (!query.exec()) {
emit thread_trace_in_main("UPDATE ERROR -> "+query.lastError().text(), false);
ptr_socket->write("ERROR\n");
}
command= QString("UPDATE client SET locked='not_locked' WHERE locked='%1'").arg(client);
emit thread_trace_in_main(command, false);
query.prepare( command);
if (!query.exec()) {
emit thread_trace_in_main("UPDATE ERROR -> "+query.lastError().text(), false);
ptr_socket->write("ERROR\n");
}
command= QString("UPDATE changes SET locked='not_locked' WHERE locked='%1'").arg(client);
emit thread_trace_in_main(command, false);
query.prepare( command);
if (!query.exec()) {
emit thread_trace_in_main("UPDATE ERROR -> "+query.lastError().text(), false);
ptr_socket->write("ERROR\n");
}

ptr_socket->write("READY\n");
}

Debug log showing the occasional problem:

( 09:02:13 ) -> void serverthread::wait_for_request()
( 09:02:14 ) REQ: RELEASE
( 09:02:14 ) -> release_lock(QSslSocket *ptr_socket)
( 09:02:14 ) UPDATE init SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:14 ) UPDATE client SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:14 ) UPDATE changes SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:14 ) -> void serverthread::wait_for_request()
( 09:02:39 ) REQ: RELEASE
( 09:02:39 ) -> release_lock(QSslSocket *ptr_socket)
( 09:02:39 ) UPDATE init SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:39 ) UPDATE ERROR -> No query Unable to fetch row
( 09:02:39 ) UPDATE client SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:39 ) UPDATE ERROR -> No query Unable to fetch row
( 09:02:39 ) UPDATE changes SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:39 ) UPDATE ERROR -> No query Unable to fetch row
( 09:02:39 ) -> void serverthread::wait_for_request()
( 09:02:40 ) REQ: RELEASE
( 09:02:40 ) -> release_lock(QSslSocket *ptr_socket)
( 09:02:40 ) UPDATE init SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:40 ) UPDATE client SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:40 ) UPDATE changes SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:40 ) -> void serverthread::wait_for_request()
( 09:02:42 ) REQ: RELEASE
( 09:02:42 ) -> release_lock(QSslSocket *ptr_socket)
( 09:02:42 ) UPDATE init SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:42 ) UPDATE client SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:42 ) UPDATE changes SET locked='not_locked' WHERE locked='Teppo Testaaja'
( 09:02:42 ) -> void serverthread::wait_for_request()

As you can see the second RELEASE request fails due to the SQL failure. When the failure happens it seems that all the three UPDATE commands fail. Any ideas? Failure rate is about 1 or 2 out of 10.

[Lesiok]

query should be defined AFTER opening DB connection and should looks like :
QSqlQuery query(db);

[wysota]

If you are using threads -- are you aware of the rules governing the use of SQL with threads in Qt?

[Mobility]

query should be defined AFTER opening DB connection and should looks like :
QSqlQuery query(db);

I found the problem after you mentioned opening DB connection. Actually the connection was already opened earlier in my initialization function so basically I was trying to open it again every time the release function was called. Instead of opening the connection my meaning was to check that the connection is fine. So with the following lines it seems to be working:


if (!db.isOpen()) {
emit thread_trace_in_main("ERROR: "+DB_NAME, true);
ptr_socket->write("ERROR\n");
emit thread_errors();
return;
}

wysota, I kind of get a feeling that I do not know principles of using SQL in threads. Could you light me up a bit?


Thanks again for your time!

[wysota]

If you have multiple threads, you can only use a particular database connection object in the same thread in which you created the connection. So if you want to use the database from three threads, you need three separate connections. Also you can't open the connection in thread A and use it in thread B (even if you're not using it in thread A anymore). Otherwise you'll start getting strange results, like losing rows or query results.

[Mobility]

Ok, that sounds logical. In my case I'm creating the database connection in a new thread whenever it is started so that should do the trick.

However I'm not sure if this is due to threads but for some reason if the client is closed (or process killed), the DB connection cannot be opened again. Wonder why?

Whenever the client is closed, I use db.close() but it seems that this is not enough. Or as said the other option is that I'm not making this correctly with threads. Btw, currently the DB is created like this:


#include "serverthread.h"
#include <QtGui>
#include <QtNetwork>
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

[wysota]

The database may fail to open if the killed process did not release a lock on the sqlite file. Are you also using QSqlDatabase::removeDatabase()?

[Lesiok]

Carefully read the description of the QSqlDatabase::addDatabase method. You have to use the second parameter.

[Mobility]

Thanks, I was not using the connection name parameter (neither QSqlDatabase::removeDatabase()) so each time client contacted again it tried to use the same default connection and it failed. Now I'm using clients username to differentiate the database connection name. Seems to work.

Cheers guys!