>>> On Wed, Mar 1, 2006 at 11:02 am, in message <[EMAIL PROTECTED]>, Simon Riggs <[EMAIL PROTECTED]> wrote: > On Wed, 2006- 03- 01 at 10:22 - 0600, Kevin Grittner wrote: >> >>> On Tue, Feb 28, 2006 at 7:22 am, in message >> <[EMAIL PROTECTED]>, Simon Riggs >> <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org