POCO SQLite timeout handling

Tips and tricks for POCO C++ Libraries users.
DomX
Posts: 1
Joined: 30 Mar 2017, 09:07

POCO SQLite timeout handling

Postby DomX » 26 May 2017, 09:15

Hello, my question is at https://stackoverflow.com/questions/44182395/poco-sqlite-timeout-handling, so you can answer there on the Stackoverflow ;-)

However, my problem to solve:

I am working on an application (based on POCO framework) using SQLite database. There are more threads accessing the database (writers, readers), it means the database has to be locked during some operations (writes). My problem is, the DB is locked, however, it seems, the lock is not internally handled as I would expect - the

Code: Select all

Poco::Data::Statement::execute()


throws a timeout exception, even the timeout has not been reached out. I would expect, the execute statement call is blocking, until the timeout reaches out or DB is unlocked.

Bellow is my testing code. In CleanUpData() method is a boolean value "result" set to false to overcome the thrown error and try to execute the DB statement again, until the DB is unlocked.

The question is. How to write the code without the while cycle? Is it possible using POCO DB wrapper?
Thanks a lot for hints!

Code: Select all

#include <Poco/Data/Session.h>
#include <Poco/Data/SessionPool.h>
#include <Poco/Data/SQLite/Connector.h>

#include <memory>
#include <string>
#include <vector>
#include <chrono>
#include <thread>

class DataStorage
{
public:
    DataStorage();
    ~DataStorage();
    void SaveData() const;
    void CleanUpData() const;


private:
    int connectionTimeout;
    const std::string connectionString;
    const std::string dbSchemeTd =
        "\nCREATE TABLE IF NOT EXISTS `test_data` ("
        "\n\t`id` INTEGER NOT NULL,"
        "\n\t`string` TEXT DEFAULT NULL,"
        "\n\t`rkey` INTEGER NOT NULL,"
        "\n\tPRIMARY KEY(id),"
        "\n\tFOREIGN KEY(`rkey`) REFERENCES ref_keys(rkey) ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE);"
        "\nCREATE INDEX IF NOT EXISTS idx_key ON test_data(id);"
        "\nCREATE TABLE IF NOT EXISTS `ref_keys`"
        "\n\t(`rkey` INTEGER NOT NULL UNIQUE, PRIMARY KEY(`rkey`));";
};

DataStorage::DataStorage()
    : connectionTimeout(30)
    , connectionString("D:/SRC/_sandbox/data.db3")
{
    Poco::Data::SQLite::Connector::registerConnector();

    try
    {
        Poco::Data::Session db(Poco::Data::SQLite::Connector::KEY, connectionString);
        db.setConnectionTimeout(connectionTimeout);
        std::cout << "DB DataStorage Connection timeout: " << db.getConnectionTimeout() / 1000 << std::endl;
        db << "PRAGMA foreign_keys = ON;", Poco::Data::Keywords::now;
        db << dbSchemeTd, Poco::Data::Keywords::now;
    }
    catch (const std::exception& e)
    {
        std::string s = std::string("ERROR: Database initialization failed: ") + e.what();
        std::cerr << s << std::endl;
    }
}

DataStorage::~DataStorage()
{
}

void DataStorage::SaveData() const
{
    try
    {
        Poco::Data::Session db(Poco::Data::SQLite::Connector::KEY, connectionString);
        db.setConnectionTimeout(connectionTimeout);
        std::cout << "DB SaveData Connection timeout: " << db.getConnectionTimeout() / 1000 << std::endl;
        db << "PRAGMA foreign_keys = ON;", Poco::Data::Keywords::now;
        db.begin();

        Poco::Data::Statement stm(db);
        for (size_t i = 0; i < 10; ++i)
        {
            stm <<
                "INSERT INTO ref_keys"
                " (rkey) VALUES(?);",
                Poco::Data::Keywords::bind(i);

            stm <<
                "INSERT INTO test_data"
                " (id, string, rkey) VALUES(?, ?, ?);",
                Poco::Data::Keywords::bind(i),
                Poco::Data::Keywords::bind("abc" + std::to_string(i)),
                Poco::Data::Keywords::bind(i);
        }
        stm.execute(); // acquire the DB lock
        std::this_thread::sleep_for(std::chrono::seconds(10));
        db.commit();
    }
    catch (const std::exception& e)
    {
        std::cout << "DB error: " << e.what() << std::endl;
    }
}

void DataStorage::CleanUpData() const
{
    bool result = false;
    while (!result)
    {
        auto start = std::chrono::steady_clock::now();

        try
        {
            std::string sqlQuery =
                "SELECT id, string, rkey"
                " FROM test_data"
                " ORDER BY test_data.id;";

            Poco::Data::Session db(Poco::Data::SQLite::Connector::KEY, connectionString);
            db.setConnectionTimeout(connectionTimeout);
            std::cout << "DB CleanUpData Connection timeout: " << db.getConnectionTimeout() / 1000 << std::endl;

            db << "PRAGMA foreign_keys = ON;", Poco::Data::Keywords::now;
            db.begin();

            Poco::Data::Statement stm(db);
            std::vector<Poco::Tuple<int, std::string, int>> data;
            stm <<
                "SELECT id, string, rkey"
                " FROM test_data"
                " ORDER BY test_data.id;"
                " DELETE FROM ref_keys;",
                Poco::Data::Keywords::into(data);

            stm << "DELETE FROM ref_keys;";
            stm.execute(); // from my point of view, should be blocking, until the the DB lock is released / timeout reached out

            db.commit();

            for (auto& dataElement : data)
            {
                std::cout << "data: " << dataElement.get<0>() << ", " << dataElement.get<1>() << ", " << dataElement.get<2>() << std::endl;
            }

            std::cout << "Maintained DB and file storage.\n";
            result = true;
        }
        catch (const std::exception& e)
        {
            auto end = std::chrono::steady_clock::now();
            auto diff = end - start;

            std::cerr << "duration: " << std::chrono::duration_cast<std::chrono::milliseconds>(diff).count() << std::endl;
            std::cerr << "CleanUpData exception: " << e.what() << std::endl;
            result = false;
        }
    }
}

void main()
{
    DataStorage dataStorage;

    std::thread t1(&DataStorage::SaveData, &dataStorage);
    std::this_thread::sleep_for(std::chrono::seconds(5));
    std::thread t2(&DataStorage::CleanUpData, &dataStorage);

    t1.join();
    t2.join();
}

Return to “Tips & Tricks”

Who is online

Users browsing this forum: No registered users and 2 guests

cron