QSqlDatabase multithreaded access read / write

andulek

Hello,

i have an application where i use multithreaded access to some database files,
currently my set-up has 2 *.db files which are shared among 5-6 different classes
and some instances of those classes are put into separate threads.

note: every instance of every class opens it's own unique connection to database file.
and closes it in destructor when class is destroyed.

everything works fine when there are only READ accesses, as running and keeping QSqlQuery puts a SHARED_LOCK
on a database, so multiple active SELECT queries can be run. that's fine.

but sometimes (not so often than read) some instance may write some data into DB, and if it has an active
SELECT query not finished it fails, as EXCLUSIVE lock cannot be acquired while having a SHARED_LOCK already in place.

goal is to minimize necessary changes to already working application, and to add checking if a query fails,
and retry it with delay few times, until throwing an error.
So i subclassed the QSqlDatabase, and have overridden "exec" method,
this way i don't have to change anything in original application, as exec method will do all the checking.
it will fail after 100 attempts or ~5s, if database is still locked during that period - then something is really wrong.

Can someone comment on what might be wrong with this approach, as it seems to be working OK, but maybe is not the best solution around?

ASqlDatabase::ASqlDatabase(const QSqlDatabase &parent) : QSqlDatabase(parent)
{
    d_database = parent;
}

QSqlQuery ASqlDatabase::exec(const QString & query)  //this method is called instead of original exec()
{

    retry = 0;
    QSqlQuery d_q;
    d_q = d_database.exec(query);  //tries to run the query normally

    while (d_database.lastError().nativeErrorCode() == "5" && retry < 100)  //checks for error and retry count
    {
        this->delay(50); 
        retry++;

        d_database.lastError().setType(QSqlError::NoError);  //clears the error flag
        d_q = d_database.exec(query); //and retries one more time
    }

    return d_q;  //after that just return QSqlQuery "as is" if it failed it is empty, if succeeded - not.

}

void ASqlDatabase::delay( int millisecondsToWait )  //delay routine
{
    QTime dieTime = QTime::currentTime().addMSecs( millisecondsToWait );
    while( QTime::currentTime() < dieTime )
    {
        QCoreApplication::processEvents( QEventLoop::AllEvents, 100 );
    }
}

jsulm

Shouldn't you just use transactions? This way it is up to the database server to make sure all that read/write queries work in parallel.

andulek

What will happen in this scenario:

A Thread executes "BEGIN TRANSACTION"
Same Thread runs "INSERT OR UPDATE..."
Another Thread Executes "SELECT...." and puts a SHARED lock
First Thread executes "COMMIT"

A) Will "SELECT" succeed during active transaction beeing in place?
B) will commit fail or succeed as SELEC is beeing run?