Start transaction from base transaction

classic Classic list List threaded Threaded
86 messages Options
12345
Reply | Threaded
Open this post in threaded view
|

Start transaction from base transaction

Adriano dos Santos Fernandes-3
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.


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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Leyne, Sean
In reply to this post by Adriano dos Santos Fernandes-3
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Adriano dos Santos Fernandes-3
In reply to this post by Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Vlad Khorsun-2
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.

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

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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Adriano dos Santos Fernandes-3
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
In reply to this post by Adriano dos Santos Fernandes-3
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
Reply | Threaded
Open this post in threaded view
|

Re: 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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
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
Reply | Threaded
Open this post in threaded view
|

Re: 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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Leyne, Sean


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

Really, then why have we been living with that @#$@#$@# limitation for more than 10 years!


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

I find that almost impossible to believe.

The advent of high IOPs Flash/SSD and PCIe/SSD which are capable of 100K's of read IOs, would mean that disk would be able to provide as much more data than even 64 processes/thread could handle.


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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
In reply to this post by Leyne, Sean
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
In reply to this post by Leyne, Sean
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Adriano dos Santos Fernandes-3
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Dmitry Kuzmenko-3
In reply to this post by Dimitry Sibiryakov-3
Hello!
 
18.04.2017, 22:22, "Dimitry Sibiryakov" <[hidden email]>:
 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/slashdot
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
In reply to this post by Adriano dos Santos Fernandes-3
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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Leyne, Sean
In reply to this post by Dmitry Kuzmenko-3

>    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
Reply | Threaded
Open this post in threaded view
|

Re: Start transaction from base transaction

Leyne, Sean
In reply to this post by Dimitry Sibiryakov-3


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

Please stay on point!!!!

How would detaching tablespace provide any solution to the use case, I am referring to?!!!!?????

The tables I want to access would be "live" with activity from other connections!


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
12345