8 Transaction Management and Recovery

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

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.

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:

1. Connects with setting the attribute DurableCommits=0. This causes the transactions to commit non-durably.

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

Managing transaction log buffers and files

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

• Transaction log buffer: There is only on 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 can be configured using the LogFileSize DSN attribute. All 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.

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.

Locking granularities

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

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

• 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 Oracle TimesTen In-Memory Database Reference.

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

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

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