Example of Potential Transactional Inconsistency
This example shows how to create two incremental autorefresh read-only cache groups.
The following example uses the employee and departments table, where the department id of the department table is a foreign key that points to the department id of the employee table.
The following example creates two incremental autorefresh read-only cache groups, where each is in its own cache group. The autorefresh transaction limit is enabled with ttCacheAutorefreshXactLimit
before a large transaction and is disabled after it completes.
-
Before you initiate the large transaction, invoke
ttCacheAutorefreshXactLimit
to set the interval value and the number of operations after which to automatically commit. The following sets the number of operations to three (which is intentionally low to show a brief example) for all incremental autorefresh read-only cache groups with a two second interval.CALL ttCacheAutorefreshXactLimit('2000', '3'); < 2000, 3 > 1 row found.
-
Create the incremental autorefresh read-only cache groups with interval of two seconds. This example creates two static (non-dynamic) read-only cache groups, where each contains a single table.
CREATE READONLY CACHE GROUP cgDepts AUTOREFRESH MODE INCREMENTAL INTERVAL 2 SECONDS FROM departments ( department_id NUMBER(4) PRIMARY KEY , department_name VARCHAR2(30) NOT NULL , manager_id NUMBER(6) , location_id NUMBER(4) ); CREATE READONLY CACHE GROUP cgEmpls AUTOREFRESH MODE INCREMENTAL INTERVAL 2 SECONDS FROM employees ( employee_id NUMBER(6) PRIMARY KEY , first_name VARCHAR2(20) , last_name VARCHAR2(25) NOT NULL , email VARCHAR2(25) NOT NULL UNIQUE , phone_number VARCHAR2(20) , hire_date DATE NOT NULL , job_id VARCHAR2(10) NOT NULL , salary NUMBER(8,2) , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , department_id NUMBER(4) );
-
Run a
LOAD CACHE GROUP
statement for both cache groups with autorefresh.LOAD CACHE GROUP cgDepts COMMIT EVERY 256 ROWS; 27 cache instances affected. LOAD CACHE GROUP cgEmpls COMMIT EVERY 256 ROWS; 107 cache instances affected.
You can have inconsistency within the table during an autorefresh as shown with the employees table.
-
On TimesTen, select the minimum and maximum salary of all employees.
SELECT MIN(salary), MAX(salary) FROM employees; < 2100, 24000 > 1 row found.
-
On the Oracle database, add 100,000 to everyone's salary.
UPDATE employees SET salary = salary + 100000; 107 rows updated.
-
On TimesTen, when you run the
SELECT
again (while the autorefresh transactions are commmitted after every 3 records), it shows that while the maximum salary has updated, the minimum salary is still the old value.SELECT MIN(salary), MAX(salary) FROM employees; < 2100, 124000 > 1 row found.
-
However, once the autorefresh completes, transactional consistency is maintained. For this example, once the autorefresh process completes, all salaries have increased by 100,000.
SELECT MIN(salary), MAX(salary) FROM employees; < 102100, 124000 > 1 row found.
-
The large transaction is complete, so disable the transaction limit for cache groups with a 2 second interval autorefresh.
call ttCacheAutorefreshXactLimit('2000', 'OFF');
You can have transactional inconsistency between cache groups if you run a SQL statement while the autorefresh process is progressing. The following SELECT
statement example runs against the employees and department table in the cgDepts
autorefresh cache group. With this example, since the foreign key is not enforced on TimesTen and the autorefresh process applies several transactions, the employee table updates may be inserted before the department updates.
In addition, all of the updates for both tables in the cache group are not applied until the autorefresh cycle has completed. In the following example, the SELECT
statement is performed before the autorefresh process is complete. Thus, the results do not show all of the expected data, such as the department name and several employees (some of the lawyers in the legal department 1000) are missing.
SELECT e.department_id, d.DEPARTMENT_NAME, e.FIRST_NAME, e.LAST_NAME FROM employees e, departments d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID (+) AND e.department_id >= 1000 ORDER BY 1,2,3,4; < 1000, Legal, Alec, Dunkle > < 1000, Legal, Barry, Strong > < 1000, Legal, Leigh, Harrison > 3 rows found.
However, after the autorefresh process completes, transactional consistency is maintained. The following shows the same SELECT
statement performed after the autorefresh is complete. All expected data, the department information and all of the new lawyers, are updated.
SELECT e.department_id, d.DEPARTMENT_NAME, e.FIRST_NAME, e.LAST_NAME FROM employees e, departments d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID (+) AND e.department_id >= 1000 ORDER BY 1,2,3,4; < 1000, Legal, Alec, Dunkle > < 1000, Legal, Barry, Strong > < 1000, Legal, Leigh, Harrison > < 1000, Legal, John, Crust > < 1000, Legal, Robert, Wright > < 1000, Legal, Robert, Smith > 6 rows found.
For cache groups with autorefresh that have more than one table, you can also experience transactional inconsistency if you run SQL statements while the autorefresh process is in progress.