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

Jiří Činčura-2
> 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
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 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
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 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
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 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
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


> > 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
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 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
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 Jiří Činčura-2
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
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 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Re: Start transaction from base transaction

Jiří Činčura-2
In reply to this post by Vlad Khorsun-2
>    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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Start transaction from base transaction

Molnár Attila
In reply to this post by Leyne, Sean
+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
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 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
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 Jiří Činčura-2
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[SPAM] Re: Start transaction from base transaction

Vlad Khorsun-2
In reply to this post by Jiří Činčura-2
20.04.2017 9:36, Jiří Činčura wrote:

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

   "start next transaction, basically, from the point where the previous left"

   This statement i not understand. Do you speak about transaction ? Or about
statement\cursor ? If later, why do you can't remember id of last fetched row
and start next fetch from this position ?

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

Re: Start transaction from base transaction

Jiří Činčura-2
In reply to this post by Dimitry Sibiryakov-3
>    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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [SPAM] Re: Start transaction from base transaction

Jiří Činčura-2
In reply to this post by Vlad Khorsun-2
>    This statement i not understand. Do you speak about transaction ? Or
>    about

Transaction.

> statement\cursor ? If later, why do you can't remember id of last fetched
> row
> and start next fetch from this position ?

That's what we do now, in a nutshell. The problem is of course to have
these IDs and related data (to prevent FK violations). I can explain it
to you in better detail, probably rather outside the list.

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

Parallel execution (was: 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:
>    Engine can't run more than one statement in attachment at same time. This will not be
> changed.

   Does it also mean that Firebird will never be able to execute parts of single query in
parallel even in the simplest cases like "select f from t1 union select f from t2"?

--
   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 Jiří Činčura-2


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

Re: Parallel execution (was: Start transaction from base transaction)

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

> >    Engine can't run more than one statement in attachment at same
> > time. This will not be changed.
>
>    Does it also mean that Firebird will never be able to execute parts of single
> query in parallel even in the simplest cases like "select f from t1 union select f
> from t2"?

Please stop trying to "warp" statement that others are making!

"select f from t1 union select f from t2" is ONE statement with 2 parts!

"Attachment" refers to the connection between a client and the server, it does not imply anything about how the engine might decompose a statement or how the parts could/would be executed!


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: Parallel execution

Vlad Khorsun-2
In reply to this post by Dimitry Sibiryakov-3
20.04.2017 13:04, Dimitry Sibiryakov wrote:
> 19.04.2017 14:12, Vlad Khorsun wrote:
>>     Engine can't run more than one statement in attachment at same time. This will not be
>> changed.
>
>     Does it also mean that Firebird will never be able to execute parts of single query in
> parallel

   No, it doesn't

> even in the simplest cases like "select f from t1 union select f from t2"?

   This case nor simplest nor better for parallel execution than "ordinary" "select * from t".

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