OLTP in a J2EE Container World
Let's Start Over
Prior to the advent of the J2EE Container good application design always isolated transient database errors within lower layers of the application and didn't expose these errors to the business logic, or the user. Unfortunately we live in an imperfect world and so our applications see deadlocks, lock timeouts, and stale database connections.
Now in the brave new world of J2EE the model doesn't really take into account handling these transient errors at a low level, they percolate up to the business logic. If a developer is using a CMP entity bean then the client of the bean has to handle any retry or error handling for these transient errors. At least if the developer is using a BMP entity bean then they can build some logic into their bean. Of course if the transactions cuts across beans then the logic needs to be either in the facade layer, or at the client (sevrlet or fat client).
This is not good application layering. One place where this logic could be embedded away from the application developer is inside a J2EE Connector. The connector could handle retry on transient errors without any changes in the application logic at all. Of course this may lead to a somewhat complex connector as it needs to track the various SQL statements issued as part of the transaction in order to replay them in the event of a retry.Fortunately we can simplify the problem through another time honored piece of application design. Minimizing access to the database is another aspect of good OLTP system design. This for the most part rules out the Entity bean model of load and store, and replaces it with a stored procedure which performs the update. Of course there are times when the business logic needs to be in the middle tier and the price must be paid to move data across a network boundary two times. The key is to carefully consider data access patterns and not move data into the middle tier piecemeal, or for the sole purpose of sending it back to the database.
Given that now we want to use stored procedures and we want to track the SQL statements issued to transactions within a connector, that gets us to the point where we could use the Command Pattern for the connections within the connector. That provides a good model for implementing redo and provides a clean encapsulation of the transaction as an object. We can build a facade using Stateless session beans which issue these transactions against the database using our reliable connector. That will lead to more robust and performant applications.