Is it useful to use snapshots transaction for read-only query?

[pierantonio.bovo]

But Eivind had previously said:
"... SELECT queries are always run in an implicit SNAPSHOT transaction anyway".
Maybe this doesn't apply if there is already an open transaction?

Do you think this could be better?

aDatabase.StartTransaction;
aTable.Edit; // aTable.Database = aDatabase
...
with TNxQuery.Create(nil) do
try

Database := aDatabase;
Sql.Text := 'START TRANSACTION SNAPSHOT; SELECT TOP 1 current_date FROM "foecfgloc"; COMMIT; ';
BlockReadSize := 512 * 1024;
RequestLive := false;
ReadOnly:= true;
TimeOut:=0;
Open;
...

except

Free;
raise;

end;
....

aTable.Post;
aDatabase.Commit;

Tks

Hello,

The problem is that you are *explicitly* starting a normal transaction
with the "ADatabase.StartTransaction;" call.

Everything from that point forward, until a Rollback or a Commit, will
be run on the context of that transaction.

If you don't make that call, then the Query, as Eivind said, will create
a snapshot transaction internally.

If you want to start an explicit snapshot transaction then you have to
set to true the corresponding parameter on the StartTransaction call.

It is not clear in the example what does the ATable does or why. You
will need to have it connected to a nxDatabase/nxSession chain anyway to
be able to use it, and it will have to be a different session than the
one used by the Query, because if it is using the same then the
transaction will be upgraded to a normal transaction when trying to edit
the table (or maybe the Edit will fail, I don't remember what happens
when an explicit snapshot transaction is active and you try to edit a
table).

HTH,

Rodrigo Gómez

pierantonio.bovo wrote on 19/09/2024 08:01 a. m.:
> But Eivind had previously said:
> "... SELECT queries are always run in an implicit SNAPSHOT transaction
> anyway".
> Maybe this doesn't apply if there is already an open transaction?
>
> Do you think this could be better?
>
> ADATABASE.STARTTRANSACTION;
> ATABLE.EDIT; [/b]// ATABLE.DATABASE = ADATABASE
> [b]...
> WITH TNXQUERY.CREATE(NIL) DO
> TRY
> DATABASE := ADATABASE;
> SQL.TEXT := 'START TRANSACTION SNAPSHOT; SELECT
> TOP 1 CURRENT_DATE FROM \"FOECFGLOC\"; COMMIT;
> ';
> BLOCKREADSIZE := 512 * 1024;
> REQUESTLIVE := FALSE;
> READONLY:= TRUE;
> TIMEOUT:=0;
> OPEN;
> ..
> EXCEPT
> FREE;
> RAISE;
> END;
> ...
>
> ATABLE.POST;
> ADATABASE.COMMIT;
>
> Tks
>
>

> But Eivind had previously said:
> "... SELECT queries are always run in an implicit SNAPSHOT transaction
> anyway".
> Maybe this doesn't apply if there is already an open transaction?

The above applies when no explicit transaction is already present. I
should have specificed that, sorry.

A more complete initial problem description would have helped.



--
Eivind Bakkestuen [NDD]

[pierantonio.bovo]

Thanks to all
I try to summarize the solution with some source code.

If I need to use a transaction to modify data from one or more tables and, at the same time, I need to read data from other tables then I need to use two distinct TNxDatabase instances.

aDatabase.StartTransaction;
aTable.Edit; // aTable.Database = aDatabase
...
with TNxQuery.Create(nil) do
try

Database := secondDatabase;
Sql.Text := 'START TRANSACTION SNAPSHOT; SELECT TOP 1 current_date FROM "foecfgloc"; COMMIT; ';
BlockReadSize := 512 * 1024;
RequestLive := false;
ReadOnly:= true;
TimeOut:=0;
Open;
...

except

Free;
raise;

end;
....

aTable.Post;
aDatabase.Commit;

> If I need to use a transaction to modify data from one or more tables
> and, at the same time, I need to read data from other tables then I
> need to use two distinct TNxDatabase instances.

I will emphasize here, that nobody has said that this is the way it
should be done. Using two separate transactions might acually come back
to bite you, because the whole idea of wrapping an update process in a
(single!) transaction is data consistency.

The recommended way to handle lock errors is to implement retries.

You might also be interested in the TnxDatabase.StartTransactionWith()
where you list all the tables required, or recently added methods to
add tables to an existing transaction.


> Sql.Text := 'START TRANSACTION SNAPSHOT; SELECT TOP 1 current_date
> FROM "foecfgloc"; COMMIT; ';

Again, the transaction commands will be pointless here, but also
harmless.

--
Eivind Bakkestuen [NDD]

[pierantonio.bovo]

Thank you for the recommendations.
I am already using retry techniques and reducing operations within a transaction, but in some cases, this is not enough.
My idea is to separate operations that work on different tables within the same database: I want to avoid that reading from one table can generate conflicts (or slowing down) at the database level if a modification is being made on other tables using a transaction at the same time.
That's why I have considered using a second pair of TNxDatabase and TNxSession objects in 'readonly' mode.