[odb-users] Question: Multithreading with odb::msql on Centos 7

[Jean Guegant]

Hi,

According to my understanding of the documentation, odb::database and 
hence odb::mssql::database are thread-safe objects.
We can share one instance of a a odb::mssql::database object (either a 
global variable, or an allocated instance on the heap carefully 
encapsulated by a shared_pointer) within multiple threads. A transaction 
on a thread will use a connection from a pool, and if none are available 
the call to db_->begin() will wait until a connection is free.

Yet it looks like I can't do two transactions from the same db objects 
on different threads, I end-up with the following stack trace:

#0  0x0000000000000000 in ?? ()
#1  0x00007ffff6fc0f33 in __connect_part_two 
(connection=connection at entry=0x7fffe4000a10) at SQLConnect.c:1891
#2  0x00007ffff6fc5d66 in SQLDriverConnect (hdbc=0x7fffe4000a10, 
hwnd=<optimized out>, conn_str_in=<optimized out>, 
len_conn_str_in=<optimized out>, conn_str_out=<optimized out>, 
conn_str_out_max=<optimized out>, ptr_conn_str_out=0x7ffff137d058, 
driver_completion=0) at SQLDriverConnect.c:1530
#3  0x00007ffff7231b2a in odb::mssql::connection::connection 
(this=0x7fffe40008f0, db=...) at connection.cxx:120
#4  0x00007ffff72321c9 in 
odb::mssql::connection_pool_factory::pooled_connection::pooled_connection (this=0x7fffe40008f0, 
db=...) at connection-factory.cxx:153
#5  0x00007ffff7232234 in odb::mssql::connection_pool_factory::create 
(this=0x69bd50) at connection-factory.cxx:50
#6  0x00007ffff723204c in odb::mssql::connection_pool_factory::connect 
(this=0x69bd50) at connection-factory.cxx:91
#7  0x00007ffff7233023 in odb::mssql::database::connection_ 
(this=<optimized out>) at database.cxx:488
#8  0x00007ffff724341a in connection (this=<optimized out>) at 
../../odb/mssql/database.ixx:18
#9  odb::mssql::transaction_impl::start (this=0x7fffe40008c0) at 
transaction-impl.cxx:41
#10 0x00007ffff746e312 in odb::transaction::reset (this=0x7ffff137d230, 
impl=0x7fffe40008c0, make_current=<optimized out>) at transaction.cxx:42
#11 0x0000000000459125 in odb::transaction::transaction 
(this=0x7ffff137d230, impl=0x7fffe40008c0, make_current=true) at 
/usr/local/include/odb/transaction.ixx:25
#12 0x000000000045e529 in Crasher::run (this=0x7ffff137fd30) at 
/home/jeangu/Coding/braveheart/src/test/cpp/bravecrash/main.cpp:61
#13 0x0000000000457554 in startCrasher (db=std::shared_ptr (count 5, 
weak 0) 0x69b9b0) at 
/home/jeangu/Coding/braveheart/src/test/cpp/bravecrash/main.cpp:96
#14 0x0000000000477dff in std::_Bind_simple<void 
(*(std::shared_ptr<odb::mssql::database>))(std::shared_ptr<odb::mssql::database>)>::_M_invoke<0ul>(std::_Index_tuple<0ul>) 
(this=0x698320) at /usr/include/c++/4.8.2/functional:1732
#15 0x0000000000477b91 in std::_Bind_simple<void 
(*(std::shared_ptr<odb::mssql::database>))(std::shared_ptr<odb::mssql::database>)>::operator()() 
(this=0x698320) at /usr/include/c++/4.8.2/functional:1720
#16 0x00000000004777c6 in std::thread::_Impl<std::_Bind_simple<void 
(*(std::shared_ptr<odb::mssql::database>))(std::shared_ptr<odb::mssql::database>)> 
 >::_M_run() (this=0x698308) at /usr/include/c++/4.8.2/thread:115
#17 0x00007ffff62f6220 in ?? () from /lib64/libstdc++.so.6
#18 0x00007ffff7bc6dc5 in start_thread () from /lib64/libpthread.so.0
#19 0x00007ffff5a5e21d in clone () from /lib64/libc.so.6

Using the following (slightly stripped) code:

======================================
// variablevaluemodel.h

#include <odb/core.hxx>
#include <odb/query.hxx>


#pragma db view query("EXEC astored_procedure (?)")
struct variable_value {
     std::string variable;
     std::string value;
};

// main.cpp
#include <atomic>
#include <thread>

#include <odb/mssql/database.hxx>
#include <variablevaluemodel-odb.hxx>

typedef odb::query<variable_value> query;
typedef odb::result<variable_value> result;
typedef std::shared_ptr<odb::mssql::database> shared_database;

class Crasher
{
public:
     Crasher(shared_database db) :
             db_(db)
     {

     }

     void run()
     {
         while (true) {

             odb::transaction t(db_->begin());

             result r(db_->query<variable_value>(query::_val("A 
parameter"));

             t.commit();
         }
     }

private:
     shared_database db_;
};

void startCrasher(shared_database db)
{
     Crasher c(db);
     c.run();
}

int main(int argc, char* argv[])
{

     auto db = shared_database(new odb::mssql::database("", "", 
"YourDB", "YourHost\\YourInstance",  1433));

     int nThread= std::thread::hardware_concurrency();

     std::vector<std::unique_ptr<std::thread>> workers;

     for (int i = 0; i < 2; ++i)
     {
         auto worker = std::unique_ptr<std::thread>(new 
std::thread(startCrasher, db));
         workers.push_back(std::move(worker));
     }

     for (auto& worker: workers) {
         worker->join();
     }

     return EXIT_SUCCESS;
}
======================================

My specs are the following:
Kernel:
Linux marsupilami 3.10.0-327.3.1.el7.x86_64 #1 SMP Wed Dec 9 14:09:15 
UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

Compiler:
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4)

Libs:
libodb-2.4.0
libodb-mssql-2.4.0
unixODBC-2.3.0
msodbcsql-11.0.2270.0

Same goes if I use a global database object.

Is it actually possible to do two transaction on the same db objects 
from different threads? Could the usage of a stored procedure be the 
problem? Is there any limitation for the mssql library only?

If I use a different database object per thread targeting the same 
database and doing the same transaction, could it induce any 
race-condition somewhere in the database or in the drivers, or in odb? 
Any performance impact?

Any help would be really helpful!

Sincerely,
Jean Guegant