mercredi 26 août 2015

Committing sqlite query takes ages when another query is waiting

Take the following C++ example:

#include <iostream>
#include <fstream>
#include <unistd.h>

#include <signal.h>
#include <thread>

#include "sqlite/sqlite.cpp"
#include "time/time.cpp"

microtimestamp start;
microtimestamp mid;
microtimestamp end;

void t1()
{
    sqlite my_sqlite("mydb2.db");

    my_sqlite.begin();
    my_sqlite.query("delete from x").step();

    usleep(10E6);

    start = microtime();

    my_sqlite.commit();

    mid = microtime();
}

void t2()
{
    sqlite my_sqlite("mydb2.db");

    my_sqlite.query("insert into x values(5)").step();
    end = microtime();
}

int main()
{
    std :: thread th1(t1);

    usleep(1.0E5);

    std :: thread th2(t2);

    th1.join();
    th2.join();

    std :: cout << "To commit: " << mid - start << std :: endl; 
    std :: cout << "To do the query: " << end - mid << std :: endl;
}

Here sqlite is a library that I developed just to wrap the calls to the sqlite3 interface, but it just forwards the calls to that interface.

I have two threads. The first starts slightly before the second. It creates a connection to a database, then begins a transaction. Then it sleeps for ten seconds. In the meantime, the second thread starts, opens a connection to the same database, and tries to execute a query.

Without any additional configuration, this results in a SQLITE_BUSY error returned by sqlite3_step. I read that I can, however, set a waiting timeout with sqlite3_busy_timeout. I set the timeout on the second thread database to a very high value. Now, no error is return and the second thread actually waits for the first thread to release the database before writing.

However, I have a timing problem. As you can see, I am taking three microtimestamps: before starting the commit on the first thread, after completing the commit on the first thread, and after the query is completed on the second thread.

The results are as follows:

To commit: 746329
To do the query: 1836

It looks like it takes forever to commit the transaction when some other thread is waiting on a SQLITE_BUSY to get to do its own transaction. Of course, if I disable the second thread waiting to acquire the database the first thread commits in a millisecond.

I tried doing the same thing using the sqlite3 terminal interface instead of using the first thread. My program successfully waits until the database is released, but when I first try to commit on my terminal I get a database locked error. If I wait a second or so, then try again to commit, the commit is successful, the database is released and the query gets immediately completed on my program that was waiting.

What is going on? How comes that it takes so long to commit a transaction when another thread is waiting to make a transaction on the db? And why does it yield a database locked error on the thread that locked the database? Maybe I am missing something...?

Aucun commentaire:

Enregistrer un commentaire