8 Transaction Management and Recovery

TimesTen supports transactions that provide atomic, consistent, isolated and durable (ACID) access to data.

TimesTen transactions support ANSI Serializable and ANSI Read Committed levels of isolation. ANSI Serializable isolation is the most stringent transaction isolation level. ANSI Read Committed allows greater concurrency. Read Committed is the default and is an appropriate isolation level for most applications.

Applications configure the transaction features they need with connection attributes or connection options. See "Connection Attributes" in Oracle TimesTen In-Memory Database Reference for more details on how to set isolation levels and durability options.

The main topics in this chapter are:

Transaction overview
Transaction semantics
Transaction atomicity
Transaction durability
Transaction logging
Durable commit performance enhancements
Concurrency control through isolation and locking
Checkpoint operations

Transaction overview

All operations on a TimesTen database, even those that do not modify or access application data, are executed within a transaction. When running an operation and there is no outstanding transaction, one is started automatically on behalf of the application. Transactions are completed by an explicit or implicit commit or rollback. When completed, resources that were acquired or opened by the transaction are released and freed, such as locks and cursors.

Use the following SQL statements to commit or rollback your transaction:

The SQL COMMIT statement commits the current transaction. Updates made in the transaction are made available to concurrent transactions.
The SQL ROLLBACK statement rolls back the current transaction. All updates made in the transaction are undone.

Note:

For the syntax of the COMMIT and ROLLBACK statements, see "SQL Statements" in the Oracle TimesTen In-Memory Database SQL Reference.

Read-only transactions do not require a commit. When executing write operations, complete transactions to release locks. When possible, keep write transactions short in duration. Any long-running transactions can reduce concurrency and decrease throughput because locks are held for a longer period of time, which blocks concurrent transactions. Also, long-running transactions can prevent transaction log files from being purged, causing these files to accumulate on disk.

A connection can have only one outstanding transaction at any time and cannot be explicitly closed if it has an open transaction.

Configuring transaction implicit commit behavior

The following sections describe how you can configure whether the application enables implicit commit behavior or requires explicit commit behavior for DML or DDL statements:

Transaction autocommit behavior
TimesTen DDL commit behavior
Relationship between autocommit and DDLCommitBehavior

Transaction autocommit behavior

Autocommit configures whether TimesTen issues an implicit commit after DML or DDL statements. By default, autocommit is enabled, following the ODBC and JDBC specifications.

When autocommit is on, the following behavior occurs:

An implicit commit is issued immediately after a statement executes successfully.
An implicit rollback is issued immediately after a statement execution fails, such as a primary key violation.
If the statement generates a result set that opens a cursor, the automatic commit is not issued until that cursor and any other open cursors in the transaction have been explicitly closed. Any statements executed while a cursor is open is not committed until all cursors have been closed.

Fetching all rows of a result set does not automatically close its cursor. After the result set has been processed, its cursor must be explicitly closed if using the read committed isolation level or the transaction must be explicitly committed or rolled back if using Serializable isolation level.

Note:
Even with durable commits and autocommit enabled, you could lose work if there is a failure or the application exits without closing cursors.
If you are using ODBC or JDBC batch operations to INSERT, UPDATE or DELETE several rows in one call when autocommit is on, a commit occurs after the entire batch operation has completed. If there is an error during the batch operation, those rows that have been successfully modified will be committed within this transaction. If an error occurs due to a problem on a particular row, only the successfully modified rows preceding the row with the error are committed in this transaction. The pirow parameter to the ODBC SQLParamOptions function contains the number of the rows in the batch that had a problem.

Commits can be costly for performance and intrusive if they are implicitly executed after every statement. TimesTen recommends you disable autocommit so that all commits are intentional. Disabling autocommit provides control over transactional boundaries, enables multiple statements to be executed within a single transaction, and improves performance, since there is no implicit commit after every statement.

If autocommit is disabled, transactions must be explicitly completed with a commit or rollback after any of the following:

Completing all the work that was to be done in the transaction.
Issuing a transaction-consistent (blocking) checkpoint request.
Updating column and table statistics to be used by the query optimizer.
Calling a TimesTen built-in procedure that does not generate a result set in order for the new setting specified in the procedure to take effect, such as the ttLockWait procedure.

You must establish a connection to a database before changing the autocommit setting. To disable autocommit, perform one of the following:

In ODBC-based applications, execute SQLSetConnectOption function with SQL_AUTOCOMMIT_OFF.
In JDBC applications, Connection.setAutoCommit(false) method.
When running ttIsql, issue the autocommit 0 command.

TimesTen DDL commit behavior

Traditionally, in TimesTen databases, DDL statements are executed as part of the current transaction and are committed or rolled back along with the rest of the transaction. However, the default behavior for the Oracle database is that it issues an implicit COMMIT before and after any DDL statement.

For the TimesTen release 11.2.1, you can configure for either behavior with the DDLCommitBehavior connection attribute, as follows:

0 - Oracle database behavior. An implicit transaction commit is performed before the execution of each DDL statement and a durable commit is performed after the execution of each DDL statement. This is the default.
1 - Traditional TimesTen behavior. Execution of DDL statements does not trigger implicit transaction commits.

DDL statements include the following:

CREATE, ALTER and DROP statements for any database object, including tables, views, users, procedures and indexes.
TRUNCATE
GRANT and REVOKE

The consequences of setting DDLCommitBehavior=0 include the following:

DDL changes cannot be rolled back.
DDL statements delete records from global temporary tables unless the tables were created with the ON COMMIT PRESERVE ROWS clause.
Tables created with the CREATE TABLE ... AS SELECT statement are visible immediately.
TRUNCATE statements are committed automatically. However, the truncate of the parent and child tables must be truncated in separate transactions, with the child table truncated first. You cannot truncate a parent table unless the child table is empty. The truncation of child and parent table can only be in the same transaction if you set DDLCommitBehavior to 1.

For more information, see "DDLCommitBehavior" in the Oracle TimesTen In-Memory Database Reference.

Relationship between autocommit and DDLCommitBehavior

Both autocommit and DDLCommitBehavior configure if and when implicit commits occur for SQL statements.

Autocommit applies to both DDL and DML statements. Enabling for implicit commits of DDL statements overlaps in both options. If autocommit is enabled and DDLCommitBehavior is disabled, autocommit only commits after the DDL statement. However, if both autocommit and DDLCommitBehavior is enabled, an implicit commit occurs both before and after the DDL statement.
To enable DDLCommitBehavior, you set the DDLCommitBehavior DSN attribute. To enable or disable autocommit, the application executes an ODBC function or JDBC method.

Table 8-1 shows what behavior occurs when you enable or disable one option in conjunction with the other:

Table 8-1 Relationship between autocommit and DDLCommitBehavior

AutoCommit	DDLCommitBehavior	Relationship
ON	ON	All statements are automatically committed, unless you have an open cursor. DDL statements are implicitly committed before and after execution.
OFF	ON	Recommended setting. DDL statements are implicitly committed before and after execution. All other statements require an explicit commit.
ON	OFF	All statements are implicitly committed after execution, unless you have an open cursor. A commit is issued after the DDL is processed and not before.
OFF	OFF	All statements require an explicit commit, including DDL statements.

Transaction semantics

Logging and locking are used to ensure atomicity, consistency, isolation and durability (ACID) semantics for transactions. Locking and transaction logs are used to ensure ACID semantics as a transaction modifies data in a database as follows:

Locking: TimesTen acquires locks on data items that the transaction writes and, depending on the transaction isolation level, data items that the transaction reads. See "Concurrency control through isolation and locking".
Transaction log: Modifications to the database are recorded in a transaction log. See "Transaction logging".

The following table shows how TimesTen uses locks and transaction logs:

If	Then
Transaction is terminated successfully (committed)	Transaction log is posted to disk if the `DurableCommits` attribute is turned on. Locks that were acquired on behalf of the transaction are released and the corresponding data becomes available to other transactions to read and modify. All open cursors in the transaction are automatically closed.
Transaction is rolled back	Transaction log is used to undo the effects of the transaction and to restore any modified data items to the state they were before the transaction began. Locks that were acquired on behalf of the transaction are released. All open cursors in the transaction are automatically closed.
System fails (data not committed)	On first connect, TimesTen automatically performs database recovery by reading the latest checkpoint image and applying the transaction log to restore the database to its most recent transactionally consistent state. See "Checkpoint operations".
Application fails	All outstanding transactions are rolled back.

TimesTen supports temporary databases, which have essentially no checkpoints. However, they do have a transaction log so that transactions can be rolled back. Recovery is never performed for such databases. They will be destroyed after a database or application shuts down or fails. For information on temporary databases, see "Database overview".

Transaction atomicity

All TimesTen transactions are atomic. Either all or none of the effects of the transaction are applied to the database. Atomicity is implemented by using the transaction log to undo the effects of a transaction if it is rolled back. Rollback can be caused explicitly by the application or during database recovery because the transaction was not committed at the time of failure.

Transaction durability

The TimesTen Data Manager provides durability with a combination of checkpointing and transaction logging.

A checkpoint operation writes the current in-memory database image to a checkpoint file on disk, which has the effect of making all transactions that have been committed at the time of the checkpoint operation durable.

All transactions are logged to an in-memory transaction log buffer, which is written to disk in one of the following ways:

Guaranteed durability through a durable (synchronous) commit
Delayed durability through a non-durable (asynchronous) commit:

Note:

Checkpointing and logging are further described in "Transaction logging" and "Checkpoint operations".

The following sections describe durability options for TimesTen applications:

Guaranteed durability
Delayed durability
Durable commit performance enhancements

Guaranteed durability

Durability is implemented with a combination of checkpointing and logging.

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

Control returns to the application after the transaction log data has been durably written to disk. A durably committed transaction will not be lost even in the event of a system failure.

To enable guaranteed durability, applications set the DurableCommits attribute to 1.

Any recovery uses the last checkpoint image together with the transaction log to reconstruct the latest transaction-consistent state of the database.

Note:

Committing a transaction durably makes that transaction and all previous transactions durable. Any non-durable transactions will no longer be subject to loss in the event of a database failure, just as if it had originally been committed durably.

If most of your transactions commit durably, you may want to set the LogFlushMethod first connect attribute to 2. This connection attribute configures how TimesTen writes and synchronizes log data to transaction log files. For more information, see "Use durable commits appropriately".

Delayed durability

In delayed durability mode, as in guaranteed durability mode, each transaction enters records into the in-memory transaction log as it makes modifications to the database. However, when a transaction commits in delayed durability mode, it does not wait for the transaction log to be posted to disk before returning control to the application. Thus, a non-durable transaction may be lost in the event of a database failure. However, they execute considerably faster than durable transactions. Eventually, transactions are flushed to disk by the database's subdaemon process or when the in-memory log buffer is full.

Applications request delayed durability mode by setting the DurableCommits attribute to 0. This is the default and the recommended option. Connections that use delayed durability can coexist with connections that use guaranteed durability.

Applications that wish to take advantage of the performance benefits of delayed durability mode, but 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.

Durable commit performance enhancements

The performance cost for durable commits can be reduced with a group commit of multiple concurrently executing transactions. Many threads executing at the same time, if they are short transactions, may commit at almost the same time. Then, 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. Each connection spends more time waiting to commit than it spends using the CPU. Alternatively, applications that perform infrequent durable commits cause each connection to be very CPU-intensive for the TimesTen portion of its workload.

Applications that do not require optimal response time and can tolerate some transaction loss may elect to perform periodic durable commits. This maintains a smaller window of vulnerability to transaction loss as opposed to all transactions being committed non-durably. By committing only every nth transaction durably or performing a durable commit every n seconds, an application can achieve a quicker response time while maintaining a small window of vulnerability to transaction loss. A user can elect to perform a durable commit of a critical transaction, such as one that deals with financial exchange, that cannot be vulnerable to loss.

To enable periodic durable commits, an application does the following:

Connects with setting the attribute DurableCommits=0. This causes the transactions to commit non-durably.
When a durable commit is needed, the application can call the ttDurableCommit built-in procedure before committing. The ttDurableCommit built-in procedure does not actually commit the transaction; it merely causes the commit to be durable when it occurs.

Transaction logging

TimesTen creates one transaction log for each database, which is shared by all concurrent connections. A transaction log record is created for each database update, commit, and rollback. However, transaction log records are not generated for read-only transactions. Log records are first written to the transaction log buffer, which resides in the same shared memory segment as the database. The contents of the log buffer are then subsequently flushed to the latest transaction log file on disk.

The transaction log is used to track all updates made within a transaction, so that those updates can be undone if the transaction is rolled back.

Transaction logging enables recovery of transactions from checkpoint files and the transaction log, which were committed from the time of the last checkpoint operation after a system failure. If the transaction is non-durable, any committed transactions in the log buffer that have not been flushed to disk would be lost in the event of a system failure.

The following sections describe how to manage and monitor the transaction log buffers and file:

Managing transaction log buffers and files
Monitoring accumulation of transaction log files

Managing transaction log buffers and files

The following describes how to configure transaction log buffers and files:

Transaction log buffers: There is one transaction log buffer for each database and the size of the transaction log buffer can be configured using the LogBufMB DSN attribute. Each transaction log buffer can have multiple strands. The number of transaction log buffer strands is configured with the LogBufParallelism attribute.
Transaction log files: The maximum size for the transaction log files are configured with the LogFileSize DSN attribute. The transaction log files are created in the same directory as the checkpoint files unless the LogDir attribute specifies a different location. The transaction log file names have the form ds_name.logn. The ds_name is the database path name that is specified by the DataStore DSN attribute and is provided within the database's DSN. The suffix n is the transaction log file number, starting at zero.

Note:
For best performance, TimesTen recommends that applications use the LogDir attribute to place the transaction log files in a different physical device from the checkpoint files. If separated, I/O operations for checkpoints do not block I/O operations to the transaction log and vice versa.

Monitoring accumulation of transaction log files

It is important to verify at frequent intervals that there are no transaction log holds that could result in an excessive accumulation of transaction log files. If too many transaction log files accumulate and fill up available disk space, new transactions in the TimesTen database cannot begin until the transaction log hold is advanced and transaction log files are purged by the next checkpoint operation.

The following sections describe transaction log operations, log holds, and accumulation of log files:

Purging transaction log files
Log holds by TimesTen components or operations
Monitoring log holds and log file accumulation

Purging transaction log files

Any transaction log file is kept until TimesTen determines it can be purged, which can occur under the following conditions:

Transactions writing log records to the file have been committed or rolled back. These can be either local database transactions or XA transactions.
Changes recorded in the file have been written to both checkpoint files.
Changes recorded in the file have been replicated, if replication is enabled.
Changes recorded in the file have been propagated to Oracle Database, if IMDB Cache is used and configured for that behavior.
Changes recorded in the file have been reported to XLA, if XLA is used.

Under normal TimesTen operating conditions, unneeded transaction log files are purged each time a checkpoint is initiated. A checkpoint can be initiated either through a configurable time interval with the CkptFrequency connection attribute, a configurable log volume with the CkptLogVolume connection attribute, or execution of the ttCkpt built-in function, which can be called either manually or in a background checkpointing application thread.

If you are running out of disk space because of log files accumulating, use the CkptLogVolume connection attribute instead of the CkptFrequency connection attribute. In addition, if you execute the ttLogHolds build-in procedure frequently, you can tell if log reclamation is blocked.

Note:

To improve preformance, locate your log files on a separate disk partition from the one on which the checkpoint files are located. The LogDir connection attribute determines where log files are stored. For more information, see "Managing transaction log buffers and files" and "LogDir" in the Oracle TimesTen In-Memory Database Reference.

See "Checkpointing" in the Oracle In-Memory Database Cache Introduction for general information. See "Configure or turn off background checkpointing" for more details on CkptFrequency and CkptLogVolume. Also, see CkptFrequency, CkptLogVolume, ttCkpt, and ttLogHolds in the Oracle TimesTen In-Memory Database Reference.

Log holds by TimesTen components or operations

Several TimesTen components or operations can cause transaction log holds. A transaction log hold prevents log files, beyond a certain point, from being purged until they are no longer needed. In normal circumstances, the log hold position is regularly advanced and log files are purged appropriately. However, if operations are not functioning properly and the hold position does not advance, there can be an excessive accumulation of log files beyond the hold position that can no longer be purged, which eventually fills available disk space.

These components and operations include the following:

Replication: There is a transaction log hold until the transmitting replication agent confirms that the log files have been fully processed by the receiving host.

Possible failure modes include the following:
- The network is down or there is a standby crash and replication is unable to deliver data to one or more subscribers. If necessary, the application can direct that logs no longer be held, then duplicate the master database to the standby when normal operations resume. Criteria for when to do this includes the amount of time required to duplicate, the amount of available disk space on the master for log files, and the transaction log growth rate.
- The overall database transaction rate exceeds the ability of replication to keep the active and standby databases synchronized. An application can reduce the application transaction rate or the number of replicated tables.
For more information, see "Improving replication performance" in the Oracle TimesTen In-Memory Database Replication Guide and "Troubleshooting Replication" in the Oracle TimesTen In-Memory Database Troubleshooting Guide.
XLA: There is a transaction log hold until the XLA bookmark advances.

A possible failure mode occurs when the bookmark becomes stuck, which can occur if an XLA application terminates unexpectedly or if it disconnects without first deleting its bookmark or disabling change-tracking. If a bookmark gets too far behind, the application can delete it. If the XLA reader process is still active, it must first be terminated, so that another XLA process can connect and delete the bookmark.
Active standby pairs that replicate AWT cache groups: There is a transaction log hold until the replication agent confirms that the transaction corresponding to the log hold has been committed on the Oracle Database. With an active standby pair, the active database typically receives the confirmation from the standby database. If the standby database is down, the replication agent receives confirmation from Oracle Database directly.

Possible failure modes include the following:
- Oracle Database is down or there is a lock or resource contention.
- The network is down, slow, or saturated.
- With an active standby pair, replication to the standby database falls behind. Check log holds on the standby database.
- The transaction rate to TimesTen exceeds the maximum sustainable rate that TimesTen can propagate to Oracle Database.
For more information, see "Monitoring AWT cache groups" in the Oracle In-Memory Database Cache User's Guide and "Troubleshooting AWT Cache Groups" in the Oracle TimesTen In-Memory Database Troubleshooting Guide.
Cache groups configured with AUTOREFRESH: There is a transaction log hold until the replication agent on the active database confirms the log files have been fully processed by the standby database.

Possible failure modes include the following:
- Replication from the active database to the standby database is impacted because the standby database falls behind due to large workloads resulting from AUTOREFRESH mode.
- The standby database is down or recovering, but has not been marked as FAILED through a call, initiated by either the user application or Oracle Clusterware, to the ttRepStateSave built-in procedure. The active database does not take over propagation to the Oracle Database until the state of the standby database is marked as FAILED. While the standby database is down or recovering, transaction log files are held for the Oracle Database.
For more information, see "Monitoring autorefresh cache groups" in the Oracle TimesTen In-Memory Database Troubleshooting Guide.
Incremental TimesTen backup: There is a transaction log hold until the backup completes.

A possible failure mode can occur if the incremental backup falls too far behind the most recent entries in the transaction log. For example, ensure that an unexpected burst of transaction activity cannot fill up available transaction log disk space due to the backup holding a log file that is too old. An application can perform another incremental backup to work around this situation.
Long-running transaction or XA transaction: There is a transaction log hold until the transaction completes.

A possible failure mode can occur if an application transaction does not commit or roll back for a long time, so that it becomes necessary for the application to terminate the long-running transaction.

If necessary, you can roll back a transaction using the ttXactAdmin utility with the -xactIdRollback option. See "ttXactAdmin" in Oracle TimesTen In-Memory Database Reference.

Monitoring log holds and log file accumulation

Options for periodic monitoring of excessive transaction log accumulation include the following:

Call the ttLogHolds built-in procedure, which returns a result set with details of all log holds. The information includes the following, as applicable:
- Log file number, the offset of the hold position, and the type of hold, which can be checkpoint, replication, backup, XLA, long-running transaction, or long-running XA transaction
- Name of the checkpoint file for a checkpoint hold
- Name of the subscriber and the parallel track ID it uses for replication
- Backup path for a backup hold
- Name of the persistent subscription and process ID of the last process to open it for XLA
- Transaction ID for a long-running transaction
- XA XID for a long-running XA transaction
For more information, see "ttLogHolds" in the Oracle TimesTen In-Memory Database Reference.
Call the ttCkptHistory built-in procedure to check the last several checkpoints to confirm none of the returned rows has a status of FAILED.

For more information, see "ttCkptHistory" in the Oracle TimesTen In-Memory Database Reference.
Check the SYS.MONITOR table for operational metrics. Each transaction log file has a unique sequence number, which starts at 0 for the first log file and increments by 1 for each subsequent log file. The number of the current log file is available in SYS.MONITOR.LAST_LOG_FILE. The number of the oldest log file not yet purged is available in SYS.MONITOR.FIRST_LOG_FILE. You should raise an error or warning if the difference in the sequence numbers exceeds an inappropriate threshold.

For more information, see "SYS.MONITOR" in the Oracle TimesTen In-Memory Database System Tables and Views Reference.
For XLA, check the SYS.TRANSACTION_LOG_API table that provides bookmark information, such as the process ID of the connected application, which could help diagnose the reason why a bookmark may be stuck or lagging.

For more information, see "SYS.TRANSACTION_LOG_API" in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

Concurrency control through isolation and locking

The following sections describe transaction isolation and locking levels:

Transaction isolation levels
Locking granularities

Transaction isolation levels

Transaction isolation enables each active transaction to operate as if there were no other transactions active in the system. Isolation levels determine if row-level locks are acquired when performing read operations. When a statement is issued to update a table, locks are acquired to prevent other transactions from modifying the same data until the updating transaction completes and releases its locks.

The Isolation connection attribute sets the isolation level for a connection. Isolation levels have no effect if using database-level locking because transactions cannot be run concurrently. The isolation level cannot be changed in the middle of a transaction.

TimesTen supports the following two transaction isolation levels:

ANSI Read Committed isolation: The read committed isolation level is the recommended mode of operation for most applications, and is the default mode. It enables transactions that are reading data to execute concurrently with a transaction that is updating the same data. TimesTen makes multiple versions of data items to allow non-serializable read and write operations to proceed in parallel.

Read operations do not block write operations and write operations do not block read operations, even when they read and write the same data. Read operations do not acquire locks on scanned rows. Write operations acquire locks that are held until the transaction commits or rolls back. Readers share a committed copy of the data, whereas a writer has its own uncommitted version. Therefore, when a transaction reads an item that is being updated by another in-progress transaction, it sees the committed version of that item. It cannot see an uncommitted version of an in-progress transaction.

Read committed isolation level provides for better concurrency at the expense of decreased isolation because of the possibility of non-repeatable reads or phantom rows within a transaction. If an application executes the same query multiple times within the same transaction, the commit of an update from another transaction may cause the results from the read operation to retrieve different results. A phantom row appears in modified form in two different reads, in the same transaction, due to early release of read locks during the transaction.

To set read committed isolation level, if previously modified since this is the default, do one of the following:
- ODBC applications execute the SQLSetConnectOption ODBC function with the SQL_TXN_ISOLATION flag set to SQL_TXN_READ_COMMITTED.
- Connect with isolation=1 in the connection string.
- When using ttIsql, execute ISOLATION 1 or ISOLATION READ_COMMITTED.
ANSI Serializable isolation: All locks acquired within a transaction by a read or write operation are held until the transaction commits or rolls back. Read operations block write operations, and write operations block read operations. 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.

Serializable isolation level provides for repeatable reads and increased isolation at the expense of decreased concurrency. A transaction that executes the same query multiple times within the same transaction 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.

To set the isolation level to Serializable, do one of the following:
- ODBC applications execute the SQLSetConnectOption ODBC function with the SQL_TXN_ISOLATION flag set to SQL_TXN_SERIALIZABLE.
- Connect with isolation=0 in the connection string.
- When using ttIsql, execute isolation 0 or isolation serializable.
To ensure that materialized views are always in a consistent state, all view maintenance operations are performed under Serializable isolation, even when the transaction is in read committed isolation. This means that the transaction obtains read locks for any data items read during view maintenance. However, the transaction releases the read locks at the end of the INSERT, UPDATE or CREATE VIEW statement that triggered the view maintenance, instead of holding them until the end of the transaction.

Note:

The ttXactAdmin utility generates a report showing lock holds and lock waits for all outstanding transactions. It can be used to troubleshoot lock contention problems where operations are being blocked, or encountering lock timeout or deadlock errors. It can also be used to roll back a specified transaction.

Locking granularities

TimesTen supports row-level locks, table-level locks and database-level locks:

Note:

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

Row-level locking: Transactions usually obtain locks on the individual rows that they access. Row-level locking is the recommended mode of operation because it provides the finest granularity of concurrency control. It allows concurrent transactions to update different rows of the same table. However, row-level locking requires space in the database's temporary partition to store lock information.

Row-level locking is the default. However, if it has been modified to another type of locking and you want to re-enable row-level locking, do one of the following:
- Set the LockLevel connection attribute to 0.
- Execute the ttLockLevel built-in procedure with the lockLevel parameter set to Row. This procedure changes the lock level between row-level and database-level locking on the next transaction and for all subsequent transactions for this connection.
- Execute the ttOptSetFlag procedure to set the RowLock parameter to 1, which enables the optimizer to consider using row locks.
Note:
See "LockLevel," "ttLockLevel," and "ttOptSetFlag" in the Oracle TimesTen In-Memory Database Reference for more information.
Table-level locking: Table-level locking is recommended when concurrent transactions access different tables or a transaction accesses most of the rows of a particular table. Table-level locking provides better concurrency than database-level locking. Row-level locking provides better concurrency than table-level locking. Table-level locking requires only a small amount of space in the temporary partition to store lock information.

Table-level locking provides the best performance for the following:
- Queries that access a significant number of rows of a table
- When there are very few concurrent transactions that access a table
- When temporary space is inadequate to contain all row locks that an operation, such as a large insert or a large delete, might acquire
To enable table-level locking, execute the ttOptSetFlag procedure to set the TblLock parameter to 1, which enables the optimizer to consider using table locks. In addition, set RowLock to 0 so that the optimizer does not consider row-level locks.

If both table-level and row-level locking are disabled, TimesTen defaults to 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. Even though table-level locking provides better performance than row-level locking because of reduced locking overhead, the optimizer will often choose row-level locking for better concurrency. For more information, see "ttOptSetFlag" in the Oracle TimesTen In-Memory Database Reference.

Note:
When multiple locks have been obtained within the same transaction, the locks are released sequentially when the transaction ends.
Database-level locking: Database-level locking serializes all transactions, which effectively allows no concurrency on the database. When a transaction is started, it acquires an exclusive lock on the database, which ensures that there is no more than one active transaction in the database at any given time. It releases the lock when the transaction is completed.

Database-level locking often provides better performance than row-level locking, due to reduced locking overhead. In addition, it provides higher throughput than row-level locking when running a single stream of transactions such as a bulk load operation. However, its applicability is limited to applications that never execute multiple concurrent transactions. With database-level locking, every transaction effectively runs in ANSI Serializable isolation, since concurrent transactions are disallowed.

To enable database-level locking, do one of the following:
- Set the LockLevel connection attribute to 1.
- Execute the ttLockLevel built-in procedure with the lockLevel parameter set to DS. This procedure changes the lock level between row-level and database-level locking on the next transaction and for all subsequent transactions for this connection.

Setting wait time for acquiring a lock

Set the LockWait connection attribute to the maximum amount of time that a statement waits to acquire a lock before it times out. The default is 10 seconds. For more information, see "LockWait" in Oracle TimesTen In-Memory Database Reference.

If a statement within a transaction waits for a lock and the lock wait interval has elapsed, an error is returned. After receiving the error, the application can reissue the statement.

Lock wait intervals are imprecise due to the scheduling of the database's managing subdaemon process to detect lock timeouts. This imprecision does not apply to zero-second timeouts, which are always immediately reported. The lock wait interval does not apply to blocking checkpoints.

The database's managing subdaemon process checks every two seconds to see if there is a deadlock in the database among concurrent transactions. If a deadlock occurs, an error is returned to one of the transactions involved in the deadlock cycle. The transaction that receives the error must rollback in order to allow the other transactions involved in the deadlock to proceed.

Checkpoint operations

A checkpoint operation saves the in-memory image of a database to disk files, known as checkpoint files. By default, TimesTen performs background checkpoints at regular intervals. Checkpointing may generate a large amount of I/O activity and have a long execution time depending on the size of the database and the number of database changes since the most recent checkpoint.

Note:

Applications can programmatically initiate checkpoint operations. See "Setting and managing checkpoints" for more details.

Temporary databases do not initiate checkpointing. See "Database persistence" for more information on temporary databases.

The following sections describe checkpoint operations and how you can manage them:

Purpose of checkpoints
Usage of checkpoint files
Types of checkpoints
Setting and managing checkpoints

Purpose of checkpoints

A checkpoint operation has two primary purposes.

Decreases the amount of time required for database recovery, because it provides a more up-to-date database image on which recovery can begin.
Makes a portion of the transaction log unneeded for any future database recovery operation, typically allowing one or more transaction log files to be deleted.

Both of these functions are very important to TimesTen applications. The reduction in recovery time is important, as the amount of a transaction log needed to recover a database has a direct impact on the amount of downtime seen by an application after a system failure. The removal of unneeded transaction log files is important because it frees disk space that can be used for new transaction log files. In addition, the fewer transaction log files you have, the less time is required to load a database into memory. If these files were never removed, they would eventually consume all available space in the transaction log directory's file system, causing database operations to fail due to log space exhaustion.

Usage of checkpoint files

Each TimesTen database has two checkpoint files, named dsname.ds0 and dsname.ds1, where dsname is the database path name and file name prefix specified in the database DSN. During a checkpoint operation, TimesTen determines which checkpoint file contains the most recent consistent image and then writes the next in-memory image of the database to the other file. Thus, the two files contain the two most recent database images.

TimesTen uses the most recent consistent checkpoint file and the transaction log to recover the database to its most recent transaction-consistent state after a database shutdown or system failure. If any errors occur during this process, or if the more recent checkpoint image is incomplete, then recovery restarts using the other checkpoint file.

TimesTen also creates dsName.resn files for each database. These files are pre-allocated to the same size as the transaction log files. The .res files contain pre-allocated space that is used if the transaction log directory were to become full. If this occurs, transactions are prevented from writing any new log records. Transactions that attempt to write new log records are forced to rollback.

Types of checkpoints

TimesTen supports two types of database checkpoint operations:

Fuzzy or non-blocking checkpoints
Transaction-consistent checkpoints

Fuzzy or non-blocking checkpoints

Fuzzy checkpoints, or non-blocking checkpoints, allow transactions to execute against the database while the checkpoint is in progress. Fuzzy checkpoints do not obtain locks of any kind, and therefore have a minimal impact on other database activity. Because transactions may modify the database while a checkpoint operation is in progress, the resulting checkpoint file may contain both committed and uncommitted transactions. Furthermore, different portions of the checkpoint image may reflect different points in time. For example, one portion may have been written before a given transaction committed, while another portion was written afterward. The term "fuzzy checkpoint" derives its name from this fuzzy state of the database image.

To recover the database when the checkpoint files were generated from fuzzy checkpoint operations, TimesTen requires the most recent consistent checkpoint file and the transaction log to bring the database into its most recent transaction-consistent state.

Transaction-consistent checkpoints

Transaction-consistent checkpoints, also known as blocking checkpoints, obtain an exclusive lock on the database for a portion of the checkpoint operation, blocking all access to the database during that time. The resulting checkpoint image contains all committed transactions prior to the time the checkpoint operations acquired the exclusive lock on the database. Because no transactions can be active while the database lock is held, no modifications made by in-progress transactions are included in the checkpoint image.

TimesTen uses the most recent consistent checkpoint file to recover the database to transaction-consistent state at the time of the last successful checkpoint operation completed. It uses the transaction log files to recover the database to its most recent transaction-consistent state after a database shutdown or system failure.

To request a transaction-consistent checkpoint, an application uses the ttCkptBlocking built-in procedure. The actual checkpoint is delayed until the requesting transaction commits or rolls back. If a transaction-consistent checkpoint is requested for a database for which both checkpoint files are already up to date then the checkpoint request is ignored.

Setting and managing checkpoints

The default behavior for TimesTen checkpoints is as follows:

TimesTen performs periodic fuzzy checkpoints in the background. You can modify this behavior. See "Configure or turn off background checkpointing" for more information.
TimesTen performs a transaction-consistent checkpoint operation of a database just before the database is unloaded from memory. See "Transaction-consistent checkpoints".

You can manage and monitor checkpoints with the following connection attributes and built-in procedures:

CkptFrequency attribute
CkptLogVolume attribute
CkptRate attribute
ttCkpt built-in procedure
ttCkptBlocking built-in procedure
ttCkptConfig built-in procedure
ttCkptHistory built-in procedure

The following sections describe how to manage checkpointing:

Programmatically performing a checkpoint
Configure or turn off background checkpointing
Display checkpoint history and status
Setting the checkpoint rate

Programmatically performing a checkpoint

By default, TimesTen performs periodic fuzzy checkpoints in the background. Therefore, applications rarely need to issue manual checkpoints. However, if an application wishes to issue a manual checkpoint, it can call the ttCkpt built-in procedure to request a fuzzy checkpoint or the ttCkptBlocking built-in procedure to request a transaction-consistent checkpoint.

Configure or turn off background checkpointing

Using attributes or built-in procedures, you can configure TimesTen to checkpoint either when the transaction log files contain a certain amount of data or at a specific frequency.

To configure checkpointing in TimesTen, do the following:

Configure the CkptFrequency and CkptLogVolume connection attributes as follows:

The CkptFrequency connection attribute controls the how often, in seconds, that TimesTen performs a background checkpoint. The default is 600 seconds.
The CkptLogVolume connection attribute controls how much data, in megabytes, that collects in the log between background checkpoints. By increasing this amount, you can delay the frequency of the checkpoint. The default is 0.

To turn off background checkpointing, set both the CkptFrequency and CkptLogVolume connection attributes to 0.

Alternatively, you can configure background checkpointing or turn it off by executing the ttCkptConfig built-in procedure. The values set by ttCkptConfig take precedence over those set with the connection attributes.

Note:

For information on default values and usage, see the Oracle TimesTen In-Memory Database Reference.

Display checkpoint history and status

Use the ttCkptHistory built-in procedure to display the information on the last eight checkpoints. You can monitor the progress of a running checkpoint with the Percent_Complete column.

Setting the checkpoint rate

By default, there is no limit to the rate at which checkpoint data is written to disk. You can use the CkptRate attribute or the ttCkptConfig built-in procedure to set the maximum rate at which background checkpoint data is written to disk. Checkpoints taken during recovery and final checkpoints do not honor this rate; in those situations, the rate is unlimited.

Note:

See the Oracle TimesTen In-Memory Database Reference for details on using these features.

Setting a rate too low can cause checkpoints to take an excessive amount of time and cause the following problems:

Delay the purging of unneeded transaction log files
Delay the start of backup operations
Increase recovery time.

When choosing a rate, you should take into consideration the amount of data written by a typical checkpoint and the amount of time checkpoints usually take. Both of these pieces of information are available through the ttCkptHistory built-in procedure.

If a running checkpoint appears to be progressing too slowly when you evaluate the progress of this checkpoint with the Percent_Complete column of the ttCkptHistory result set, the rate can be increased by calling the ttCkptConfig built-in procedure. If a call to ttCkptConfig changes the rate, the new rate takes effect immediately, affecting even the running checkpoint.

Perform the following to calculate the checkpoint rate:

Call the ttCkptHistory built-in procedure.
For any given checkpoint, subtract the starttime from the endtime.
Divide the number of bytes written by this elapsed time in seconds to get the number of bytes per second.
Divide this number by 1024*1024 to get the number of megabytes per second.

When setting the checkpoint rate, you should consider the following:

The specified checkpoint rate is only approximate. The actual rate of the checkpoint may be below the specified rate, depending on the hardware, system load and other factors.
The above method may underestimate the actual checkpoint rate, because the starttime and endtime interval includes other checkpoint activities in addition to the writing of dirty blocks to the checkpoint file.
The Percent_Complete field of the ttCkptHistory call may show 100 percent before the checkpoint is actually complete. The Percent_Complete field shows only the progress of the writing of dirty blocks and does not include additional bookkeeping at the end of the checkpoint.
When adjusting the checkpoint rate, you may also need to adjust the checkpoint frequency, as a slower rate makes checkpoints take longer, which effectively increases the minimum time between checkpoint beginnings.