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 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.