-= ACID Properties =-

   The ACID (Atomicity, Consistency, Isolation, and Durability) properties
of transaction processing systems must be supported by the system under test
during the timed portion of this benchmark. Since TPC-H is not a transaction
processing benchmark, the ACID properties must be evaluated outside the timed
portion of the test.

1. Atomicity Requirements

   The system under test must guarantee that transactions are atomic; the
system will either perform all individual operations on the data, or will assure
that no partially completed operations leave any effects on the data.


1.1. Atomicity of the Completed Transactions

   Perform the ACID Transaction for a randomly selected set of input data
and verify that the appropriate rows have been changed in the ORDERS, LINEITEM,
and HISTORY tables.

The following steps were performed to verify the atomicity of the completed ACID
transactions:
   1. The total price from the ORDERS table and the extended price from the
      LINEITEM table were retrieved for a randomly selected order key.
   2. One ACID Transaction was performed using the order key from step 1.
   3. The ACID Transaction was committed.
   4. The total price from the ORDERS table and the extended price from the
      LINEITEM table were retrieved for the same order key.
   5. It was verified that the appropriate rows had been changed.


1.2. Atomicity of Aborted Transactions

   Perform the ACID Transaction for a randomly selected set of input data,
substituting a ROLLBACK of the transaction for the COMMIT of the transaction.
Verify that the appropriate rows have not been changed in the ORDERS, LINEITEM,
and HISTORY tables.

The following steps were performed to verify the atomicity of the completed ACID
transactions:
   1. The total price from the ORDERS table and the extended price from the LINEITEM
      table were retrieved for a randomly selected order key.
   2. One ACID Transaction was performed using the order key from step 1. The
      transaction was stopped prior to the commit.
   3. The ACID Transaction was rolled back.
   4. The total price from the ORDERS table and the extended price from the LINEITEM
      table were retrieved for the same order key.
   5. It was verified that the appropriate rows had not been changed.
      Consistency Requirements


2. Consistency Requirements

   Consistency is the property of the application that requires any execution of
transactions to take the database from one consistent state to another. A
consistent state for the TPC-H database is defined to exist when:

   O_TOTALPRICE = SUM(trunc(trunc(L_EXTENDEDPRICE*(1-L_DISCOUNT),2)*(1+L_TAX),2))

   For each ORDER and LINEITEM defined by (O_ORDERKEY = L_ORDERKEY).

2.1 Consistency Test
   Verify that ORDERS and LINEITEM tables are initially consistent, submit the
prescribed number of ACID Transactions with randomly selected input parameters,
and re-verify the consistency of the ORDERS and LINEITEM.

The following queries were executed before and after the durability tests to
show that the database was always in a consistent state both initially and after
submitting transactions:

   SELECT * FROM 
   (
      SELECT o_orderkey, 
             o_totalprice ? sum(trunc(trunc(l_extendedprice * (1-l_discount),2)*(1+l_tax),2)) part_res 
       FROM orders, lineitem 
       WHERE o_orderkey=l_orderkey 
       GROUP BY o_orderkey, o_totalprice 
   ) WHERE not part_res=0;

EXASolution has serializable isolation level with table level lock concurrency
control. The ACID Transaction of stream0 was expanded with 5 seconds delay after
the update and before commit. Since only one update transaction can execute at
any one time, the delay should guaranty that the active update transaction is
"in-flight" at the time of the failure. To improve the throughput of the
concurrent streams a random delay was used after COMMIT (0-5 seconds) and after
transaction conflicts (0-2 seconds).

The following steps were performed to verify the consistency of ACID
transactions:
   1. The consistency of the ORDERS and LINEITEM tables was verified.
   2. 1000 transactions for each of the 11+1 execution streams were prepared.
   3. After that at least 100 ACID transactions were submitted from each of 11+1
      execution streams.
   4. A durability failure was induced by pulling a disk drive or switching off the
      power to one or more servers. 
   5. The consistency of the ORDERS and LINEITEM tables was re-verified.
      Isolation Requirements

3. Isolation Requirements
   Operations of concurrent transactions must yield results, which are
indistinguishable from the results, which would be obtained by forcing each
transaction to be serially executed to completion in some order.

The steps of the isolation tests were adopted to the EXASOL isolation
environment.

3.1. Isolation Test 1 ? Read-Write Conflict with Commit
Demonstrate isolation for the read-write conflict of a read-write transaction
and a read-only transaction when the read-write transaction is committed

The following steps were performed to satisfy the test of isolation for a
read-only and a read-write committed transaction:
   1. Start a query and verify that the row was retrieved.
   2. Start an update transaction, read and update the same row. Wait before commit.
   3. Start the same query and verify that the row retrieved has not changed.
   4. Commit the update transaction
   5. Start the same query and verify that the new row is retrieved

3.2. Isolation Test 2 ? Read-Write Conflict with Rollback
Demonstrate isolation for the read-write conflict of a read-write transaction
and a read-only transaction when the read-write transaction is rolled back.

The following steps were performed to satisfy the test of isolation for a
read-only and a rolled back read-write transaction:
   1. Start a query and verify that the row was retrieved.
   2. Start an update transaction, read and update the same row. Wait before commit.
   3. Start the same query and verify that the row retrieved has not changed.
   4. Rollback the update transaction
   5. Start the same query and verify that the old row (step 1) is retrieved

3.3. Isolation Test 3 ? Write-Write Conflict with Commit
Demonstrate isolation for the write-write conflict of two update transactions
when the first transaction is committed.

The following steps were performed to verify isolation of two update
transactions:
   1. Start a query and verify that the row was retrieved.
   2. Start an update transaction, read and update the same row. Wait before commit.
   3. Start another update transaction, read and try to update the same row and
      verify that the transaction is forced to rollback.
   4. Commit the update transaction
   5. Start the same query and verify that the new row is retrieved

3.4. Isolation Test 4 ? Write-Write Conflict with Rollback
Demonstrate isolation for the write-write conflict of two update transactions
when the first transaction is rolled back.

The following steps were performed to verify isolation of two update
transactions after the first one is rolled back:
   1. Start a query and verify that the row was retrieved.
   2. Start an update transaction, read and update the same row. Wait before commit.
   3. Start another update transaction, read and try to update the same row and
      verify that the transaction is forced to rollback.
   4. Rollback the update transaction
   5. Start the same query and verify that the new row retrieved in step 1 has not
      changed

3.5. Isolation Test 5 ? Concurrent Read and Write Transactions on Different Tables
Demonstrate the ability of read and write transactions affecting different
database tables to make progress concurrently.

The following steps were performed to demonstrate the ability of read and write
transactions affecting different tables to make progress concurrently:
   6. Create a copy of tables Lineitem and Orders. The original tables are referred
      to as TablePair A. The duplicate tables are referred to as TablePair B.
   7. Start a query against TablePair A and verify that the row was retrieved.
   8. Start a query against TablePair B and verify that the row was retrieved.
   9. Start an update transaction, read and update the same row in TablePair A. Wait
      before commit.
   10.Start an update transaction, read and update the same row in TablePair B and
      commit.
   11.Commit the update transaction against TablePair A.
   12.Start a query against TablePair A and verify that the updated row was
      retrieved.
   13.Start a query against TablePair B and verify that the updated row was
      retrieved.

3.6. Isolation Test 6 ? Update Transactions during Continuous Read-Only Query Stream
Demonstrate the continuous submission of arbitrary (read-only) queries against
one or more tables of the database does not indefinitely delay update
transactions affecting those tables from making progress.

The following query was used:

   SELECT    l1.l_quantity,
             SUM(l2.l_extendedprice),
             SUM(l3.l_extendedprice),
             SUM(l3.l_quantity)
   FROM      lineitem l1, lineitem l2, lineitem l3
   WHERE     l1.l_shipdate <= DATE ?1998-12-01? -0
             AND l1.l_orderkey = l2.l_orderkey
             AND l1.l_linenumber = l2.l_linenumber
             AND l1.l_extendedprice = l3.l_extendedprice
             AND l3.l_quantity < 30
   GROUP BY l1.l_quantity;
   COMMIT;

   1. A Transaction, T1, which executed the above query against the qualification
      database, was started using a randomly selected DELTA.
   2. An ACID Transaction, T2, was started for a randomly selected O_KEY, L_KEY and
      DELTA.
   3. T2 completed and appropriate rows in the ORDERS, LINEITEM and HISTORY tables
      had been changed.
   4. T1 was still executing.
   5. Transaction T1 completed executing the query.

4. Durability Requirements
   The tested system must guarantee durability: the ability to preserve the effects
of committed transactions and insure database consistency after recovery from
any one of the failures listed in Clause 3.5.2.

4.1. Permanent Unrecoverable Failure of Any Durable Medium
Guarantee the database and committed updates are preserved across a permanent
irrecoverable failure of any single durable medium containing TPC-H database
tables or recovery log tables.

During the durability test, one disk was removed. The test continued
uninterrupted, because of the RAID protection. Upon replacement/re-insert of the drive, 
the data files were rebuild to a consistent state by the RAID controller. 
The durability success file and the HISTORY table were compared and the counts matched.

4.2 System Crash
Guarantee the database and committed updates are preserved across an
instantaneous interruption (system crash/system hang) in processing which
requires the system to reboot to recover.

The system crash and memory failure tests were combined. Two system crashs were
performed by turning off the power during the durability test: one test for only
one node and the other test for the whole cluster.

When power was restored, in both cases the system rebooted automatically and the
database was restarted manually. The durability success file and the HISTORY
table were compared and the counts matched.

4.3 Memory Failure
Guarantee the database and committed updates are preserved across failure of all
or part of memory (loss of contents). See the previous section.

The system crash and memory failure tests were combined as explained in section
4.2
