Sean, Em 1/4/2014 19:59, Leyne, Sean escreveu: > All, > > Consider a large database (200GB, large tables with 450 Million rows) which > is running on a kick-a** server with pool of enterprise SSDs for storage > (more IOPS then Firebird could ever use), which I need to extract data from > on a regular basis throughout the day for use by an external BI system. > During the business day, the database is Live with over 300 connections > active with 1 million+ DB transactions. > > I need to extract the BI data as a true "snapshot" of data (ensuring FKs are > valid), in as short a timeframe as possible. > > Because runtime is critical, I want to break the extract process into logical > pieces and run each piece is a separate process/thread (with its own > connection) (aka run in parallel). > > Here is the problem: > > - I need to be able to open the connections/start transactions in such a > way that no other transaction can be committed until all my transactions have > been started. > - I cannot allow for an existing transaction to be committed that could > "invalidate" my snapshot, by making changes visible to a "middle" transaction > which is just about to start. > > As far as I can tell, there is no way for this to be done. > > In my "ideal world": > - I would have each extract process/thread open its own database > connection and pass the connection handle/ID to a central coordinator process > - This coordinator would then call a new "StartTransactionCoordinated" > method and pass the list of Connection handles/IDs to which the transactions > would be created for > - The engine would then create a transaction for each connection, but > ensuring that no new transaction can be committed during > - The assignment of transaction to connection would be at the engines > discretion > - The transactions would then follow the usual lifetime, committing > independently from each other. > > I think that my requirements and proposed approach/solution could be a > significant benefit for use cases where operations can occur in parallel - > eg. database replication (or data pump) where multiple connections could be > used to extract (or apply) changes/data in parallel. > > > What do folks think? > > Am I dreaming? > > > Sean > >
I know very little about your requirements.... But with the provided information I could think in an approach.. but I am not sure how it will behave... Take a Look: The starting process set a flag, that an on before commit trigger checks and if it's set wait to commit Since the time to open all connections for each thread and start the transaction will be very short, it could solve your problem and not freeze all the transactions for a long time. After all the threads starts it's own transaciton the main process reset the flag, and all transactions would be allowed to commit. Since it's a not so often process the tiem when the on before commit trigger will freeze will be very rare You will need a transaction free object (external table, generator or system context variable) What do you think ? see you ! ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
