Best Practices from Oracle Development's A‑Team

The Parking Lot Pattern

Greg Mally
Consulting Solutions Architect

The parking lot pattern is a strategy in Oracle SOA Suite to stage data in an intermediary store prior to complete processing by SOA Suite itself.  This pattern was spearheaded years ago by Deepak Arora and Christian Weeks of the Oracle SOA A-Team.  It has been implemented in various verticals to address processing challenges which include batch, complex message correlation/flows, throttling (see Throttling in SOA Suite via Parking Lot Pattern), etc.  To detail the pattern, this write-up discusses the components of a batch-related implementation.

The Parking Lot

The implementation of the “parking lot” can be done using various storage technologies like JMS, database, or Coherence (just to mention a few).  However, Oracle strongly recommends that a database table be used for simplicity.  The table structure typically contains state tracking and metadata relating to the payload that will be processed.  In our batch-processing example the table would contain: a row identifier column, a batch identifier column, a state column, maybe a type identifier column, maybe a priority indicator column, and finally the data/payload column.




Special properties

LOT_ID The identifier for the parking lot row. Usually some sort of sequence identifier. Primary key for the table
BATCH_ID An identifier for the batch. It would be shared across all rows within the batch.  
STATE The state of the row: commonly this is a single character representing the states the row transitions through.  This field is usually used by the database adapter's polling functionality as a “logical delete” indication. Example values:
N: new
R: reserved
P: processing
C: complete
SUBTYPE (optional) An optional subtype indicator: some sort of meta property about the input row. Note: don't overload this to process both new orders and bulk inventory updates.  There should be separate parking lots for truly separate types.
PRIORITY (optional) An optional priority indication to allow the database adapter to pull these rows first for processing.  
DATA (alternative 1) A CLOB containing a string of the data in XML form. See discussion
DATA (alternative 2) This would be a reference to data populated elsewhere in the system. For example, the order could be stored in a separate “pending orders” table and this could be an identifier for that other row. See discussion
DATA (alternative 3) Inline the data as columns directly within the parking lot table (combine the table from alternative 2 with the parking lot table, effectively). See discussion


Some things to note:

  • There should be one parking lot per general type, do not overload a single parking lot with multiple types (for example orders and inventory updates).
  • The parking lot table is anticipated to be busy. Ensure you clean up stale data through regular purging.

Data Representation Within the Parking Lot

There are at least three possible alternatives for storing the actual data within the parking lot.  Each option has different properties that need to be considered:

1. Store the data as a CLOB in XML form. This is the simplest approach, especially for complex data types. It adds some additional overhead writing and reading the CLOB as well as transforming between the XML and the CLOB. Note that these costs would be associated with XMLTYPE as well, and since there is no need for visibility into this while data it is in the database, it doesn't provide any benefit.
2. Store the data separately in other tables with fully realized columns. This solution is most appropriate if the application is already doing it. That is, if the de-batching process is already copying the input payload to a tabular format in the database table, then this data format could be leveraged for the parking lot.
3. Combine the table that might otherwise exist in #2 with the parking lot itself. While this solution might prove to be the most performant, it can only work for simple data structures in the parking lot.

Database Adapter Usage

The parking lot process would be implemented as a SOA composite with a database adapter and a BPEL process.  The database adapter would read and dispatch individual rows to the BPEL process, creating an instance per order.

The database adapter supports various polling strategies.  Oracle recommends using the “logical delete” strategy, whereby a particular value of the STATE column would be asserted as part of the polling operation: SELECT <column list> FROM PARKING_LOT WHERE STATE=’N’.  The query is additionally enhanced with pessimistic locking function that allow for parallel execution from many separate nodes simultaneously—allowing this to work seamlessly in a cluster. Finally, a “reserved value” should be specified for full distributed polling support (the reserve value is updated during the poll so that the row is no longer a candidate on other nodes, until the transaction can complete).

There is an alternative database polling approach known as “SKIP LOCKING” (see http://docs.oracle.com/cd/E21764_01/integration.1111/e10231/adptr_db.htm#BGBIJHAC and DB Adapter – Distributed Polling (SKIP LOCKED) Demystified ).  While the skip locking approach has several advantages, it does not allow the intermediate states to be committed to the database.  The result is that it does not give the same stateful visibility to other processes that may be interested in the current state within the parking lot; for example, an OSB status monitoring service that provides the user with a means to check the status of the batch they submitted.

The database adapter supports various tuning properties that give very fine-grain control over its behavior, such as the number of poller threads, the number of rows to read per cycle, the number of rows to pass to the target BPEL process, and so on.  For more information about the database adapter, please refer to http://docs.oracle.com/cd/E21764_01/integration.1111/e10231/adptr_db.htm.  The Oracle Fusion Middleware Performance and Tuning Guide also covers database adapter tuning at http://docs.oracle.com/cd/E21764_01/core.1111/e10108/adapters.htm#BABDJIGB.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha