#11  
Old 20th September 2024, 02:01 AM
pierantonio.bovo pierantonio.bovo is offline
Junior Member
 
Join Date: Jun 2011
Posts: 8
Default

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
  #12  
Old 20th September 2024, 04:28 AM
Rodrigo Gómez Córdova
 
Posts: n/a
Default Re: Is it useful to use snapshots transaction for read-only query?

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
>
>

  #13  
Old 23rd September 2024, 12:50 PM
Eivind Bakkestuen
 
Posts: n/a
Default Re: Is it useful to use snapshots transaction for read-only query?

> 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]
  #14  
Old 27th September 2024, 01:40 AM
pierantonio.bovo pierantonio.bovo is offline
Junior Member
 
Join Date: Jun 2011
Posts: 8
Default

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;
  #15  
Old 27th September 2024, 01:10 PM
Eivind Bakkestuen
 
Posts: n/a
Default Re: Is it useful to use snapshots transaction for read-only query?

> 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]
  #16  
Old 1st October 2024, 09:19 PM
pierantonio.bovo pierantonio.bovo is offline
Junior Member
 
Join Date: Jun 2011
Posts: 8
Default

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.


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Read only user can't run Query David Rose nexusdb.public.support 2 29th November 2013 02:39 PM
Read-Only Query Daniel Rogers nexusdb.public.support.sql 1 30th July 2009 06:11 PM
SQL Transaction query query Mark Robinson nexusdb.public.support.sql 4 17th June 2007 08:27 PM
Snapshots Ken Randall nexusdb.public.support 4 6th June 2007 10:36 PM
Questions about snapshots Scott Martin nexusdb.public.support 1 13th April 2007 08:52 AM


All times are GMT +11. The time now is 05:10 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.