beginTransaction/rollback/committ do not work

[Michetan]

Using instead "database->executeQuery("BEGIN;", {} );" / "database->executeQuery("ROLLBACK;", {} );" / "database->executeQuery("COMMIT;", {} );" the behavior is as expected.

[lganzzzo]

Hello @Michetan ,

Thanks for the issue!

What version of SQLite do you use? Also, please post the code snippet for us to reproduce the issue.

Regards, Leonid

[Michetan]

Hi @lganzzzo , I'm using SQLite 3.37.0 and here is a snippet of the code. I want to write all the records that belong to an area, before making the area available to the concurrent queries, so I need to use the begin/commit feature of the DB.

        // BUG! auto dbTransaction = database->beginTransaction();
        m_database->executeQuery("BEGIN;", {} /* empty params map */);

        deleteArea(companyId, projectId, dto->id);

        int sequence = 1; // Area has vertexes [1 ..]

        for (auto vertex : *dto->vertices)
        {
            auto recordDto = ObjectRecordDto::createShared();

            recordDto->companyId = companyId;
            recordDto->projectId = projectId;
            recordDto->objectId = dto->id;
            recordDto->name = dto->name;
            recordDto->vertexSeq = sequence++;
            recordDto->X = vertex->x;
            recordDto->Y = vertex->y;
            recordDto->Z = vertex->z;

            /* Write dto in the database */
            auto dbInsertResult = m_database->createObject(recordDto);
            OATPP_ASSERT_HTTP(dbInsertResult->isSuccess(), Status::CODE_500, dbInsertResult->getErrorMessage());
        }

        // BUG! dbTransaction.commit();
        m_database->executeQuery("COMMIT;", {} /* empty params map */);

[lganzzzo]

Interesting,

In your example you do not explicitly specify DB connection for your DB calls. It means that if you are using more one connection in the DB connection pool your snippet should not work.

Also please note, that in order for transactions work you have to make DB calls on transaction connection:

{
  auto transaction = client.beginTransaction();

  auto dbInsertResult = m_dbClient->createObject(recordDto, transaction.getConnection() /* transaction connection */);

  transaction.commit();
}

---

In any case I have to double-check. I guess adding transaction test to CI is a good idea...

[Michetan]

I'm not sure if this provides more info to you about the connection but actually the "m_database" has been injected in the hpp header in this way:

OATPP_COMPONENT(std::shared_ptr<GeocodingDb>, m_database); // Inject database component

And previously created in this way:

OATPP_CREATE_COMPONENT(std::shared_ptr, geocodingDb)([] {

  /* Get database ConnectionProvider component */
  OATPP_COMPONENT(std::shared_ptr<oatpp::provider::Provider<OATPP_DB_ORM::Connection>>, connectionProvider);

  /* Create database-specific Executor */
  auto executor = std::make_shared<OATPP_DB_ORM::Executor>(connectionProvider);

  auto migrationPath = Poco::Util::Application::Application::instance().config().getString("application.dir", "./") + "sql/";

  auto database = std::make_shared<GeocodingDb>(executor, migrationPath);

  database->executeQuery("PRAGMA journal_mode = WAL;", {} /* empty params map */);

  /* Create MyClient database client */
  return database;

}());

Note that I inserted here the WAL journaling mode.

Thank you, Leonid.

Michele.

[Michetan]

The solution you suggested works fine, thank you.

{ auto transaction = client.beginTransaction();

auto dbInsertResult = m_dbClient->createObject(recordDto, transaction.getConnection() / transaction connection /);

transaction.commit(); }