Wt

Hey John,

This is a relevant question in the sense that we've also encountered these issues in SQlite based applications.

First of all, you should read up on sqlite concurrency. The default concurrency model easily gets you in deadlocks (which is being avoided and throws the exception) because a write operation needs a write lock which must happen without any reading transactions are active. They have implemented a new wal mode which gets rid of that.

W.r.t. the sqlite error, we are providing the actual error message of sqlite in the exception,

However it does not eliminate the dead lock risk since regularly it needs to merge the write-ahead log in the main datab9

ase file. We haven't actually seen this problem ever happening though.

You really need to move to a real servrt-base database such as Postgres to avoid these problems entirely.

With respect to the error msg, you would want to get the error code in the exception? That is something we should make available indeed.

Regards,

Koen

Thanks Koen,

I started to think that might be the case, and looked into Postgres a couple of days ago. I stumbled on some benchmarks which showed the Postgres Dbo backend was slow, 3x or 4x slower than SQLITE3, if I recall due to it not being digital or something (?). Should I expect a big performance hit moving from SQLITE3 to Postgres, assuming the Postgres server is running on the same machine as the Wt app?

What is the fastest backend? Each of my apps' jobs can involve upwards of 20,000 queries, so they're slow enough as it is!

John

Hey,

Another option which I thought of was to use Sqlite3 with a connection pool of size 1. That will effectively serialize all transactions (wt only takes a connection for the duration of a transaction).

But I do believe that postgres will scale up better as it allows concurrency. There is indeed serialization overhead in the protocol which is especially costly for datetime data but not so much otherwise. Another option is mysql which has the reputation of being faster for simple SQL operations although I don't know if that's still true.

Regards,

Koen

Thanks Koen

I set the connectionpool size to 1 and indeed, the exceptions disappeared! But loading the web UI became quite slow... I think I will investigate Postgres further.

Meanwhile, I am trying to catch the exceptions, but I'm getting no results. Can you tell me if this code is right for displaying errors?

try {
    session.createTables();
}
catch (Wt::Dbo::Exception exError) {
    std::cout << "ERROR CREATING DATABASE - CODE: " + exError.code() + "\n";
}
try {
    session.execute("CREATE INDEX fS ON files(size)");
    session.execute("CREATE INDEX fP ON files(path)");
    session.execute("CREATE INDEX fN ON files(name)");
}
catch (Wt::Dbo::Exception exError) {
    std::cout << "ERROR CREATING INDEX- CODE: " + exError.code() + "\n";

}

Also, while I'm asking, why is

session.execute("CREATE INDEX fS ON files(size)");

(and all the index creation commands) throwing an exception in the first place? It's a valid SQLITE3 command. Do I have my session.execute syntax wrong? Or is there an issue with adding indexes immediately after creating a table? Should I do it some other way?

thanks

John

We had a similar problem. Our solution to get around the DB locks in our multi-threaded (some headless) app was to overload the "Wt::Dbo::Transaction" with an object that sets a [recursive] mutex if we specify a write operation is going to be run.

i.e.

MySqliteTransaction transaction{session, true}
...
object.modify()->some_property = something_else;
...
transaction.commit();

The MySqliteTransaction sees the true and locks a mutex.

Our app isn't meant to be used by many users though.. Performance issues would surely manifest if we had many concurrent users.