1. Operations Guide
  2. Using the ttIsql Utility
  3. Using ttIsql to Manage Transactions

Using ttIsql to Manage Transactions

The ttIsql utility has several built-in commands for managing transactions.

These commands are summarized below:

  • autocommit - Turns on or off the autocommit feature. This can also be set as an attribute of the set command.

  • commit - Commits the current transaction.

  • commitdurable - Commits the current transaction and ensures that the committed work is recovered in case of database failure.

  • rollback - Rolls back the current transaction.

  • isolation - Changes the transaction isolation level. This can also be set as an attribute of the set command.

  • sqlquerytimeout - Specifies the number of seconds to wait for a SQL statement to run before returning to the application. This can also be set as an attribute of the set command.

When starting ttIsql, the autocommit feature is turned on by default, even within a SQL script. In this mode, every SQL operation against the database is committed automatically.

To turn the autocommit feature off, run the ttIsql autocommit command with an argument of 0. When autocommit is turned off, transactions must be committed or rolled back manually by running the ttIsql commit, commitdurable or rollback commands. The commitdurable command ensures that the transaction's effect is preserved in case of database failure. If autocommit is off when ttIsql exits, any uncommitted statements are rolled back and reported by ttIsql.

The ttIsql isolation command can be used to change the current connection's transaction isolation properties. The isolation can be changed only at the beginning of a transaction. The isolation command accepts one of the following constants: READ_COMMITTED and SERIALIZABLE. If the isolation command is modified without an argument then the current isolation level is reported.

The ttIsql sqlquerytimeout command sets the timeout period for SQL statements. If the run time of a SQL statement exceeds the number of seconds set by the sqlquerytimeout command, the SQL statement does not run and an 6111 error is generated. See Setting a Timeout Duration for SQL Statements in the Oracle TimesTen In-Memory Database Java Developer's Guide and Setting a Timeout Duration for SQL Statements in the Oracle TimesTen In-Memory Database C Developer's Guide.

Note:

TimesTen roll back and query timeout features do not stop cache operations that are being processed on the Oracle database. This includes passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating and dynamic loading.

The following example demonstrates the common use of the ttIsql built-in transaction management commands. 

E:\>ttIsql
Copyright (c) 1996, 2024 Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

Command> connect "DSN=database1";
Connection successful: 
DSN=database1;DataStore=/disk1/databases/database1;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)
Command> autocommit 0;
Command> CREATE TABLE LOOKUP (KEY NUMBER NOT NULL PRIMARY KEY, VALUE CHAR (64));
Command> commit;
Command> INSERT INTO LOOKUP VALUES (1, 'ABC');
1 row inserted.
Command> SELECT * FROM LOOKUP;
< 1, ABC >
1 row found.
Command> rollback;
Command> SELECT * FROM LOOKUP;
0 rows found.
Command> isolation;
isolation = READ_COMMITTED
Command> commitdurable;
Command> sqlquerytimeout 10;
Command> sqlquerytimeout;
Query timeout = 10 seconds
Command> disconnect;
Disconnecting...
Command> exit;
Done.