drogon: Async transaction execution failure

Describe the bug With DbFastClient, apply query with newTransactionAsync() keeps reporting error.

To Reproduce

...
  const uint64_t id = 1;
  const ushort age = 16;
  auto clientPtr = drogon::app().getFastDbClient("FOO");

  clientPtr->newTransactionAsync([id=std::move(id),age=std::move(age)](const std::shared_ptr<Transaction> &transPtr) {
      assert(transPtr);
      transPtr->execSqlAsync( "select * from foo where id=$1",
        [=](const Result &r) {
            if ( 0 < r.size() )
            {
                *transPtr << "update foo set age=$1 where id=$2"
                          << age          //Does it required to be std::string ?
                          << r[0]["id"].as<uint64_t>()
                          >> [](const Result &r)
                             {
                                LOG_INFO << "Updated!";
                                //... do something about the task;
                             }
                          >> [](const DrogonDbException &e)
                             {
                                LOG_ERROR << "err:" << e.base().what();
                             };
            }
            else
            {
                LOG_ERROR << "No new tasks found!";
            }
        },
        [](const DrogonDbException &e) {
            LOG_ERROR << "err:" << e.base().what();
        },
      id);   //Does it required to be std::string ?
    });

  return;
...

Sometimes crashes but always returns the following

20200313 04:24:41.332853 UTC 16597 ERROR error - MysqlConnection.cc:427
20200313 04:24:41.332891 UTC 16597 ERROR Error(0) [00000] "" - MysqlConnection.cc:443
20200313 04:24:41.349522 UTC 16597 ERROR Error occurred in transaction begin - TransactionImpl.cc:287
20200313 04:24:41.349666 UTC 16597 ERROR err:The transaction has been rolled back - api_v0_foo.cc:163

and sometimes with these

20200313 04:35:36.383567 UTC 16676 ERROR error:1 status:1 - MysqlConnection.cc:258
20200313 04:35:36.383629 UTC 16676 ERROR Error(1054) [42S22] "Unknown column '$1' in 'where clause'" - MysqlConnection.cc:443
20200313 04:35:36.414844 UTC 16676 ERROR err:Unknown column '$1' in 'where clause' - api_v0_foo.cc:163
20200313 04:35:36.415163 UTC 16676 DEBUG [operator()] Transaction roll back! - TransactionImpl.cc:159

Expected behavior The record with id=1 in foo, the age field set to 16.

Additional context Am I misunderstand the usage? Actually, I was trying to reference your example with the for update but failed with blocking call with newTransaction(). Then, I amended it with newTransactionAsync() but failed again even with the for update removed.

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 20 (20 by maintainers)

Most upvoted comments

True, I briefly read something on the internet. Even worse, I do need to study the syntax/operators of sql as well. Thanks again @an-tao

ihmc3jn09hk on Mar 26, 2020

with ORM, you should, create a transaction–transPtr, then:

mapper<Foo>(transPtr);
mapper.forUpdate().find(.....,[](Foo a){
     a.getAmount()+=1;
    mapper.update(a);
});

(The above code is just a hint and may can not be compiled.) this can approach the aim, but it takes two database requests, so the performance is definitely not as good as raw SQL.

an-tao on Mar 26, 2020

For these sentences:

<< select * from foo where id in ("giver","receiver") for update
<< update foo set amount = amount - 2 where id=giver
<< update foo set amount = amount + 2 where id=reciever

you should use:

<< begin
<< select * from foo where id in ("giver","receiver") for update 
<< update foo set amount = amount - 2 where id=giver
<< update foo set amount = amount + 2 where id=reciever
<< commit

that’s to say all the three SQL sentence should be in one transaction.

an-tao on Mar 26, 2020

    **_dbClient << "update foo set amount = (amount + 1) where id='a'" >>
        [callbackPtr](const Result &r) {
              auto resp = HttpResponse::newHttpResponse();
              resp->setBody("good");
              (*callbackPtr)(resp);
        } >>
        [callbackPtr](const drogon::orm::DrogonDbException &e) {
        LOG_ERROR << "error: " << e.base().what();
        auto resp = HttpResponse::newHttpResponse();
        resp->setStatusCode(k500InternalServerError);
        resp->setBody("Bad");
        (*callbackPtr)(resp);
    };

then you could use drogon_ctl press -c100 -t4 -n1000 http://localhost:port/path to test it.

an-tao on Mar 26, 2020

If you use mysql, you should the ‘?’ as placeholders for parameters. mysql and postgresql have different placeholders, making them unified would add extra cost.

an-tao on Mar 24, 2020