8 Transaction Management and Recovery

Guaranteed atomicity and durability

When you set the attribute DurableCommits=1, then durable commits are implemented and the log is written to disk at transaction commit. By default, all TimesTen transactions are durable. The effects of the transaction are persistent and will not be lost in the event of system failure.

Durability is implemented with a combination of checkpointing and logging. See "Checkpoints" and "Log files". A checkpoint operation writes the current data store image to a checkpoint file on disk, which has the effect of making all transactions that committed before the checkpoint durable. For transactions that committed after the last checkpoint, TimesTen uses conventional logging techniques to make them durable. As each transaction progresses, it records its data store modifications in an in-memory log. At commit time, the relevant portion of the log is flushed to disk. This log flush operation makes that transaction, and all previously-committed transactions, durable.

In the case of a system failure, recovery uses the last checkpoint image together with the log to reconstruct the latest transaction-consistent state of the data store.

In addition to being durable, by default all TimesTen transactions are also atomic. Either all or none of the effects of the transaction is applied to the data store.

Atomicity is implemented by using the log to undo the effects of a transaction if it is rolled back. Rollback can be caused explicitly by the application, using the ODBC SQLTransact function or JDBC Connection.rollback method, or during data store recovery because the transaction was not committed at the time of failure.

In order to have guaranteed atomicity and durability, applications must set the DurableCommit attributes to 1.

Guaranteed atomicity, delayed durability

When you set the attribute DurableCommits=0, then durable commits are not implemented and the log is not written to disk at transaction commit. It is possible to connect to a data store with guaranteed durability disabled. In this case, the atomicity of a transaction is guaranteed, but not its durability. This mode is known as delayed durability mode.

In delayed durability mode, as in guaranteed durability mode, each transaction enters records into the in-memory log as it makes modifications to the data store. However, when a transaction commits in delayed durability mode, it does not wait for the log to be posted to disk before returning control to the application. Since the content of the in-memory log would be lost in a system failure, transactions committed in this mode are not durable.

Non-durable committed transactions have much better response time than durable-committed transactions, because no I/O is required to commit the transaction. A non-durable committed transaction can be made durable either by checkpointing or by committing a subsequent transaction durably. See "Checkpoints" for more information. As with guaranteed durability mode, a checkpoint makes all transactions that committed before the checkpoint durable. Committing a transaction durably commits that transaction and makes all previously committed transactions durable.

Applications that wish to take advantage of the performance benefits of delayed durability mode but which can only tolerate the loss of a small number of transactions can perform periodic durable commits in a background process - only those transactions that committed non-durably after the last durable commit are vulnerable to loss in the event of a system failure.

Applications request delayed durability mode by setting the DurableCommits attribute to 0. When in this mode, applications can call the ttDurableCommit built-in procedure to force the current transaction to commit durably when it commits.

Using durable commits

The performance cost of durable commits can be mitigated if many threads are running at the same time, due to an effect called "group commit." Under group commit, a single disk write commits a group of concurrent transactions durably. Group commit does not improve the response time of any given commit operation, as each durable commit must wait for a disk write to complete, but it can significantly improve the throughput of a series of concurrent transactions.

When durable commits are used frequently, TimesTen can support more connections than there are CPUs, as long as transactions are short. This is true because each connection spends more time waiting to commit than it spends using the CPU. This is in contrast to applications that do infrequent durable commits, in which case each connection tends to be very CPU-intensive for the TimesTen portion of its workload. In the latter case, using more connections than there are processors will tend to give worse performance, due to CPU contention.

Applications that require lower response time and can tolerate some transaction loss may elect to perform periodic durable commits. By committing only every nth transaction durably, or performing a durable commit every n seconds typically in a background process, an application can achieve quick response time while maintaining a small window of vulnerability to transaction loss.

Because a durable commit commits not only itself but all previously-committed transactions durably - even those performed by other threads or other processes, an application that commits every n transactions durably only risks the loss of the last n transactions.

Similarly, an application that performs a durable commit every n seconds, risks only the transactions that committed during the last n seconds.

To enable periodic durable commits, an application connects with DurableCommits=0, so transactions commit non-durably by default. When a durable commit is needed, the application calls the ttDurableCommit built-in procedure before committing. As with all SQL statements, it is best to prepare the call to ttDurableCommit if it will be used frequently. The ttDurableCommit built-in procedure does not actually commit the transaction; it merely causes the commit to be durable when it occurs.

Another option for avoiding data loss is to use TimesTen replication instead of durable commits to maintain a copy of the data in two memories. Although two memories are not as durable as disk, replication can provide higher data availability by allowing for failover without data store recovery. This type of trade-off is common in high-performance systems. For more details on TimesTen replication, see the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

Log files

All log files are created in the same directory as the data store files unless the LogDir attribute specifies a different location. The log file names have the form ds_name.logn, where ds_name is the data store path name given in the data store's DSN and n is the log file number, starting at zero.

To retain archived log files, set the LogPurge attribute to 0. When the LogPurge attribute is not set for the data store connection, TimesTen renames log files no longer needed to perform recovery to ds_name.logn.arch. In this case, the application is responsible for removing these unneeded log files. See "LogPurge" in the Oracle TimesTen In-Memory Database Reference for details on purging log files.

Transaction isolation levels

• In ANSI Serializable isolation, each transaction acquires locks on all data items that it reads or writes. It holds these locks until it commits or rolls back. As a result, a row that has been read by one transaction cannot be updated or deleted by another transaction until the original transaction terminates. Similarly, a row that has been inserted, updated or deleted by one transaction cannot be accessed in any way by another transaction until the original transaction terminates.

  Repeatable reads are assured: a transaction that executes the same query multiple times is guaranteed to see the same result set each time. Other transactions cannot UPDATE or DELETE any of the returned rows, nor can they INSERT a new row that satisfies the query predicate.

  In this isolation level, readers can block writers and writers can block readers and other writers.

• In ANSI Read Committed isolation, each transaction acquires locks only on the items that it writes. Items read by SELECT statements, the SELECT portion of INSERT SELECT statements and MERGE statements, are not locked. This is the default isolation level for TimesTen.

  In this isolation level, readers do not block writers, nor do writers block readers, even when they read and write the same data items. To allow readers and writers to access the same items without blocking, writers create private copies of the items that they update. These private copies become public when the transaction commits, or are discarded if the transaction rolls back. Therefore, when a transaction reads an item that has been updated by another in-progress transaction, it sees the state of that item before it was updated. It cannot see an uncommitted state.

  Non-repeatable reads are possible in this isolation level. If a read committed transaction executes the same query multiple times, the commit of an updater transaction may cause it to see different results.

Using read committed isolation level can lead to duplicates in a result set. A SELECT statement selects more or less rows than the total number of rows in the table if some rows were added or removed and committed in the range that the SELECT scan is happening. This may happen when an UPDATE, INSERT or DELETE adds or deletes a value from an index and the SELECT scan is using this index. This can also happen when an INSERT or DELETE adds or deletes rows from the table and the SELECT operation is using an all-table scan.

This happens because index values are ordered and an UPDATE of an index value may delete the old value and insert the new value into a different place. In other words it moves a row from one position in the index to another position. If an index scan sees the same row in both positions, it returns the same row twice. This does not happen with a serial scan because table pages are unordered and rows do not need to be moved around for an UPDATE. Hence once a scan passes a row, it will not see that same row again.

The only general way to avoid this problem is for the SELECT to use serializable isolation. This prevents a concurrent INSERT, DELETE or UPDATE operation. There is no reliable way to avoid this problem with INSERT or DELETE by forcing the use of an index because these operations affect all indexes. With UPDATE, this problem can be avoided by forcing the SELECT statement to use an index that is not being updated.

All data access in TimesTen uses locking or copying to provide isolation. Applications set the transaction isolation level either using the SQLSetConnectOption ODBC function with the SQL_TXN_ISOLATION flag, or by setting the Isolation connection attribute.

To ensure that materialized views are always in a consistent state, all view maintenance operations are effectively performed under Serializable isolation, even when the transaction is otherwise in Read_Committed isolation. This means that the transaction will obtain read locks for any data items read during view maintenance. However, the transaction will release these read locks at the end of the INSERT, UPDATE or CREATE VIEW statement that triggered the view maintenance instead of holding them until it terminates.

Locking granularities

TimesTen supports row-level locks, table-level locks and data store-level locks:

• With row-level locking, transactions usually obtain locks on the individual rows that they access, although a transaction may obtain a lock on an entire table if TimesTen determines that doing so would result in better performance. Row-level locking is the default and is the best choice for most applications, as it provides the finest granularity of concurrency control. To use row-level locking, applications must set the LockLevel connection attribute to 0 (the default value) or call the ttLockLevel built-in procedure with the lockLevel parameter set to Row.

  In order to CREATE, DROP, or ALTER a user, you can only use row-level locking and thus, the Locklevel must be set to 0 before you can perform any of these operations.

• Table-level locking is useful for queries that access a significant portion of the rows of a table or when there are very few concurrent transactions that access a table. Enable the optimizer to choose table-level locking by calling the ttOptSetFlag built-in procedure and setting the TbleLock flag to 1. If both table-level and row-level locking are disabled, TimesTen chooses row-level locking. If both table-level and row-level locking are enabled, TimesTen chooses the locking scheme that is more likely to have better performance. Typically, table-level locking provides better performance than row-level locking because of reduced locking overhead. For more information, see "ttOptSetFlag" in Oracle TimesTen In-Memory Database Reference.

• With data store-level locking, every transaction obtains an exclusive lock on the entire data store, thus ensuring that there is no more than one active transaction in the data store at any given time. Data store-level locking often provides better performance than row-level locking, due to reduced locking overhead. However, its applicability is limited to applications that never need to execute multiple concurrent transactions. With data store-level locking, every transaction effectively runs in ANSI Serializable isolation, since concurrent transactions are disallowed. To use data store-level locking, applications set the LockLevel connection attribute to 1 or call the ttLockLevel built-in procedure with the lockLevel parameter set to set to DS.

Coexistence of different locking levels

Different connections can coexist with different levels of locking, but the presence of even one connection using data store-level locking leads to reduced concurrency. For performance information, see "Choose the best method of locking".

Types of checkpoints

TimesTen supports two kinds of data store checkpoints: Transaction-consistent checkpoints and Fuzzy or non-blocking checkpoints.

Setting and managing checkpoints

By default, TimesTen performs automatic non-blocking checkpoints in the background. In this case, background checkpoints are non-blocking. See "Fuzzy or non-blocking checkpoints" for more information.

Several data store attributes and built-in procedures are available to set, manage and monitor checkpoints. These include:

• CkptFrequency attribute

• CkptLogVolume attribute

• CkptRate attribute

• ttCkpt built-in procedure

• ttCkptBlocking built-in procedure

• ttCkptConfig built-in procedure

• ttCkptHistory built-in procedure

TimesTen also automatically performs a transaction-consistent checkpoint when the last application disconnects from the data store, unless the RAM policy is always. For temporary data stores, checkpoints are still taken to purge the log files. See "Transaction-consistent checkpoints".

In addition, applications can programatically perform a checkpoint using the ttCkpt or ttCkptBlocking built in procedure. For details on how to call the ttCkpt and other TimesTen built-in procedures from a C or Java program, see "Calling TimesTen built-in procedures within C applications" in the Oracle TimesTen In-Memory Database C Developer's Guide or "Calling TimesTen built-in procedures" in the Oracle TimesTen In-Memory Database Java Developer's Guide.

By default, TimesTen performs background checkpoints at regular intervals. If an application attempts to perform a checkpoint while a background checkpoint is in progress, TimesTen returns an error to the application. To turn off background checkpointing, set CkptFrequency=0 and CkptLogVolume=0. You can also use the built-in procedure ttCkptConfig to configure background checkpointing or turn it off. The values set by ttCkptConfig take precedence over those set with the data store attributes.

Using these attributes and the built-in procedure, you can configure TimesTen to checkpoint either when the log files contain a certain amount of data or at a specific frequency. For information on default values and usage, see the Oracle TimesTen In-Memory Database Reference.

If the application attempts to back up a data store while a background checkpoint is in process, TimesTen waits until the checkpoint finishes and before beginning the backup. If a background checkpoint starts while a backup is in progress, the background checkpoint will not take place until the backup has completed. If a background checkpoint starts while an application-initiated checkpoint is in progress, then an error results.

You can use the ttCkptHistory built-in procedure to display the history of last eight checkpoints, the settings for checkpoint frequency and log volume and the status of in-progress checkpoint disk writes.