[odb-users] Timeout and Concurrency issues

[Boris Kolpackov]

Hi Erez,

Erez Pics <picserez at gmail.com> writes:

> I remember your comment regarding poor concurrent model on SQLite engine,
> should we consider using more then one connection for better multi
> threaded performance ? [or consider connection pool or another object
> practice use?]

ODB already does all of this for you. If you try to access the database
from multiple threads simultaneously, then ODB will create and use
several connections. When each thread is done with its transaction,
the connection is placed into a connection pool where it can be
reused by another thread. You can read about all this in Section 18.3,
"SQLite Connection and Connection Factory".


> I will be happy to hear any other suggestions, advises and best
> practices on ODB SQLite, thread safe and concurrency ?

SQLite multi-threaded concurrency is quite poor, especially if your
workload is write-heavy. ODB already does everything there is to do
to make it perform as well as possible, so the first thing is to
see whether it is actually fast enough for your application.

If it is not, then the only thing to do is to try to make your
transactions as short as possible. It is common to find code that
is executed inside a transaction (like object creation) that doesn't
need to be there. Here is a typical example:

transaction t (db.begin ());
object o ("Test", "Data");
db.persist (p);
log << "auto-assigned object id " << o.id ();
t.commit ();

Which should be:

object o ("Test", "Data");

transaction t (db.begin ());
db.persist (p);
t.commit ();

log << "auto-assigned object id " << o.id ();

Finally, if you are getting a lot of deadlock exceptions, one thing
that you can do is start write transactions as such right away with
the odb::sqlite::database::begin_immediate() call.

Boris