Synchronous WriteThrough (SWT) Cache Group

A synchronous writethrough (SWT) cache group enforces a caching behavior where committed changes on the TimesTen cache tables are automatically and synchronously propagated to the cached Oracle Database tables.

See Figure 4-7.

Only TimesTen Classic supports SWT cache groups.

Note:

You should avoid running DML statements on Oracle Database tables cached in an SWT cache group. This can result in an error condition. See Restrictions With SWT Cache Groups.

Figure 4-7 Synchronous WriteThrough Cache Group

Description of Figure 4-7 follows
Description of "Figure 4-7 Synchronous WriteThrough Cache Group"

The transaction commit on the TimesTen database occurs synchronously with the commit on the Oracle database. When an application commits a transaction in the TimesTen database, the transaction is processed in the Oracle database before it is processed in TimesTen. The application is blocked until the transaction has completed in both the Oracle and TimesTen databases.

If the transaction fails to commit in the Oracle database, the application must roll back the transaction in TimesTen. If the Oracle Database transaction commits successfully but the TimesTen transaction fails to commit, the cache tables in the SWT cache group are no longer synchronized with the cached Oracle Database tables.

Note:

The behavior and error conditions for how commit occurs on both the TimesTen and Oracle databases when committing propagated updates is the same commit process on a user managed cache group with the PROPAGATE cache attribute that is described in PROPAGATE Cache Table Attribute.

To manually resynchronize the cache tables with the cached Oracle Database tables, call the ttCachePropagateFlagSet built-in procedure to disable update propagation, and then reissue the transaction in the TimesTen database after correcting the problem that caused the transaction commit to fail in TimesTen. Then, call the ttCachePropagateFlagSet built-in procedure to re-enable update propagation. You can also resynchronize the cache tables with the cached Oracle Database tables by reloading the accompanying cache groups.

The following is an example definition of the Oracle Database table that is to be cached in an example SWT cache group. The Oracle Database table is owned by the schema user sales.

CREATE TABLE product
(prod_num    VARCHAR2(6) NOT NULL PRIMARY KEY,
 name        VARCHAR2(30),
 price       NUMBER(8,2),
 ship_weight NUMBER(4,1));

The Oracle cache administration user, cacheadmin, must be granted certain privileges when creating a cache group. For SWT cache groups, the required privileges are SELECT, INSERT, UPDATE, and DELETE privileges on any cached tables. In this example, the table is the sales.product table.

See Required Privileges for Cache Administration User for Cache Operations for all required privileges for different activities.

On the Oracle database as an administrator, grant the following privileges:

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.product TO cacheadmin;

On the TimesTen database:

Connect as the TimesTen cache administration user. Use the CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP statement to create an SWT cache group.

The following statement creates a synchronous writethrough cache group top_products that caches the sales.product table:

CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP top_products
FROM sales.product
 (prod_num    VARCHAR2(6) NOT NULL,
  name        VARCHAR2(30),
  price       NUMBER(8,2),
  ship_weight NUMBER(4,1),
  PRIMARY KEY(prod_num));

When TimesTen manages operations for SWT cache groups, it connects to the Oracle database using the current user's credentials provided on the connection string. The current user's credentials can be provided with an Oracle Wallet pointed to by the PwdWallet connection attribute or with the UID, PWD, and OraclePwd connection attributes. TimesTen does not connect to the Oracle database with the Oracle cache administration user name and password registered with the ttCacheUidPwdSet built-in procedure when managing SWT cache group operations. See Providing Cache Administration User Credentials When Connecting and Registering the Cache Administration User Name and Password.

Restrictions With SWT Cache Groups

There are certain restrictions when using an SWT cache group.

The following restrictions apply when using an SWT cache group:

  • Only the ON DELETE CASCADE and UNIQUE HASH ON cache table attributes can be used in the cache table definitions.

    See ON DELETE CASCADE Cache Table Attribute for more information about the ON DELETE CASCADE cache table attribute.

    See Creating a Hash Index on the Primary Key Columns of the Cache Table for more information about the UNIQUE HASH ON cache table attribute.

  • A FLUSH CACHE GROUP statement cannot be issued on the cache group.

    See Flushing a User Managed Cache Group for more information about the FLUSH CACHE GROUP statement

  • The cache table definitions cannot contain a WHERE clause.

    See Using a WHERE Clause for more information about WHERE clauses in cache group definitions and operations.

  • A TRUNCATE TABLE statement cannot be issued on the cache tables.

  • SWT cache groups cannot cache Oracle Database views or materialized views.

  • You should avoid running DML statements directly on Oracle Database tables cached in an SWT cache group. This could result in an error condition. Any insert, update, or delete operation on the cached Oracle Database table can negatively affect the operations performed on TimesTen for the affected rows. TimesTen does not detect or resolve update conflicts that occur on the Oracle database. Committed changes made directly on a cached Oracle Database table may be overwritten by a committed update made on the TimesTen cache table when the cache table update is propagated to the Oracle database. In addition, deleting rows on the cached Oracle Database table could cause an empty update if TimesTen tries to update a row that no longer exists.

    To ensure that not all data is restricted from DML statements on Oracle Database, you can partition the data on Oracle Database to separate the data that is to be included in the SWT cache group from the data to be excluded from the SWT cache group.