mORMot Open Source

vennexedu

Member

Registered: 2024-11-12

Posts: 12

BEGIN CONCURRENT transactions implemented?

Do transactions with the flag "BEGIN CONCURRENT" exist in mORMot 2 as specific methods? So far I couldn't find this option anywhere. I'm trying to write into my SQLite3 db concurrently with multiple threads, but first I got "db is locked" errors and after using "BusyTimout := 10000" I get "NOTADB" errors. I already use WAL2 mode. Couldn't find this one implemented in mORMot 2 either. I'm also using serialized mode. Is there a way to change it into multithread with the library? I figured it's possible with "BeforeInitialized".

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: BEGIN CONCURRENT transactions implemented?

I suspect you use direct SQlite3 access, not the ORM.
There is a giant lock at the SQlite3 wrapper level.
So the writes will always be serialized, whatever setting you use.

To speed things up, you may rewrite your writes to use a mORMot ORM batch from several threads, then having a dedicated single thread writing to the DB.

vennexedu

Member

Registered: 2024-11-12

Posts: 12

Re: BEGIN CONCURRENT transactions implemented?

So there's actually no way to write concurrently into the SQLite3 db? Yes, I use low-level access, so to speak. I suspected high-level ORM functions to be slower than direct access. First I used a single thread for writing into the DB that collects the strings of multiple threads with direct access. That was relatively fast though. I use TTextWriter, btw. It's brilliant!

But shouldn't multi-threading be possible with SQLite3 if we use THREADSAFE=2? My data consists of records, each of which doesn't have an index (maybe that's the bottleneck?), but a timestamp and a box number, whereas the combination makes this record unique. I thought that since each row in unique, SQLite3 would use different b-trees for each entry. The order in which the rows arrive does not matter since we sort them afterwards anyway. I know it's not an SQLite3 support forum, but mORMot2 and SQLite3 are a bit intertwined.

Last edited by vennexedu (2024-11-15 07:46:31)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: BEGIN CONCURRENT transactions implemented?

As I wrote above: There is a giant lock at the SQlite3 wrapper level.

First task is to check about the actual timing of each requests.
Also ensure you use the proper SQLIte3 settings, e.g. synchronous=off
I easily reach 1,000,000 inserts per second with the ORM in batch mode on my PC, on a single thread.

vennexedu

Member

Registered: 2024-11-12

Posts: 12

Re: BEGIN CONCURRENT transactions implemented?

But are your columns dynamic or static? Because mine are dynamic and therefore ORM wouldn't make sense for me, right?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: BEGIN CONCURRENT transactions implemented?

No ORM columns are... object fields... so fixed, not dynamic.
(even if we plan to add some dynamic/runtime schema too)

The idea is that if the ORM reaches a lot of writes per second, you could achieve good enough performance with SQLite3 even with a main lock in your own code, if you use the same pattern: e.g. transaction + multi-insert statements.