>>> On Wed, Mar 1, 2006 at 11:02 am, in message
<1141232565.27729.379.camel@localhost.localdomain>, Simon Riggs
<simon@2ndquadrant.com> wrote:
> On Wed, 2006- 03- 01 at 10:22 - 0600, Kevin Grittner wrote:
>> >>> On Tue, Feb 28, 2006 at 7:22 am, in message
>> <1141132955.27729.119.camel@localhost.localdomain>, Simon Riggs
>> <simon@2ndquadrant.com> wrote:
>
>> We use serializable transactions heavily; our whole middle tier
>> architecture depends on having that transaction isolation level for
all
>> requests which modify data. (You probably don't want to hear the
>> details.)
>
> *I* would, but others may not. ;- )
An executive overview of our environment, with enough detail to
constitute more than vague hand waving, would probably be at least 4K of
text. If you're interested, I could write something up and post it
somewhere, but this list doesn't seem to be the appropriate place.
Where would be?
The general hand waving overview: We've got about 100 databases with a
lot of fancy portability features which allow real time data replication
in a heterogeneous environment. 3,000 directly connected users, dozens
of queue-based interfaces (in both directions) with business partner
agencies, and a web site with about 2 million hits per day which query
from the databases (when you count both SOAP and browser traffic). So
far four of our databases are on PostgreSQL, with another four being
converted over the next few days. The servers spread around the state
will be converted more gradually, over the course of the next year or
so. Software is almost entirely Java, and mostly home-grown. All
database access is done through JDBC from a middle tier database service
which treats each client request as one (and only one) database
transaction so that serialization errors can be handled automatically.
Queries are written in an approved subset of standard ANSI SQL, with our
query tool parsing that and turning it into Java classes which use
"lowest common denominator" SQL code, with Java code to handle all
procedural steps. Since stored procedures and triggers are implemented
in our Java code, we can see all data flow, allowing us to copy the
transactions to audit databases and replicate the data from multiple
sources to multiple targets. It is easy to show a number of ways that
we will have data integrity problems if the JDBC requests from the
trigger code aren't in a SERIALIZABLE transaction with the triggering
data modification.
Now, aren't you sorry you asked? ;-)
-Kevin