mORMot Open Source

nhungthientai

Member

Registered: 2019-03-13

Posts: 1

Specific sqlite settings!!

Hi,

I try to migrate from DevArt LiteDAC to SynSqlite3/SynDB because of performance issues.

My current server code (with LiteDAC) use Sqlite dll with this compile settings:

SQLITE_CASE_SENSITIVE_LIKE
SQLITE_THREADSAFE=2

and this runtime settings:

SQLITE_OPEN_SHAREDCACHE
PRAGMA read_uncommitted = true;
BusyTimeout = 15 (seconds)

My server create sqlite connection for each client request (each request in their own thread) and use BEGIN IMMEDIATE for writing transactions.

Does SynSqlite3/SynDB will works with SQLITE_THREADSAFE=2 ? And it's possible to set SQLITE_OPEN_SHAREDCACHE and PRAGMA read_uncommitted = true somehow before connection ?

And final question: is TSQLDBSQLite3ConnectionProperties.MainSQLite3DB.BusyTimeout works as expected ?

Thanks for help !

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Specific sqlite settings!!

The statically linked SQlite3 library is compiled with https://github.com/synopse/mORMot/blob/ … /sqlite3.c bootstap file.

You can see that SQLITE_THREADSAFE is 1.

SQLITE_CASE_SENSITIVE_LIKE is not defined, but you can run https://www.sqlite.org/pragma.html#prag … itive_like

We also defined SQLITE_OMIT_SHARED_CACHE as 1.
It means we expect a single connection to the database.
This is also needed for the optional encryption feature.

Our SynSqlite3 wrapper serialize all operations via a lock.

So you would have to change/tune the data access model of your application, I am afraid.

edwinsn

Member

Registered: 2010-07-02

Posts: 1,218

Re: Specific sqlite settings!!

It means we expect a single connection to the database.
...
Our SynSqlite3 wrapper serialize all operations via a lock.

Hello ab, out of curiosity, when sqlite is configured to allow multi connections, does it also use a lock to coordinate the multiple access, or does it have a better approach than locking?

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Specific sqlite settings!!

There is a giant lock at SQLite3 pager level.

See https://www.sqlite.org/lockingv3.html