Hello, Clearly, I shouldn't actually use these transactions unless I have to, and in cases where I do use it, I'd expect the completion of the transaction to depend on the speed of all participating databases in the transaction, but are there any additional overheads which might come with a significant time penalty that might commonly be overlooked by someone like me with no previous experience with two-phase commit (2PC)? --- The application: I'm evaluating a design for a database scheme where the nation is partitioned into small areas (e.g. 2km squares), each area serviced solely by its own dedicated database. All queries are locally pinpointed, with a limited search radius, and the database enclosing the centre is responsible for executing the query. The only issue is to ensure that a query near a boundary between two adjacent areas behaves as though there was no partitioning. To do this, I'm looking into using 8.1's new 2PC to allow me to selectively copy data inserted near a boundary into the adjacent neighbouring databases, so that this data will appear in boundary searches carried out by the neighbours. The percentage of inserts which are copied into neighbours is intended to be roughly 25%, most of which involve just a single copy. My scheme intends to ensure that all the databases are able to fit entirely in RAM, and in addition, the amount of data in each database will be relatively small (and therefore quick to sift through). Inserted data is 'small', and most of the time, each database is servicing read requests rather than writing, updating or inserting. A single nationwide database would be logically simpler, but in my case, the application is a website, and I want a hardware solution that is cheap to start with, easily extensible, allows a close coupling between the apache server responsible for a region and the database it hits. Any insights gratefully received! Andy Ballingall
