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
if (!db.open()) {
emit thread_trace_in_main("ERROR: "+DB_NAME, true);
ptr_socket->write("ERROR\n");
emit thread_errors();
return;
}
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");
}
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");
}
To copy to clipboard, switch view to plain text mode
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()
( 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()
To copy to clipboard, switch view to plain text mode
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.
Bookmarks