Quantcast

Start transaction from base transaction

classic Classic list List threaded Threaded
66 messages Options
1234
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
In reply to this post by Leyne, Sean
19.04.2017 1:09, Leyne, Sean wrote:
> 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!

   The same way as you now access whole "live" database during backup: ALTER TABLESPACE
BEGIN BACKUP, copy it by any utility, ALTER TABLESPACE END BACKUP. Then just fix the file
and attach to target database.

>> They use a connection and discard for a pool.
>> But they need multi-processing and consistency.
>
> Exactly!

   Ok, can subj provide consistency across databases on different servers? Would it allow
to query several servers as if they were one and get consistent results? I.e. can I start
transaction on one server based on transaction on different server and see consistent data
view if I feed data from one table on one server and from other table on second server?


--
   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
|  
Report Content as Inappropriate

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
In reply to this post by Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
In reply to this post by Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

Vlad Khorsun-2
In reply to this post by Dimitry Sibiryakov-3
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

Dimitry Sibiryakov-3
In reply to this post by Vlad Khorsun-2
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[SPAM] Re: Start transaction from base transaction

Vlad Khorsun-2
19.04.2017 16:35, Dimitry Sibiryakov wrote:
> 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.

   And it does it already for a many years when fetching records in a batch (default mode)

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

   Sure, it also could start new connection at applicatin side to have new channel for
delivering data. It also could process that data.

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

   We speak about parallel readers. If you want to speak about something else - no problem,
start another thread and don't spam this one.


Vlad

PS Ah, almost forget: also, engine could make an array of cup's of coffee for you ...
    asynchronously, of course ;)


------------------------------------------------------------------------------
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
|  
Report Content as Inappropriate

Re: Start transaction from base transaction

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

Re: Start transaction from base transaction

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


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

Re: Start transaction from base transaction

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

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

Re: 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
1234
Loading...