a scenario about adding a record with unique field...

dcoun · 2025-03-23 09:53:16

Probably a scenario for a newbie but I am not still sure how to implemented correctly with mormot2:
Everything from the following is happening in the server which connects to an external database and I am using AcquireExecutionMode[execOrmWrite/Get]:=amBackgroundThread;
I have a table TormTable, that except the table ID, it has also an other unique field unif, covered with a unique database index.

TormTable=class(Torm)
  published 
    property unif:ptrint read Funif write Funif;
end;

Before I add a new record in this table (as one job in a list of sql orm adds), I check if an existing record has the same content with the field TormTable.unif and use this record instead.
My problem is to add a new record in this table in a race condition:
Checking if a record with the same TormTable.unif exists and using a TRestBatch for all adds in my function is a solution.
But it can cause a error is a record with the same TormTable.unif contents was just inserted by an other web client request. (There is also a small possibility that such a record can be added by an other external software in the db-This table is defined to check maxID before each insert in mormot).
I was thinking that I can repeat the check and the Trestbatch. But sometimes I am getting out of sync errors from Zeos with indexes collisions and I have not find a solution to avoid them.
In such a case, what is the appropriate way to close and recreate the connection or refresh the db connection pool, in a running mormot server?

An other solution is to use db transaction locking for this table.
1. In such a case, should I use AcquireExecution[execOrmWrite].Safe^.Lock; before Begintransaction?
2. calling commit, if I get an exception should I issue a rollback?
Generally which is the safe way to handle a transaction?

Thank you in advance

ab · 2025-03-23 11:02:22

Some comments:
1) don't use PtrInt for a field, it is not portable between 32-bit and 64-bit. Use explicit Int64 or integer.
2) what do you store into this field? Why just not use the TID?
3) look at TSynUniqueIdentifier if you really need a separated field, or at TOrmModel.SetIDGenerator() to have the ORM fill the TID directly.

dcoun · 2025-03-23 11:07:51

ab wrote:

Some comments:
1) don't use PtrInt for a field, it is not portable between 32-bit and 64-bit. Use explicit Int64 or integer.
2) what do you store into this field? Why just not use the TID?
3) look at TSynUniqueIdentifier if you really need a separated field, or at TOrmModel.SetIDGenerator() to have the ORM fill the TID directly.

The unique field TormTable.unif's content is predefined by other source. I can not change it. But I must have one record in the table per TormTable.unif content.
I thought about hash32 for defining idvalue but I want to avoid it, because in other tables I have multiple unique fields and it becomes more possible for duplicate hash result

ab · 2025-03-23 11:15:38

So why not just this PtrInt field as the TID?

dcoun · 2025-03-23 11:18:41

ab wrote:

So why not just this PtrInt field as the TID?

Because in the near future, the unique field can be a string too. In an other table, it is a GUID

ab · 2025-03-23 14:48:49

So the easiest in your case is to use a global lock during the process.
And perhaps use an in-memory cache - e.g. via TSynDictionary - of the unique field, for lookup. Reading all Id and UniF values at server startup, then using it as fast reference.

And about the batch adding, don't use a manual transaction, but let the TRestBatch implement it for you.
It could fallback and rollback the whole batch if needed.

dcoun · 2025-03-23 16:28:34

The problem with the TrestBatch, is that sometimes the next orm object needs first object's IDvalue in one of its properties.
How can I handle this?

ab · 2025-03-23 17:05:50

By filling the in-memory dictionary during preparing the TRestBatch.
In case of error writing, just rollback the in-memory dictionary by reading again all id/unif from DB again (it should not be called anyway).

dcoun · 2025-03-23 17:09:57

The in-memory dictionary can not be used if I have an other app outside mormot that feeds too records.
I can use the in-memory dictionary for a number of tables, but not for all.

ab · 2025-03-23 17:30:16

So resolve it at SQL level, and don't use the ORM.

dcoun · 2025-03-23 17:34:34

In an other thread, you propose to recreate the database connection. How can I do that?
I notice that I can "expire" the connections pool. What should I check before doing that?

ab · 2025-03-23 17:44:09

Which DB provider do you use?
Some of our providers do auto-reconnect if needed.

dcoun · 2025-03-23 17:46:34

Zeos with mariadb
postgresql with mormot's direct

mORMot Open Source

#1 2025-03-23 09:53:16

a scenario about adding a record with unique field...

#2 2025-03-23 11:02:22

Re: a scenario about adding a record with unique field...

#3 2025-03-23 11:07:51

Re: a scenario about adding a record with unique field...

#4 2025-03-23 11:15:38

Re: a scenario about adding a record with unique field...

#5 2025-03-23 11:18:41

Re: a scenario about adding a record with unique field...

#6 2025-03-23 14:48:49

Re: a scenario about adding a record with unique field...

#7 2025-03-23 16:28:34

Re: a scenario about adding a record with unique field...

#8 2025-03-23 17:05:50

Re: a scenario about adding a record with unique field...

#9 2025-03-23 17:09:57

Re: a scenario about adding a record with unique field...

#10 2025-03-23 17:30:16

Re: a scenario about adding a record with unique field...

#11 2025-03-23 17:34:34

Re: a scenario about adding a record with unique field...

#12 2025-03-23 17:44:09

Re: a scenario about adding a record with unique field...

#13 2025-03-23 17:46:34

Re: a scenario about adding a record with unique field...

Board footer