mORMot Open Source

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

SharedTransaction(sessionID=1) with mixed connections exception

I' m getting an exception when using Trestbatch arising from mormot.db.sql line 3702: "SharedTransaction(sessionID=1) with mixed connections: TsqlDBZeosConnection and TsqlDBZeosConnection"
I am using Mariadb with Zeos and I have implemented multiple TrestServerDB instances connecting to the same external database in order to be used by different threads. It was proposed in a past discussion for better performance.

Should I use the same TrestServerDB instance for all batches or is there something else I missed?
For single inserts/updates, it works ok with autocommit=true, but I have problem with batches.

I have a TSynThreadPool that runs all works with batches in TSynThreadPoolWorkThread derived thread.
Should I have a dedicated TrestServerDB instance for each TSynThreadPoolWorkThread to be used for batches?

Just to note, that I am using predefined IDvalue for inserting orm objects, so the autonumbering by mormot for primary key is not a problem

Thank you in advance

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: SharedTransaction(sessionID=1) with mixed connections exception

Multiple TRestServerDB instances won't make any performance benefit with external DB if you reuse the same TsqlDBZeosConnectionProperties, because the external DB connections use a shared thread-pool owned by the TsqlDBZeosConnectionProperties.

Are you using one dedicated TsqlDBZeosConnectionProperties per TRestServerDB?

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: SharedTransaction(sessionID=1) with mixed connections exception

Yes, I thought the pool creates new dB connections.
So, I have to have a TsqlDBZeosConnectionProperties per Trestserverdb.
Thank you @ab I will report back if I find a problem

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: SharedTransaction(sessionID=1) with mixed connections exception

IMHO for best performance, a single thread writing the pending batches e.g. every 100ms with its own connection could be the best option.

Of course, it could depend on the actual DB and the driver support.

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: SharedTransaction(sessionID=1) with mixed connections exception

Trying to have a separate TsqlDBZeosConnectionProperties per Trestserverdb I noticed that the server is really lacking available connections. Every Trestserverdb  seems to consumes about 20 connections of the external database in a MariaDB server. So, I come back and search the forum to understand. I have some questions and please correct me if needed, in the following statements for a service with https interfaces that do the database works using one or more TrestServerDB instances:

1. Each TrestServerDB instance has a thread to do reading ORM tasks in the DB and a thread to do write ORM tasks in the DB. This is controlled by setting a TRestServerAcquireMode option in each TRestServerUriContextCommand context e.g. TrestserverDB.AcquireExecutionMode[execOrmGet]:=amBackgroundThread;
What I can not understand is how execSoaByInterface will be used. I have a mormot http server that exposes interfaces (derived from TInjectableObjectRest) and all interfaces' methods use a main TrestServerDB instance to do the DB orm tasks. Can different interfaces derived from TInjectableObjectRest use the same one TrestServerDB instance without problems?

2. In past you have proposed me @ab to use  AcquireExecution[execOrmWrite].Safe and lock it before doing a db orm task. This stopped nearly all sql errors with desynchronization in zeos but it is much slower than without it. The idea now was to use different TrestServerDB instances but even that it is not very efficient for the database server.

3. I have found a TSqlDBConnectionPropertiesThreadSafe.EndCurrentThread that you also recommended in the past. How this helps, and when should it be used?

4. How to create a json string from a couple of orms to be saved as a batch and put it in a list and send all the batches to the db in a single thread every 100ms? I am using more than 100 different ormclasses that can be used and write records in the db. An what about blobs that could exist in Torm classes? And what will happen if two clients send the same orm record with the same ID to be added? How can I control if an ORM record with an ID is already inserted in the batch? Probably I need a list for Torms to be added and a Tbatch to add only updates/deletes

Last edited by dcoun (2024-08-26 19:08:49)