Oracle TPC Benchmarks and "discrete transactions"

Note cross-posting.

Two months ago Oracle announced version 7 of the Oracle database system (Oracle7). At the same time, Oracle (and others using Oracle7) produced TPC-A and TPC-B benchmark results to demonstrate Oracle7 performance. The benchmark results show dramatically higher performance than previous results run with Oracle version 6. (For example, with TPC-B on a VAX 6000-560, the tps rate more than doubled from 153 tpsB to 315 tpsB.) Some performance changes probably resulted from internal improvements in Oracle. In addition, though, significant changes were made in the way the benchmark was implemented between Oracle 6 and 7. For example, the ACCOUNT table was changed from b-tree to hashed-key access. A more interesting change was the use of a new feature in Oracle7 called "discrete transactions".

Oracle documentation explains discrete transactions as follows:

• Discrete transactions improve the performance of short, non-distributed transactions and streamline transaction processing so short transactions can execute more rapidly.
• Discrete transactions are useful for transactions that:
  • modify only a few database blocks
  • change each database block only once per transaction
  • do not modify data likely to be requested by long-running queries
  • do not need to see the old value of data before modifying the data
• Discrete transactions can only be used for a limited subset of all transactions. However, for sophisticated users with large databases, where speed is the most critical factor, the performance improvements can make working within the design constraints worthwhile.

It seems that discrete transactions are very well suited to the TPC-A and TPC-B transaction! Discrete transactions have many limitations which do not apply to regular Oracle transactions. Some of these are:

1. Discrete transactions cannot see their own changes. A discrete transaction fails if it attempts to access the same row twice.
2. Discrete transactions cannot do "select for update". This is a fundamental requirement to support repeatable reads in the Oracle model. Consequently, discrete transactions cannot be used with buried updates.
3. Discrete transactions incapacitate Oracle's ability to do read-consistent queries without locking. Oracle normally achieves read consistency by looking at the roll back segment for the previous version of a row in the event it has changed since the start of the query. One of the optimizations of discrete transactions is that no undo information is generated. Consequently, a query which tries to read a row which has been modified by a discrete transaction at any time since the query began will fail with a "snapshot too old" error. This is a fatal error causing the transaction to be aborted. Effectively, this breaks a key advantage and selling point of Oracle -- the ability to mix long running queries with update transactions.
4. Because of #2 & #3, the two primary mechanisms for achieving read consistency in Oracle don't work with discrete transactions. The only way to prevent inconsistent reads is to abort conflicting transactions.
5. Discrete transactions cannot modify both tables involved in a referential integrity constraint. (see below in "Usage Notes" for more explanation of this)
6. Discrete transactions cannot be distributed.
7. Discrete transactions require that the end-user understand how records are physically arranged in the database to know that a transaction will not change more than one record in any given database block. This requires a highly sophisticated understanding of the database internals.
8. Discrete transactions require "creativity" to do even simple transactions: in TPC-A, a trigger was used to retrieve the account balance after updating it because it was not possible to use a select statement. (see point #1 above)

This raises many interesting questions, which I've broken out as they may apply to the different net groups.

comp.databases & comp.databases.theory:

• Are discrete transactions a good idea?
• Is this an innovation that other database vendors should pursue?
• Given their limited nature and the user sophistication required, do discrete transactions make sense in the relational model?

comp.benchmarks:

• Should discrete transactions be used in benchmarks?
• Will the performance improvements achieved on TPC-A and TPC-B benchmarks (2x) from Oracle 6 to 7 be seen by typical Oracle users?
• If not, does this undermine the usefulness of TPC-A and TPC-B as a meaningful metric across hardware and software platforms?
• Are discrete transactions a database innovation or a performance gimmick?

comp.databases.oracle:

• Will Oracle users use discrete transactions?
• Is the performance achieved using discrete transactions representative of the performance improvements typical users will see moving from Oracle 6 to 7?
• Do real world applications work within the restrictions of discrete transactions?

  ---

   Additional information from Oracle manual    ("Application Developer's Guide"):

+  How Discrete Transactions Work
+  ------------------------------
+
+  During a discrete transaction, all changes made to any data are
+  deferred until the transaction commits.  Note that other concurrent
+  transactions are unable to see the uncommitted changes of a
+  transaction during normal transaction processing or during the
+  processing of discrete transactions.
+
+  Redo information is generated, but is stored in a separate location in
+  memory.  When the transaction issues a commit request, the redo
+  information is written to the redo log file (along with other group
+  commits) and the changes to the database block are applied directly to
+  the block.  Control is returned to the application, once the commit
+  completes.  This eliminates the need to generate undo information
+  since the block is not actually modified until the transaction is
+  committed, and the redo information is stored in the redo log buffers.
+
+  Undo information generated by regular transactions is used to
+  re-create old versions of data for queries that require a consistent
+  view of the data.  Because no undo information is generated for
+  discrete transactions, a discrete transaction that starts and
+  completes during a long query can cause the query to receive the
+  "snapshot too old" error if the query requests data changed by the
+  discrete transaction.
+
+  In order to use the BEGIN_DISCRETE_TRANSACTION procedure, the
+  DISCRETE_TRANSACTION_ENABLED initialization parameter must be set to
+  TRUE.  If this parameter is set to FALSE, all calls to
+  BEGIN_DISCRETE_TRANSACTION are ignored and transactions requesting
+  this service are handled as regular transactions.  Refer to Appendix E
+  of the ORACLE7 Server Administrator's Guide for more information about
+  setting initialization parameters.
+
+  Errors During Discrete Transactions
+  -----------------------------------
+
+  Any errors encountered during processing of a discrete transaction
+  cause the pre- defined exception DISCRETE_TRANSACTION_FAILED to be
+  raised.  These errors include the failure of a discrete transaction to
+  comply with the usage notes outlined below.  (For example, calling
+  BEGIN_DISCRETE_TRANSACTION after a transaction has begun, or
+  attempting to modify a database block more than once during a
+  transaction, raises the exception.)
+
+  Usage Notes
+  -----------
+
+  The BEGIN_DISCRETE_TRANSACTION procedure must be called before the
+  first statement in a transaction.  The call to this procedure is
+  effective only for the duration of the transaction (that is, once the
+  transaction is committed or rolled back, the next transaction is
+  processed as a normal transaction).
+
+  Transactions that use this procedure cannot participate in distributed
+  transactions.
+
+  Because discrete transactions cannot see their own changes, a discrete
+  transaction cannot perform inserts or updates on both tables involved
+  in a referential integrity constraint.  For example, assume the EMP
+  table has a foreign key constraint on the DEPTNO column that refers to
+  the DEPT table.  A discrete transaction cannot attempt to add a
+  department into the DEPT table and then add an employee belonging to
+  that department because the department is not added to the table until
+  the transaction commits and the integrity constraint requires that the
+  department exist before an insert into the EMP table can occur.  These
+  two operations must be performed in separate discrete transactions.
+
+  Since discrete transactions can change each database block only once,
+  certain combinations of data manipulation statements on the same table
+  are better suited for discrete transactions than others.  One INSERT
+  statement and one UPDATE statement used together are the least likely
+  to affect the same block.  Multiple UPDATE statements are also
+  unlikely to affect the same block depending on the size of the
+  affected tables.  Multiple INSERT statements (or INSERT statements
+  that use queries to specify values), however are likely to affect the
+  same database block.
+
+  Multiple DML operations performed on separate tables are highly
+  unlikely to affect the same database blocks, unless the tables are
+  clustered.
+
+  Because a discrete transaction cannot see its own changes nor modify
+  the same block twice, a SELECT FOR UPDATE statement and the subsequent
+  update statement is not supported for discrete transactions.  If you
+  are using the BEGIN_DISCRETE_TRANSACTION procedure and want to ensure
+  that the data does not change between the time it is read and when it
+  is updated, you must either lock the table being accessed in share
+  mode, or you must be certain that concurrent transactions do not
+  attempt to modify data read by the discrete transaction.
+
+
======================================================================
======================================================================

Charles Levine
email: levine_charles_at_tandem.com

The opinions expressed are my own and
not necessarily those of my employer.