Re: [Firebird-devel] Start transaction from base transaction

[Alex Peshkoff via Firebird-devel]

On 3/1/19 4:50 PM, Dmitry Yemanov wrote:

01.03.2019 16:40, Adriano dos Santos Fernandes wrote:


BTW it would be good to known if this pull request (with
fb_info_tra_snapshot_number, SET TRANSACTION
SNAPSHOT [ AT NUMBER  ] and isc_tpb_at_snapshot_number) will go to
master/v4.


No problems from me.



+1



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dmitry Yemanov]

01.03.2019 16:40, Adriano dos Santos Fernandes wrote:


BTW it would be good to known if this pull request (with
fb_info_tra_snapshot_number, SET TRANSACTION
SNAPSHOT [ AT NUMBER  ] and isc_tpb_at_snapshot_number) will go to
master/v4.


No problems from me.


Dmitry



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

01.03.2019 15:40, Adriano dos Santos Fernandes wrote:

On 01/03/2019 10:28, Vlad Khorsun wrote:

...

   BTW, what branch go you mean ?


https://github.com/FirebirdSQL/firebird/pull/193

I'll change SNAPSHOT_CN name in different commit as it's already present
in master.


  Understand now


BTW it would be good to known if this pull request (with
fb_info_tra_snapshot_number, SET TRANSACTION
SNAPSHOT [ AT NUMBER  ] and isc_tpb_at_snapshot_number) will go to
master/v4.


  I vote to see it there.

Regards,
Vlad


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 01/03/2019 10:28, Vlad Khorsun wrote:
> 01.03.2019 13:16, Adriano dos Santos Fernandes wrote:
>
>> I am assuming nobody objects and will implement the changes in the
>> branch.
>
>   No objection, just not forget to change syntax\constant names as was
> agreed recently:
>
> ---
> Which then I would go to isc_tpb_at_snapshot_number and SET TRANSACTION
> SNAPSHOT [ AT NUMBER  ]
> ---
>
It's what I am telling replying to my own message quoting the previous
message part.


>   BTW, what branch go you mean ?
>
https://github.com/FirebirdSQL/firebird/pull/193

I'll change SNAPSHOT_CN name in different commit as it's already present
in master.

BTW it would be good to known if this pull request (with
fb_info_tra_snapshot_number, SET TRANSACTION
SNAPSHOT [ AT NUMBER  ] and isc_tpb_at_snapshot_number) will go to
master/v4.


Adriano



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 25/02/2019 13:21, Adriano dos Santos Fernandes wrote:
> Vlad is proposing the new info code to be named
> fb_info_tra_snapshot_number. I have no problem with it, provided that
> SNAPSHOT_CN is also renamed (with he seems to be open), but for what?
> SNAPSHOT_NUMBER too? (I have no problem with that)
>
> So if we consistently call that number a "SNAPSHOT NUMBER" the path to
> name the new TPB and SET TRANSACTION subclause becomes easy.
>
> Which then I would go to isc_tpb_at_snapshot_number and SET TRANSACTION
> SNAPSHOT [ AT NUMBER  ].
>
>
I am assuming nobody objects and will implement the changes in the branch.


Adriano



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

Hi!

As part of READ COMMITTED READ CONSISTENCY, it was added context
variables (under RDB$GET_CONTEXT's SYSTEM namespace) GLOBAL_CN and
SNAPSHOT_CN.

GLOBAL_CN: Most current value of global Commit Number counter

SNAPSHOT_CN: Value of Commit Number of currently database snapshot:
either transaction level (for SNAPSHOT or CONSISTENCY transaction), or
request level (for READ COMMITTED READ CONSISTENCY transaction). NULL,
if snapshot is not exist.

It was also added function RDB$GET_TRANSACTION_CN( 
): Returns commit number of given transaction.

I may be wrong but as RDB$GET_TRANSACTION_CN does not get the
SNAPSHOT_CN of active transactions, but only commit number of committed
transactions, these set of (user exposed) feature is now currently only
for curiosity or debug purposes, but not for monitoring. If SNAPSHOT_CN
is exposed outside own transaction/attachment (say in MON$ or with
another function as RDB$GET_TRANSACTION_CN that gets the SNAPSHOT_CN
from another transaction) that feature may also be used for monitoring.
I think that must be considered. Despite that, with this thread feature
SNAPSHOT_CN gains a function, i.e., to start more than one transaction
(could be in different attachments) seeing the same initial data.

It's agreed that we also need a transaction info code to get the same
thing currently returned by SNAPSHOT_CN of SNAPSHOT transactions.

So all this functionality is very related, so they names must match
accordingly.

Vlad is proposing the new info code to be named
fb_info_tra_snapshot_number. I have no problem with it, provided that
SNAPSHOT_CN is also renamed (with he seems to be open), but for what?
SNAPSHOT_NUMBER too? (I have no problem with that)

So if we consistently call that number a "SNAPSHOT NUMBER" the path to
name the new TPB and SET TRANSACTION subclause becomes easy.

Which then I would go to isc_tpb_at_snapshot_number and SET TRANSACTION
SNAPSHOT [ AT NUMBER  ].


Adriano



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 24/02/2019 08:23, Vlad Khorsun wrote:



 > TPB: isc_tpb_snapshot_commit_number,  



         isc_tpb_snapshot_number  

    Regards,
    Vlad

    PS we also must add isc_info_tra_snapshot_number and, probably, 
context variable.




Don't we already have context SNAPSHOT_CN? It already has the same 
meaning of the new feature, so therefore what context you would want 
to add?


  SNAPSHOT_CN returns snapshot number of current request if 
transaction is read-committed read
consistency (RCRC). But... after more thinking i consider it is ok to 
use it for snapshot cloning.
Additional variable returning NULL for RCRC transactions will add more 
confusing. From the other
side - there is no problem to clone any kind of existing snapshot. 
Users must be warned that snapshot
transaction which cloned request-level snapshot of another RCRC 
transaction will see same data at
base request and subsequent requests in the base RCR transaction could 
see another data.


Yes, and that is already possible as an implementation side effect. It 
should be extremely discouraged, except for cases like long running 
request detected via monitoring that one wants to debug in another 
connection.



  I also suggest that new isc_info_tra_snapshot_number returns the 
same data as SNAPSHOT_CN variable.


Yes, no problem. But for RC it will be NULL as there is no request.




And then SNAPSHOT_CN means "SNAPSHOT COMMIT NUMBER", it's the reason 
for the syntax that I offered.


  We can change it, if needed. Note, I introduced two variables: 
GLOBAL_CN and SNAPSHOT_CN and its
names looks logical and consistent to me (at least at that time). I 
see no problem to change names

to something better.

Yes, the names must match as SNAPSHOT_CN means same thing as the clause 
passed to SET TRANSACTION.



Adriano



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

24.02.2019 12:37, Dimitry Sibiryakov wrote:

24.02.2019 3:23, Adriano dos Santos Fernandes wrote:

    maybe:
         SET TRANSACTION SNAPSHOT [USING SNAPSHOT ]
    or
         SET TRANSACTION SNAPSHOT [USING SNAPSHOT NUMBER ]

What I dislike here is double SNAPSHOT words.


   You can make it optional.


  No objection from my side

Regards,
Vlad


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

24.02.2019 4:23, Adriano dos Santos Fernandes wrote:

On Sat, Feb 23, 2019, 21:30 Vlad Khorsun wrote:

23.02.2019 21:14, Adriano dos Santos Fernandes wrote:
 > Hi!
 >
 > After changes to use commit number instead of base transaction number, I
 > offer to make that interfaces for the feature:

    I offer to not introduce additional confusing with different usages of 
commit
numbers.

    Commit Number (CN) itself is an unique value assigned to the every 
committed
transaction. The source of that value is per-database counter. When some 
database
snapshot is created it uses current value of database counter of commit 
numbers
as own identifier. Lets name it Snapshot Number (SN) to distinguish from 
Commit
Number assigned to transaction. The sourse of CN and SN is the same, but 
usage
and meaning is very different !

    Therefore

 > SQL command: SET TRANSACTION SNAPSHOT COMMIT NUMBER 
 >
 > (some variant as SNAPSHOT FROM COMMIT NUMBER or SNAPSHOT BASE COMMIT
 > NUMBER may be acceptable)

maybe:
         SET TRANSACTION SNAPSHOT [USING SNAPSHOT ]
or
         SET TRANSACTION SNAPSHOT [USING SNAPSHOT NUMBER ]


What I dislike here is double SNAPSHOT words.


  Same concerns here


 > TPB: isc_tpb_snapshot_commit_number,  

         isc_tpb_snapshot_number  

Regards,
Vlad

PS we also must add isc_info_tra_snapshot_number and, probably, context 
variable.



Don't we already have context SNAPSHOT_CN? It already has the same meaning of the new feature, so therefore what context you would 
want to add?


  SNAPSHOT_CN returns snapshot number of current request if transaction is 
read-committed read
consistency (RCRC). But... after more thinking i consider it is ok to use it 
for snapshot cloning.
Additional variable returning NULL for RCRC transactions will add more 
confusing. From the other
side - there is no problem to clone any kind of existing snapshot. Users must 
be warned that snapshot
transaction which cloned request-level snapshot of another RCRC transaction 
will see same data at
base request and subsequent requests in the base RCR transaction could see 
another data.

  I also suggest that new isc_info_tra_snapshot_number returns the same data as 
SNAPSHOT_CN variable.


And then SNAPSHOT_CN means "SNAPSHOT COMMIT NUMBER", it's the reason for the 
syntax that I offered.


  We can change it, if needed. Note, I introduced two variables: GLOBAL_CN and 
SNAPSHOT_CN and its
names looks logical and consistent to me (at least at that time). I see no 
problem to change names
to something better.

Regards,
Vlad

PS As for me, "Snapshot Commit Number" is full name for (more usable) "Snapshot Number". 
"Snapshot CN"
just another way to make full name shorter. We need to choose some consistent 
way to name the things
before final release.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

24.02.2019 3:23, Adriano dos Santos Fernandes wrote:

maybe:
         SET TRANSACTION SNAPSHOT [USING SNAPSHOT ]
or
         SET TRANSACTION SNAPSHOT [USING SNAPSHOT NUMBER ]

What I dislike here is double SNAPSHOT words.


  You can make it optional.

--
  WBR, SD.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On Sat, Feb 23, 2019, 21:30 Vlad Khorsun  wrote:

> 23.02.2019 21:14, Adriano dos Santos Fernandes wrote:
> > Hi!
> >
> > After changes to use commit number instead of base transaction number, I
> > offer to make that interfaces for the feature:
>
>I offer to not introduce additional confusing with different usages of
> commit
> numbers.
>
>Commit Number (CN) itself is an unique value assigned to the every
> committed
> transaction. The source of that value is per-database counter. When some
> database
> snapshot is created it uses current value of database counter of commit
> numbers
> as own identifier. Lets name it Snapshot Number (SN) to distinguish from
> Commit
> Number assigned to transaction. The sourse of CN and SN is the same, but
> usage
> and meaning is very different !
>
>Therefore
>
> > SQL command: SET TRANSACTION SNAPSHOT COMMIT NUMBER 
> >
> > (some variant as SNAPSHOT FROM COMMIT NUMBER or SNAPSHOT BASE COMMIT
> > NUMBER may be acceptable)
>
> maybe:
> SET TRANSACTION SNAPSHOT [USING SNAPSHOT ]
> or
> SET TRANSACTION SNAPSHOT [USING SNAPSHOT NUMBER ]
>

What I dislike here is double SNAPSHOT words.



>
>
> > TPB: isc_tpb_snapshot_commit_number,   number>
>
> isc_tpb_snapshot_number  
>
> Regards,
> Vlad
>
> PS we also must add isc_info_tra_snapshot_number and, probably, context
> variable.
>


Don't we already have context SNAPSHOT_CN? It already has the same meaning
of the new feature, so therefore what context you would want to add?

And then SNAPSHOT_CN means "SNAPSHOT COMMIT NUMBER", it's the reason for
the syntax that I offered.


Adriano
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

23.02.2019 21:14, Adriano dos Santos Fernandes wrote:

Hi!

After changes to use commit number instead of base transaction number, I
offer to make that interfaces for the feature:


  I offer to not introduce additional confusing with different usages of commit
numbers.

  Commit Number (CN) itself is an unique value assigned to the every committed
transaction. The source of that value is per-database counter. When some 
database
snapshot is created it uses current value of database counter of commit numbers
as own identifier. Lets name it Snapshot Number (SN) to distinguish from Commit
Number assigned to transaction. The sourse of CN and SN is the same, but usage
and meaning is very different !

  Therefore


SQL command: SET TRANSACTION SNAPSHOT COMMIT NUMBER 

(some variant as SNAPSHOT FROM COMMIT NUMBER or SNAPSHOT BASE COMMIT
NUMBER may be acceptable)


maybe:
SET TRANSACTION SNAPSHOT [USING SNAPSHOT ]
or
SET TRANSACTION SNAPSHOT [USING SNAPSHOT NUMBER ]


TPB: isc_tpb_snapshot_commit_number,  


isc_tpb_snapshot_number  

Regards,
Vlad

PS we also must add isc_info_tra_snapshot_number and, probably, context 
variable.


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

Hi!

After changes to use commit number instead of base transaction number, I
offer to make that interfaces for the feature:

SQL command: SET TRANSACTION SNAPSHOT COMMIT NUMBER 

(some variant as SNAPSHOT FROM COMMIT NUMBER or SNAPSHOT BASE COMMIT
NUMBER may be acceptable)

TPB: isc_tpb_snapshot_commit_number,  


Adriano


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 19/02/2019 13:56, Vlad Khorsun wrote:

> 
>   As i understand you - it doen't break anything. For me, it is safe to
> assign
> to the new snapshot (CONCURRENCY) transaction number of already existing
> and
> still alive snapshot. It is very important to ensure existence of that
> snapshot
> number until assignment happens (and new snapshot slot allocated).
> 

Thank you.

I created draft pull request for review and discussion.

https://github.com/FirebirdSQL/firebird/pull/193


Adriano



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

19.02.2019 18:24, Adriano dos Santos Fernandes wrote:

Hi Vlad,

I restarted work on this feature now using commit numbers.


  Good to know. Could you publish user interface before it is too late ? ;)


Initial prototype seems to work easily.


  It should be not too hard, agree


So now with current master code, is it ok to have transaction numbers
TN1 < TN2 < TN3 with their correspondents tra_snapshot_number not being
TSN1 <= TSN2 <= TSN3 ?


  Yes. More, RC transactions have no tra_snapshot_number (as you know of 
course).


That is, when TN3 starts it's tra_snapshot_number will not be the latest
commit number if that transaction is going to share another transaction
snapshot, but say the same tra_snapshot_number of TN1 even if TN2 was
already committed.

Does that break any assumption on Firebird transaction or GC architecture?


  As i understand you - it doen't break anything. For me, it is safe to assign
to the new snapshot (CONCURRENCY) transaction number of already existing and
still alive snapshot. It is very important to ensure existence of that snapshot
number until assignment happens (and new snapshot slot allocated).

Regards,
Vlad


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

Hi Vlad,

I restarted work on this feature now using commit numbers.

Initial prototype seems to work easily.

So now with current master code, is it ok to have transaction numbers
TN1 < TN2 < TN3 with their correspondents tra_snapshot_number not being
TSN1 <= TSN2 <= TSN3 ?

That is, when TN3 starts it's tra_snapshot_number will not be the latest
commit number if that transaction is going to share another transaction
snapshot, but say the same tra_snapshot_number of TN1 even if TN2 was
already committed.

Does that break any assumption on Firebird transaction or GC architecture?


Adriano



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> The trick words is how to shortly tell this "the snapshot used to start
> transaction ".
> 
> And it's even more trick when we think about commit retaining.
>

> My understand is that if transaction  had a commit retaining, subsequent
> new derived transactions will use the snapshot after the commit retaining in
> .
> 
> However, a already established derived transaction from  will ignore
> subsequent commit retaining done in .

IMO, nothing says that we need to support all transaction 'modes' with this 
feature -- nor should we.


Sean


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 10/05/2017 16:01, Leyne, Sean wrote:
>> "Starting from"?
> That would be misleading, the connection would not be able to see commits 
> from later transactions, which is certainly not the case.
>
>
The trick words is how to shortly tell this "the snapshot used to start
transaction ".

And it's even more trick when we think about commit retaining.

My understand is that if transaction  had a commit retaining,
subsequent new derived transactions will use the snapshot after the
commit retaining in .

However, a already established derived transaction from  will ignore
subsequent commit retaining done in .


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> "Starting from"?

That would be misleading, the connection would not be able to see commits from 
later transactions, which is certainly not the case.


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[livius]

Maybe

"Starting from"?

regards,
Karol Bieniaszewski

-Oryginalna wiadomość- 
From: Leyne, Sean
Sent: Tuesday, May 9, 2017 9:20 PM
To: For discussion among Firebird Developers
Subject: Re: [Firebird-devel] Start transaction from base transaction


> >>> + | SNAPSHOT SHARING FROM 
> >> SNAPSHOT BASED ON 
> >> ?
> >>
> > Do you think with this words it's clear that the new transaction uses
> > the same snapshot used in , instead of incorrectly saying that it
> > will use  as the snapshot for the new transaction?
> >
> > Thinking in these terms, I'm more inclined to adapt (renaming SHARING
> > to
> > SHARED) Vlad's syntax to:
> >
> > SET TRANSACTION ISOLATION LEVEL SNAPSHOT SHARED FROM 
>
>For me both variants are OK :) Probably native english speakers could 
> offer
> something better and less ambiguous.

IMO, "BASED ON" would seem to me to be more 'natural' terminology.


Sean


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel 


---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> >>> + | SNAPSHOT SHARING FROM 
> >> SNAPSHOT BASED ON 
> >> ?
> >>
> > Do you think with this words it's clear that the new transaction uses
> > the same snapshot used in , instead of incorrectly saying that it
> > will use  as the snapshot for the new transaction?
> >
> > Thinking in these terms, I'm more inclined to adapt (renaming SHARING
> > to
> > SHARED) Vlad's syntax to:
> >
> > SET TRANSACTION ISOLATION LEVEL SNAPSHOT SHARED FROM 
> 
>For me both variants are OK :) Probably native english speakers could offer
> something better and less ambiguous.

IMO, "BASED ON" would seem to me to be more 'natural' terminology.


Sean


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

Em 06/05/2017 09:15, livius escreveu:
> 
>> Yes, but we should avoid to write whole novels at SQL statements ;)
> 
> Agree - but to fast chice is also not good
> 
>> It have no sence (if i understand you :) )
> Maybe, but it should be prohibited in code or designed to do not create 
> wrong behavior
> You know if something is possible someone use it
> and i mean:
> base transaction
> transaction 2 share base transaction
> transaction 3 share base transaction
> and someone try
> transaction 4 share e.g. transaction 2 ;-) in this point it should share 
> base or do not alow it
> but maybe heare are not any problem?
> 

transaction 4 will start with the same snapshot started in transaction,
transaction 2 and transaction 3.


Adriano

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[livius]

>Yes, but we should avoid to write whole novels at SQL statements ;)

Agree - but to fast chice is also not good

> It have no sence (if i understand you :) )
Maybe, but it should be prohibited in code or designed to do not create 
wrong behavior
You know if something is possible someone use it
and i mean:
base transaction
transaction 2 share base transaction
transaction 3 share base transaction
and someone try
transaction 4 share e.g. transaction 2 ;-) in this point it should share 
base or do not alow it
but maybe heare are not any problem?

regards,
Karol Bieniaszewski

-Oryginalna wiadomość- 
From: Vlad Khorsun
Sent: Saturday, May 6, 2017 11:55 AM
To: firebird-devel@lists.sourceforge.net
Subject: Re: [Firebird-devel] Start transaction from base transaction

06.05.2017 11:47, livius wrote:
> Hi,
>
> for me name is confusing - beacause here can be two cases:
> 1. Snapshot transaction is shared

   Looks like wrong\incomplete definition, as

> 2. only view of database from its start point is shared and any 
> modification
> caused by base snapshot and any sharing transaction is not shared between
> transactions.

we speak about this one.

> Name must specify real maining.

   Yes, but we should avoid to write whole novels at SQL statements ;)

> And question araise - is it possible that shared transaction with share 
> from
> base transaction also possible to be shared? (i do not know if you
> understand me)

   It have no sence (if i understand you :) )

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel 


---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

06.05.2017 11:47, livius wrote:
> Hi,
> 
> for me name is confusing - beacause here can be two cases:
> 1. Snapshot transaction is shared

   Looks like wrong\incomplete definition, as

> 2. only view of database from its start point is shared and any modification
> caused by base snapshot and any sharing transaction is not shared between
> transactions.

we speak about this one.

> Name must specify real maining.

   Yes, but we should avoid to write whole novels at SQL statements ;)

> And question araise - is it possible that shared transaction with share from
> base transaction also possible to be shared? (i do not know if you
> understand me)

   It have no sence (if i understand you :) )

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[livius]

Hi,

for me name is confusing - beacause here can be two cases:
1. Snapshot transaction is shared
2. only view of database from its start point is shared and any modification 
caused by base snapshot and any sharing transaction is not shared between 
transactions.
Name must specify real maining.

And question araise - is it possible that shared transaction with share from 
base transaction also possible to be shared? (i do not know if you 
understand me)

regards,
Karol Bieniaszewski


-Oryginalna wiadomość- 
From: Vlad Khorsun
Sent: Friday, May 5, 2017 11:01 PM
To: firebird-devel@lists.sourceforge.net
Subject: Re: [Firebird-devel] Start transaction from base transaction

05.05.2017 21:10, Adriano dos Santos Fernandes wrote:
> On 05/05/2017 14:43, Dmitry Yemanov wrote:
>> 05.05.2017 20:01, Vlad Khorsun wrote:
>>> %type  snap_shot
>>> snap_shot
>>> : SNAPSHOT
>>> | SNAPSHOT TABLE
>>> | SNAPSHOT TABLE STABILITY
>>> + | SNAPSHOT SHARING FROM 
>> SNAPSHOT BASED ON 
>> ?
>>
> Do you think with this words it's clear that the new transaction uses
> the same snapshot used in , instead of incorrectly saying that it
> will use  as the snapshot for the new transaction?
>
> Thinking in these terms, I'm more inclined to adapt (renaming SHARING to
> SHARED) Vlad's syntax to:
>
> SET TRANSACTION ISOLATION LEVEL SNAPSHOT SHARED FROM 

   For me both variants are OK :) Probably native english speakers could
offer something better and less ambiguous.

   Also, i offer to add additional rule for transaction which should export
its snapshot data (it allows to avoid export by all transactions), something
like:

   SET TRANSACTION ISOLATION LEVEL SNAPSHOT WITH SHARED SNAPSHOT

Not sure i like it :)

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel 


---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

06.05.2017 0:17, Adriano dos Santos Fernandes wrote:
> Em 05/05/2017 18:01, Vlad Khorsun escreveu:
> 
>>
>> Also, i offer to add additional rule for transaction which should export
>> its snapshot data (it allows to avoid export by all transactions), something
>> like:
>>
>> SET TRANSACTION ISOLATION LEVEL SNAPSHOT WITH SHARED SNAPSHOT
>>
> 
> As we talk about active transactions, why did you want to "export"
> snapshot explicitly instead of share the data already available like I
> did (badly implemented yet, but possible) when requested by who want it?

   To avoid work and resources overhead when it is not required. Agree, snapshot
of a far not every transaction will be ever used by another transaction.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

Em 05/05/2017 18:01, Vlad Khorsun escreveu:

> 
>Also, i offer to add additional rule for transaction which should export
> its snapshot data (it allows to avoid export by all transactions), something
> like:
> 
>SET TRANSACTION ISOLATION LEVEL SNAPSHOT WITH SHARED SNAPSHOT
> 

As we talk about active transactions, why did you want to "export"
snapshot explicitly instead of share the data already available like I
did (badly implemented yet, but possible) when requested by who want it?


Adriano

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

05.05.2017 21:10, Adriano dos Santos Fernandes wrote:
> On 05/05/2017 14:43, Dmitry Yemanov wrote:
>> 05.05.2017 20:01, Vlad Khorsun wrote:
>>> %type  snap_shot
>>> snap_shot
>>> : SNAPSHOT
>>> | SNAPSHOT TABLE
>>> | SNAPSHOT TABLE STABILITY
>>> +   | SNAPSHOT SHARING FROM 
>> SNAPSHOT BASED ON 
>> ?
>>
> Do you think with this words it's clear that the new transaction uses
> the same snapshot used in , instead of incorrectly saying that it
> will use  as the snapshot for the new transaction?
> 
> Thinking in these terms, I'm more inclined to adapt (renaming SHARING to
> SHARED) Vlad's syntax to:
> 
> SET TRANSACTION ISOLATION LEVEL SNAPSHOT SHARED FROM 

   For me both variants are OK :) Probably native english speakers could
offer something better and less ambiguous.

   Also, i offer to add additional rule for transaction which should export
its snapshot data (it allows to avoid export by all transactions), something
like:

   SET TRANSACTION ISOLATION LEVEL SNAPSHOT WITH SHARED SNAPSHOT

Not sure i like it :)

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

05.05.2017 20:36, Adriano dos Santos Fernandes wrote:
> On 05/05/2017 14:01, Vlad Khorsun :

...

>> I don't understand for what purpose tra_oldest_snapshot was added.
> In my understand, it's a property that new transaction should copy from
> the base transaction. Isn't it?

   No. Re-evaluated value of OST is stored at jrd_trans::tra_oldest_active.

> Should I let the original code assign it even for new transactions
> that's sharing a snapshot?

   No.

> But please read answear bellow, may be important for it.
> 
> 
>> Also it is not clear why "base_number" variable is introduced in 
>> transaction_start()
>> and why it replaces "number" in many places.
> 
> When first normal (not with sharing snapshot option) transaction is
> started, it must record others transaction snapshot until its own *number*.
> 
> When second (with sharing snapshot option) transaction is started,
> *number* has its new number too, but the snapshot vector must be record
> only to the *base_number*, i.e., its snapshot vector should be identical
> to the first transaction.

   If you worry about high bound of transaction's copy of TIP 
(tra_transactions) -
it is already stored at jrd_trans::tra_top.

   BTW, transaction should export own snapshot at the end of 
transaction_start(),
because it is the moment when all it fields got actual values (especially
tra_oldest_active).

   Probably, transaction which import snapshot, should avoid recalculation of
OIT and OST. At least, it should not update own tra_oldest_active with new
value of OST.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 05/05/2017 14:43, Dmitry Yemanov wrote:
> 05.05.2017 20:01, Vlad Khorsun wrote:
>> %type   snap_shot
>> snap_shot
>>  : SNAPSHOT
>>  | SNAPSHOT TABLE
>>  | SNAPSHOT TABLE STABILITY
>> +| SNAPSHOT SHARING FROM 
> SNAPSHOT BASED ON 
> ?
>
Do you think with this words it's clear that the new transaction uses
the same snapshot used in , instead of incorrectly saying that it
will use  as the snapshot for the new transaction?

Thinking in these terms, I'm more inclined to adapt (renaming SHARING to
SHARED) Vlad's syntax to:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT SHARED FROM 


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dmitry Yemanov]

05.05.2017 20:01, Vlad Khorsun wrote:
>
> %typesnap_shot
> snap_shot
>   : SNAPSHOT
>   | SNAPSHOT TABLE
>   | SNAPSHOT TABLE STABILITY
> + | SNAPSHOT SHARING FROM 

SNAPSHOT BASED ON 
?


Dmitry


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 05/05/2017 14:01, Vlad Khorsun wrote:
>
>It doesn't forces (nor expresses) new transaction isolation level to be 
> SNAPSHOT.

Yes, I forgot to inform it in comment for now.


> Probably, it would be more natural to extend "snap_shot" rule in parser:
>
> iso_mode
>   : snap_shot
>   | READ UNCOMMITTED version_mode
>   | READ COMMITTED version_mode
>   ;
>
> %typesnap_shot
> snap_shot
>   : SNAPSHOT
>   | SNAPSHOT TABLE
>   | SNAPSHOT TABLE STABILITY
> + | SNAPSHOT SHARING FROM 

I like it.


>Also, transaction_options() should verify\enforce isc_tpb_concurrency 
> isolation
> level for a new transaction.

Yes.


>I don't understand for what purpose tra_oldest_snapshot was added.
In my understand, it's a property that new transaction should copy from
the base transaction. Isn't it?

Should I let the original code assign it even for new transactions
that's sharing a snapshot?

But please read answear bellow, may be important for it.


> Also it is not clear why "base_number" variable is introduced in 
> transaction_start()
> and why it replaces "number" in many places.

When first normal (not with sharing snapshot option) transaction is
started, it must record others transaction snapshot until its own *number*.

When second (with sharing snapshot option) transaction is started,
*number* has its new number too, but the snapshot vector must be record
only to the *base_number*, i.e., its snapshot vector should be identical
to the first transaction.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

05.05.2017 18:59, Adriano dos Santos Fernandes write:
> Vlad and others,
> 
> Can you look at this?
> 
> https://github.com/asfernandes/firebird/tree/work/sharing-snapshot

   I took a quick look, so don't get me too serious. Also, my opinion could be
incomplete and be changed later :)

> It uses the simplest possible (and bad) IPC mechanism to manually test
> the idea of get the snapshot from others processes.

   Yes, IPC\locking is a weak point (so far)

> It uses syntax: SET TRANSACTION SHARING SNAPSHOT FROM  number>

   It doesn't forces (nor expresses) new transaction isolation level to be 
SNAPSHOT.
Probably, it would be more natural to extend "snap_shot" rule in parser:

iso_mode
: snap_shot
| READ UNCOMMITTED version_mode
| READ COMMITTED version_mode
;

%type  snap_shot
snap_shot
: SNAPSHOT
| SNAPSHOT TABLE
| SNAPSHOT TABLE STABILITY
+   | SNAPSHOT SHARING FROM 

   Also, transaction_options() should verify\enforce isc_tpb_concurrency 
isolation
level for a new transaction.

> It is working in my tests.
> 
> Not talking about the IPC mechanism, is there any other trick needed?

   Ok, lets leave IPC\locking in peace for a while ;)

   I don't understand for what purpose tra_oldest_snapshot was added.
Also it is not clear why "base_number" variable is introduced in 
transaction_start()
and why it replaces "number" in many places.

   As for the tricks - so far i see no needs in something additional.
If we will decide to implement Sean's syntax with explicit "export" of 
transaction
snapshot, it could require some, though.

   Of course, IPC\locking should be reworked, but we agreed to not discuss it
at this stage.

Hope it helps,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

Vlad and others,

Can you look at this?

https://github.com/asfernandes/firebird/tree/work/sharing-snapshot

It uses the simplest possible (and bad) IPC mechanism to manually test
the idea of get the snapshot from others processes.

It uses syntax: SET TRANSACTION SHARING SNAPSHOT FROM 

It is working in my tests.

Not talking about the IPC mechanism, is there any other trick needed?


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> Being able to start next transaction,
> basically, from the point where the previous left would be so convenient and
> remove a lot of hacks we have in place right now.

To be clear, in my example/use case the "master transaction" would still be 
active and the related connection would be require to be maintained.

Vlad (or was it Alex) suggested that a feature allowing for "snapshot points" 
to be maintained could be possible, but this would be a separate issue (which 
could use some of the same code/basics).


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Jiří Činčura]

>Did you consider export of the data to local files and then sending of
>these files? 

We did. And discarded it because of the hassle of managing these files.

> File transfer over Internet is much faster.

I'm not uploading over FIrebird's protocol. It's pushed over custom
HTTPs endpoint.

-- 
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

20.04.2017 8:36, Jiří Činčura wrote:
> Sure. There's a part of our system that reads data and sends these over
> the internet. Given the deployments on the customer side the internet is
> often very very slow (<100kBps sometimes), so we decided to not keep
> transaction open, but do it repeatedly in bacches (there are other
> requirements, I don't want to go into too much details). Being able to
> start next transaction, basically, from the point where the previous
> left would be so convenient and remove a lot of hacks we have in place
> right now.

   Did you consider export of the data to local files and then sending of these 
files? 
File transfer over Internet is much faster.


-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 23:27, Leyne, Sean wrote:
> I never said anything about a single application!
>
> I mentioned in one of my posts that the Master connection would request a 
> "Transaction Clone" which would be a GUID that could be sent to another 
> *process* to allow that process' connection to link to the "Master 
> Transaction".

   Indeed, the one who talked about multi-threaded application was Adriano.

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Molnár Attila]

+1 for this feature. I would be very happy for this. Also it would be 
awesmone if this consistent view were accessible later in time (this 
woudl mean garbage collection blocking).

On 2017.04.18. 20:56, Leyne, Sean wrote:
>
>> If you need simultaneous queries - make them possible,
>> what the point of transaction hacking?
> You want a single "view" of the database from multiple _connections_.
>
> There is nothing that provides this, today -- There is no way to ensure that 
> all connections have that same view.
>
>
> Sean
>
> --
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> Firebird-Devel mailing list, web interface at 
> https://lists.sourceforge.net/lists/listinfo/firebird-devel


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Jiří Činčura]

>Could you explain, please ?

Sure. There's a part of our system that reads data and sends these over
the internet. Given the deployments on the customer side the internet is
often very very slow (<100kBps sometimes), so we decided to not keep
transaction open, but do it repeatedly in bacches (there are other
requirements, I don't want to go into too much details). Being able to
start next transaction, basically, from the point where the previous
left would be so convenient and remove a lot of hacks we have in place
right now.

-- 
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> > Really, how do you propose to coordinate the transactions account the
> separate processes (potentially different hosts)?
> 
>What? So far you talked about transactions from one multi-threaded
> application. Where "different hosts" came from all of sudden?

I never said anything about a single application!

I mentioned in one of my posts that the Master connection would request a 
"Transaction Clone" which would be a GUID that could be sent to another 
*process* to allow that process' connection to link to the "Master Transaction".


> > Ad-hoc queries?!!??? Huh???
> >
> > Where was I advocating Ad-Hoc queries?
> 
>There: https://groups.yahoo.com/neo/groups/firebird-
> support/conversations/topics/130409
>  From that conversation I came to conclusion that your system don't use
> prepared statements at all.

I didn't say that the queries had to be ad-hoc.

You can create as many prepared statements as is required for each combination 
of UPDATE you need, then use the statement that meets your need.  

So, you would have a "library" of prepared statements that you could execute in 
the most efficient manner possible.


Sean


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 22:10, Leyne, Sean wrote:
> Again, you are thinking far too narrowly -- trying to define a problem to 
> convenience frame of reference.

   Ok, let me think wider: tell me why you need to move whole tables to other 
server so 
often and so quickly? May be you'd better use continuous replication of changes 
instead of 
repeated transfer of whole tables?
   You said that there is no simple solution for your problems. It means that 
you tried 
something. Did you try to use of stored aggregates or any kind of OLAP? How 
about 
clustering, sharding or other way of horizontal scaling?

> Really, how do you propose to coordinate the transactions account the 
> separate processes (potentially different hosts)?

   What? So far you talked about transactions from one multi-threaded 
application. Where 
"different hosts" came from all of sudden?

> Ad-hoc queries?!!??? Huh???
>
> Where was I advocating Ad-Hoc queries?

   There: 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/130409
 From that conversation I came to conclusion that your system don't use 
prepared 
statements at all.

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

19.04.2017 20:43, Jiří Činčura wrote:
>> once. If they all are started without any commit between them (which can
>> be ensured by a
>> number of different ways), they will give you completely the same view of
>> data.
> 
> I'm listening. That would make my life lot easier in certain scenarios,

   Could you explain, please ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

19.04.2017 17:12, Dimitry Sibiryakov wrote:
> 19.04.2017 15:56, Vlad Khorsun wrote:
>> And it does it already for a many years when fetching records in a batch 
>> (default mode)
> 
> Really? I see in sources that it send op_fetch only when in REM_fetch() 
> it run out of
> data in buffer, not right after receiving batch.

   Look better. Consider condition after "Low in inventory" comment

>> We speak about parallel readers.
> 
> Yes. And I'm trying to understand why so hackish and unclean 
> implementation has favor
> over more versatile ones.

   3 wrong expressions at one statement - it is too much even for you

> Why derived transaction is better than flashback query?


   Because it much more cheap to implement, have much less runtime cost, easy 
to use by
developers and completely fit given task and conditions.

   And its not a "derived" transactions, its sooner "cloned"

Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> > 2- that the cost of any approach to ensure such "alignment" will not be so
> significant that it compromises system performance.
> 
>The simplest method: ON COMMIT trigger waiting for some flag to be
> unset. You set flag, start your transactions and then reset the flag.
> Performance penalty is minimal because a) is applied only to transactions
> that writed to targeted tables; 

Again, you are thinking far too narrowly -- trying to define a problem to 
convenience frame of reference.


> b) not more than time needed to start 5 transactions which is small.

Really, how do you propose to coordinate the transactions account the separate 
processes (potentially different hosts)?


> > Reminder: Nikolay worked for BroadView for several years.  If the solution
> was simple, we wouldn't be having this discussion right now.
> 
>Taking into account that you consider ad-hock queries to be a good solution
> from performance POV, I'm pity for Nikolay who surely did his best to make
> your system work.

Ad-hoc queries?!!??? Huh???

Where was I advocating Ad-Hoc queries?


Sean


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 18:54, Leyne, Sean wrote:
> I have client with over 500 connection to a database, I am pretty sure that:
>
> 1-  there is no way to ensure that all the read-only processes can start a 
> transaction without the possibility that another read/write transaction 
> started in between them, or
>
> 2- that the cost of any approach to ensure such "alignment" will not be so 
> significant that it compromises system performance.

   The simplest method: ON COMMIT trigger waiting for some flag to be unset. 
You set flag, 
start your transactions and then reset the flag. Performance penalty is minimal 
because a) 
is applied only to transactions that writed to targeted tables; b) not more 
than time 
needed to start 5 transactions which is small.

> Reminder: Nikolay worked for BroadView for several years.  If the solution 
> was simple, we wouldn't be having this discussion right now.

   Taking into account that you consider ad-hock queries to be a good solution 
from 
performance POV, I'm sure that Nikolay did his best to make your system work.

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 19:01, Leyne, Sean wrote:
> If the solution was simple, we wouldn't be having this discussion right now.

   BTW, did you considered moving of these tables into separate database that 
it can be 
transferred between servers as whole using nbackup?

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 18:54, Leyne, Sean wrote:
> I have client with over 500 connection to a database, I am pretty sure that:
>
> 1-  there is no way to ensure that all the read-only processes can start a 
> transaction without the possibility that another read/write transaction 
> started in between them, or
>
> 2- that the cost of any approach to ensure such "alignment" will not be so 
> significant that it compromises system performance.

   The simplest method: ON COMMIT trigger waiting for some flag to be unset. 
You set flag, 
start your transactions and then reset the flag. Performance penalty is minimal 
because a) 
is applied only to transactions that writed to targeted tables; b) not more 
than time 
needed to start 5 transactions which is small.

> Reminder: Nikolay worked for BroadView for several years.  If the solution 
> was simple, we wouldn't be having this discussion right now.

   Taking into account that you consider ad-hock queries to be a good solution 
from 
performance POV, I'm pity for Nikolay who surely did his best to make your 
system work.

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Jiří Činčura]

> once. If they all are started without any commit between them (which can
> be ensured by a 
> number of different ways), they will give you completely the same view of
> data.

I'm listening. That would make my life lot easier in certain scenarios,

-- 
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> 2- that the cost of any approach to ensure such "alignment" will not be so
> significant that it compromises system performance.

Ooppps!

That should have read:

2- that the cost of any approach to ensure such "alignment" *will be* so 
significant that it compromises system performance.


Sean

P.S.Reminder: Nikolay worked for BroadView for several years.  If the 
solution was simple, we wouldn't be having this discussion right now.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

>It can. Nothing prevent you from starting several read-only snapshot
> transactions at once. If they all are started without any commit between
> them (which can be ensured by a number of different ways)

Really, how?

I have client with over 500 connection to a database, I am pretty sure that:

1-  there is no way to ensure that all the read-only processes can start a 
transaction without the possibility that another read/write transaction started 
in between them, or

2- that the cost of any approach to ensure such "alignment" will not be so 
significant that it compromises system performance.


Sean


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 16:51, Leyne, Sean wrote:
> The functionality being discussed in this thread; cannot be overcome by a 
> developer

   It can. Nothing prevent you from starting several read-only snapshot 
transactions at 
once. If they all are started without any commit between them (which can be 
ensured by a 
number of different ways), they will give you completely the same view of data.

>  and has real world application.

   So does CORE-5483.

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> 19.04.2017 14:07, Adriano dos Santos Fernandes wrote:
> > Transaction consistency and multiple connections are a sufficient and
> > easier to use feature.
> 
>CORE-5483 is also sufficient and easy to use, but still you called it "a 
> hack"
> with rage.

Because it is a "hack".

You (solely) proposed functionality that can be overcome by a developer (they 
can build a list of prepared statements that include the selected columns they 
want to operate against, and use the appropriate one as required).  

Further, the functionality completely unsupported the SQL standard or any other 
database engine.

The functionality being discussed in this thread; cannot be overcome by a 
developer, and has real world application.


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 15:56, Vlad Khorsun wrote:
>And it does it already for a many years when fetching records in a batch 
> (default mode)

   Really? I see in sources that it send op_fetch only when in REM_fetch() it 
run out of 
data in buffer, not right after receiving batch.

>We speak about parallel readers.

   Yes. And I'm trying to understand why so hackish and unclean implementation 
has favor 
over more versatile ones.
   Why derived transaction is better than flashback query?

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 14:12, Vlad Khorsun wrote:
>It give nothig to readers

   Not quite so. When array binding used with select, client can send request 
for new 
packet before it start tossing record values into user buffers and this way 
work in 
parallel with server and network, reducing total wait time.

>Engine can't run more than one statement in attachment at same time. This 
> will not be
> changed.

   But engine can implicitly start new attachment and derived transaction to 
run this 
parallel statement and get the same result without need for application 
developer to 
explicitly care about it.

>So - no, this features have no advantage over parallel readers.

   On the other hand parallel reader also have no advantages over these 
features and I 
don't see other tasks which they can be better for.

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 14:07, Adriano dos Santos Fernandes wrote:
> Transaction consistency and multiple connections are a sufficient and
> easier to use feature.

   CORE-5483 is also sufficient and easy to use, but still you called it "a 
hack" with rage.


-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

19.04.2017 14:56, Dimitry Sibiryakov wrote:
> 19.04.2017 13:29, Vlad Khorsun wrote:
>> I can't evaluate something not defined. Specify "array DML + 
>> asynchronous queries" and,
>> probably, we will have the subject to speak about.
> 
> Array DML is a way to put/get many records using one API call. In ODBC it 
> is
> https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/binding-arrays-of-parameters
> in Oracle it is 
> https://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci05bnd.htm#i421503

   It give nothig to readers

> Asynchronous queries is a feature when application can send next query to 
> server before
> it ended up with previous query. It can be done from other thread or API call 
> can return
> immediately, not waiting for end of query execution. Currently no SQL server 
> allow to send
> two queries in single connection in parallel, so Firebird can be the first.

   Engine can't run more than one statement in attachment at same time. This 
will not be
changed. So, all potential profit is hiding network latency. Again, it give 
nothing to
readers.

   So - no, this features have no advantage over parallel readers. They could 
be good for
another task's though

Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 19/04/2017 08:56, Dimitry Sibiryakov wrote:
> Currently no SQL server allow to send two queries in single connection
> in parallel, so Firebird can be the first.


There must be a good reason for this, it's not only about hard
implementation.

You'd then need to offer concurrency control for the user inside of the
database/transaction, the same way programmers need then to develop
multi-threaded applications.

Transaction consistency and multiple connections are a sufficient and
easier to use feature.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 13:29, Vlad Khorsun wrote:
>I can't evaluate something not defined. Specify "array DML + asynchronous 
> queries" and,
> probably, we will have the subject to speak about.

   Array DML is a way to put/get many records using one API call. In ODBC it is 
https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/binding-arrays-of-parameters
 
in Oracle it is 
https://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci05bnd.htm#i421503
   Asynchronous queries is a feature when application can send next query to 
server before 
it ended up with previous query. It can be done from other thread or API call 
can return 
immediately, not waiting for end of query execution. Currently no SQL server 
allow to send 
two queries in single connection in parallel, so Firebird can be the first.
   First feature make network data flow denser for one query, second one do the 
same 
filling gaps between packets of one query with packets of other query. Both of 
them 
improve effectivity of Firebird network protocol which used to cause problems 
in hight 
latency networks.

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 19/04/2017 08:29, Vlad Khorsun wrote:
> PS If i understand Sean correctly, he going to read data by few connectins in 
> parallel.
> I think, it could work. Also, i guess, it shoudl be possible to use same 
> "array DML +
> asynchronous queries" from parallel connections too
>
>
Would make things like gbak very fast, with array DML for a single table
and asynchronous queries for different tables.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

19.04.2017 14:03, Dimitry Sibiryakov wrote:
> 19.04.2017 12:57, Vlad Khorsun wrote:
>> Sean speak about *read* consistency within few transactions\connections 
>> to the same
>> database. No more, no less.
> 
> Ok. What advantages can have derived transactions over array DML + 
> asynchronous queries
> which are more versatile?

   I can't evaluate something not defined. Specify "array DML + asynchronous 
queries" and,
probably, we will have the subject to speak about.

Vlad

PS If i understand Sean correctly, he going to read data by few connectins in 
parallel.
I think, it could work. Also, i guess, it shoudl be possible to use same "array 
DML +
asynchronous queries" from parallel connections too

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 12:57, Vlad Khorsun wrote:
>Sean speak about *read* consistency within few transactions\connections to 
> the same
> database. No more, no less.

   Ok. What advantages can have derived transactions over array DML + 
asynchronous queries 
which are more versatile?


-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

19.04.2017 13:34, Dimitry Sibiryakov wrote:
> 19.04.2017 12:23, Vlad Khorsun wrote:
>>>  As in Sean's scenario: you pumped data into 5 tables via 5 connections 
>>> using 5 derived
>>> transactions. Now you need atomically commit all these transactions in all 
>>> connections.
>>> How would you do it?
>> I don't. As there was no such requirement at all. You again look from 
>> replicator's POV -
>> just wrong
> 
> I'm looking from application developer's POV. Sean provided exact task: 
> transfer five
> tables from one database to other as fast as possible in a consistent way. 

   Sean speak about *read* consistency within few transactions\connections to 
the same
database. No more, no less.

 > With usage of
 > "derived transactions" and multiple connections it requires consistent view 
 > of data in
 > source database and atomic commit in target database. Without that it is 
 > possible to end
 > up with four tables containing new data and one tables (in the best case) 
 > empty.

   Even if such requirement exists - nobody prevent you to use 2PC protocol to 
satisfy it.

Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

19.04.2017 12:23, Vlad Khorsun wrote:
>> As in Sean's scenario: you pumped data into 5 tables via 5 connections 
>> using 5 derived
>> transactions. Now you need atomically commit all these transactions in all 
>> connections.
>> How would you do it?
>I don't. As there was no such requirement at all. You again look from 
> replicator's POV -
> just wrong

   I'm looking from application developer's POV. Sean provided exact task: 
transfer five 
tables from one database to other as fast as possible in a consistent way. With 
usage of 
"derived transactions" and multiple connections it requires consistent view of 
data in 
source database and atomic commit in target database. Without that it is 
possible to end 
up with four tables containing new data and one tables (in the best case) empty.
   Of course, as an experienced application developer, I wouldn't use multiple 
connections 
at all (they are just ancient workaround for MS SQL limitations), but in this 
thread we 
are stuck on this "solution".

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

19.04.2017 11:51, Dimitry Sibiryakov пишет:
> 18.04.2017 18:43, Vlad Khorsun wrote:
>> Some time ago there was discussion about sharing snapshots. As for me, it
>> is useful feature. Not a "must have", but useful.
> 
> As in Sean's scenario: you pumped data into 5 tables via 5 connections 
> using 5 derived
> transactions. Now you need atomically commit all these transactions in all 
> connections.
> How would you do it?

   I don't. As there was no such requirement at all. You again look from 
replicator's POV -
just wrong

Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

18.04.2017 18:43, Vlad Khorsun wrote:
>Some time ago there was discussion about sharing snapshots. As for me, it
> is useful feature. Not a "must have", but useful.

   As in Sean's scenario: you pumped data into 5 tables via 5 connections using 
5 derived 
transactions. Now you need atomically commit all these transactions in all 
connections. 
How would you do it?


-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> On 18/04/2017 16:41, Dimitry Sibiryakov wrote:
> > 18.04.2017 21:30, Leyne, Sean wrote:
> >>>With current Firebird architecture - yes. It can be changed.
> >> Really, then why have we been living with that @#$@#$@# limitation for
> more than 10 years!
> >Hmmm... Probably, because it is not limitation for real
> > applications? Or because four developers don't have enough hands and
> > time to make everything and there are more useful features in queue?..
>
> Today applications (not exactly the Firebird marketing, but if Firebird wants
> to advance...) does not care to have a super multi-threaded connection.
> 
> They use a connection and discard for a pool.
> 
> But they need multi-processing and consistency.

Exactly!


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

>    With current Firebird architecture you won't get better performance from 5
> connections.
> Dmitry Kouzmenko some time ago made a video that busted this myth.
> 
> Nope, video was about inserts in multiple connections into one table. This is 
> -
> yes, useless. 1 connection inserts all this data faster.

That is a use case where it would make sense that multiple connections would 
not scale beyond a small number.

But, Dimitry S, that is not the use case I am referring to.


Sean
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[kdv]

Hello! 18.04.2017, 22:22, "Dimitry Sibiryakov" : I want to push data to another database as fast as possible, I want to have 5 connections walk the list of tables and read the rows and push them to the target DB.   With current Firebird architecture you won't get better performance from 5 connections.Dmitry Kouzmenko some time ago made a video that busted this myth. Nope, video was about inserts in multiple connections into one table. This is - yes, useless. 1 connection inserts all this data faster. -- Dmitry Kuzmenko, www.ibase.ru, (495) 953-13-34 

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdotFirebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

18.04.2017 21:54, Adriano dos Santos Fernandes wrote:
> But they need multi-processing and consistency.

   As a workaround for bad performance, no?


-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 18/04/2017 16:41, Dimitry Sibiryakov wrote:
> 18.04.2017 21:30, Leyne, Sean wrote:
>>>With current Firebird architecture - yes. It can be changed.
>> Really, then why have we been living with that @#$@#$@# limitation for more 
>> than 10 years!
>Hmmm... Probably, because it is not limitation for real applications? Or 
> because four 
> developers don't have enough hands and time to make everything and there are 
> more useful 
> features in queue?..
>
Today applications (not exactly the Firebird marketing, but if Firebird
wants to advance...) does not care to have a super multi-threaded
connection.

They use a connection and discard for a pool.

But they need multi-processing and consistency.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

18.04.2017 21:30, Leyne, Sean wrote:
>>With current Firebird architecture - yes. It can be changed.
> Really, then why have we been living with that @#$@#$@# limitation for more 
> than 10 years!

   Hmmm... Probably, because it is not limitation for real applications? Or 
because four 
developers don't have enough hands and time to make everything and there are 
more useful 
features in queue?..

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

18.04.2017 21:13, Leyne, Sean wrote:
> I want to push data to another database as fast as possible, I want to have 5 
> connections walk the list of tables and read the rows and push them to the 
> target DB.

   BTW, for such purpose Oracle have detached tablespaces. You detach such 
tablespace from 
one database and attach it to other. This speed cannot be beaten by transaction 
hacking.


-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

18.04.2017 21:13, Leyne, Sean wrote:
> No -- with a single connection only 1 SQL can be executed at a time -- 
> regardless of the number of threads to your process.

   With current Firebird architecture - yes. It can be changed.

> Scenario:
>
> I want to push data to another database as fast as possible, I want to have 5 
> connections walk the list of tables and read the rows and push them to the 
> target DB.

   With current Firebird architecture you won't get better performance from 5 
connections. 
Dmitry Kouzmenko some time ago made a video that busted this myth.

-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> > You want a single "view" of the database from multiple _connections_.
> 
>I still see no point for _multiple_ connections. Isn't one enough?

No -- with a single connection only 1 SQL can be executed at a time -- 
regardless of the number of threads to your process.

Scenario:

I want to push data to another database as fast as possible, I want to have 5 
connections walk the list of tables and read the rows and push them to the 
target DB.


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

18.04.2017 20:56, Leyne, Sean wrote:
> You want a single "view" of the database from multiple _connections_.

   I still see no point for _multiple_ connections. Isn't one enough?

> There is nothing that provides this, today -- There is no way to ensure that 
> all connections have that same view.

   Oracle have flashback queries. From any connection it show data as they were 
at some 
point of time in the past.


-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

> If you need simultaneous queries - make them possible, 
> what the point of transaction hacking?

You want a single "view" of the database from multiple _connections_.

There is nothing that provides this, today -- There is no way to ensure that 
all connections have that same view.


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Dimitry Sibiryakov]

18.04.2017 18:28, Adriano dos Santos Fernandes wrote:
> 2) A multi-threaded program (may be a future version of gbak) wants to
> dispatch simultaneous queries to Firebird, so need to use more than one
> attachment and transaction and then do not have transaction consistency.

   I don't see logical relation between "wants to dispatch simultaneous 
queries" and "need 
to use more than one attachment". If you need simultaneous queries - make them 
possible, 
what the point of transaction hacking?


-- 
   WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

18.04.2017 21:21, Adriano dos Santos Fernandes wrote:
> On 18/04/2017 15:01, Vlad Khorsun wrote:
>> 18.04.2017 20:21, Adriano dos Santos Fernandes wrote:
>>> On 18/04/2017 13:43, Vlad Khorsun wrote:
  Some time ago there was discussion about sharing snapshots. As for 
 me, it
 is useful feature. Not a "must have", but useful.

  With current implementation of database snapshots (private copy of 
 TIP)
 it is not enough just to specify "base" transaction number to obtain its
 snapshot, especially for Classic.
>>> Can you please explain more?
>> I mean, that "secondary" transaction should obtain somehow the private 
>> copy
>> of (part of) TIP created by the "base" transaction when it started.
>>
>> Read the thread "How to? Coordinating transactions for multiple 
>> connections in
>> single call" started by the Sean at 02 April 2014. It contains all details.
> 
> LOL. I used same term "base transaction number" as now (even thinking
> it's bad) in that thread, and you, Jim and me appeared to have agreed on
> a solution (for the first or single time LOL).

   It happens :)

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 18/04/2017 15:01, Vlad Khorsun wrote:
> 18.04.2017 20:21, Adriano dos Santos Fernandes wrote:
>> On 18/04/2017 13:43, Vlad Khorsun wrote:
>>> Some time ago there was discussion about sharing snapshots. As for me, 
>>> it
>>> is useful feature. Not a "must have", but useful.
>>>
>>> With current implementation of database snapshots (private copy of TIP)
>>> it is not enough just to specify "base" transaction number to obtain its
>>> snapshot, especially for Classic.
>> Can you please explain more?
>I mean, that "secondary" transaction should obtain somehow the private copy
> of (part of) TIP created by the "base" transaction when it started.
>
>Read the thread "How to? Coordinating transactions for multiple 
> connections in
> single call" started by the Sean at 02 April 2014. It contains all details.

LOL. I used same term "base transaction number" as now (even thinking
it's bad) in that thread, and you, Jim and me appeared to have agreed on
a solution (for the first or single time LOL).


>>> But, with new snapshots acounting (based
>>> on commit order, proposed by Nickolay) it will be enough to get just one
>>> number from the "base" transaction.
>>>
>> Is it the post "Statement-level read consistency and intermediate
>> versions GC" in this list or another one?
>Yes, it is. BTW, the current state of that feature could be found there
>
> https://github.com/redsoftbiz/firebird/tree/read_consistency
>
> it is maintained and workable
>
Thanks!


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Adriano dos Santos Fernandes]

On 18/04/2017 13:43, Vlad Khorsun wrote:
>Some time ago there was discussion about sharing snapshots. As for me, it
> is useful feature. Not a "must have", but useful.
>
>With current implementation of database snapshots (private copy of TIP)
> it is not enough just to specify "base" transaction number to obtain its
> snapshot, especially for Classic.

Can you please explain more?


> But, with new snapshots acounting (based
> on commit order, proposed by Nickolay) it will be enough to get just one
> number from the "base" transaction.
>
Is it the post "Statement-level read consistency and intermediate
versions GC" in this list or another one?


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Leyne, Sean]

Adriano,

> 1) A server (not database server) receives a request and dispatch it to others
> servers for extra processing and more than one server need to access the
> same database in a consistent way.
> 
> 2) A multi-threaded program (may be a future version of gbak) wants to
> dispatch simultaneous queries to Firebird, so need to use more than one
> attachment and transaction and then do not have transaction consistency.

I had been thinking about the multi-threaded program problem for a while, but I 
had always thought of the problem from the point of view of a Master 
transaction/connection wanting to create "clone" transaction handles (multiple 
clones allowed) which could then be "farmed out" processes.

The thought being, that it would not be appropriate for just any connection to 
base from any " transaction" but rather than the context have initiated at the 
'source' (this would also ensure that the master/clone links would be simply 
managed by the master).


> I propose a clause to SET TRANSACTION (and its TPB) so user can inform a
> "base transaction number" (better words and syntax are welcome).
> 
> SET TRANSACTION ISOLATION LEVEL SNAPSHOT FROM BASE 100

In my mind, the clone handle would be a GUID that could only be passed in TPB 
to establish the correct context, never set via SQL.


> In this case, 100 must be an active transaction and the snapshot of the new
> transaction will be the same snapshot used when transaction 100 was
> started.

IMO, this is a must -- the transaction rules of the Master transactions must 
apply to all clones.


> New transaction will not get uncommitted changes of transaction 100.

Why not?

IMO, as far as the client and server are concerned the clone is transaction 100.

This would allow for the clone transaction GUID to be passed from process to 
process and allow each to see where the last 'left off'

It also seems the simplest way to manage garbage/version cleanup.  Only the 
Master transaction can be committed -- affecting all clones.


> With this, a program (or a set of coordinated programs) may use same
> Firebird database in multi-threaded / multi-process way preserving read
> transaction consistency.
> 
> New semantics may also be introduced to use non-active transaction as base
> number and a policy to retain some garbage for a period so one can do time-
> lapsed queries. This is, however, out of the scope of this proposal.

But that seem more of a new "freeze point" main transaction than a sub/clone 
transaction.


> 
> 
> Adriano
> 
> 
> --
> Check out the vibrant tech community on one of the world's most engaging
> tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing 
> list,
> web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Start transaction from base transaction

[Vlad Khorsun]

18.04.2017 19:28, Adriano dos Santos Fernandes wrote:
> Hi!
> 
> In distributed systems there is a problem to use Firebird and maintains
> read consistency.
> 
> Imagine follow situations:
> 
> 1) A server (not database server) receives a request and dispatch it to
> others servers for extra processing and more than one server need to
> access the same database in a consistent way.
> 
> 2) A multi-threaded program (may be a future version of gbak) wants to
> dispatch simultaneous queries to Firebird, so need to use more than one
> attachment and transaction and then do not have transaction consistency.
> 
> I propose a clause to SET TRANSACTION (and its TPB) so user can inform a
> "base transaction number" (better words and syntax are welcome).
> 
> SET TRANSACTION ISOLATION LEVEL SNAPSHOT FROM BASE 100
> 
> In this case, 100 must be an active transaction and the snapshot of the
> new transaction will be the same snapshot used when transaction 100 was
> started.
> 
> New transaction will not get uncommitted changes of transaction 100.
> 
> With this, a program (or a set of coordinated programs) may use same
> Firebird database in multi-threaded / multi-process way preserving read
> transaction consistency.
> 
> New semantics may also be introduced to use non-active transaction as
> base number and a policy to retain some garbage for a period so one can
> do time-lapsed queries. This is, however, out of the scope of this proposal.

   Some time ago there was discussion about sharing snapshots. As for me, it
is useful feature. Not a "must have", but useful.

   With current implementation of database snapshots (private copy of TIP)
it is not enough just to specify "base" transaction number to obtain its
snapshot, especially for Classic. But, with new snapshots acounting (based
on commit order, proposed by Nickolay) it will be enough to get just one
number from the "base" transaction.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel