1. ttIsql User's Guide and Reference
  2. Use ttIsql
  3. Run SQL Statements
  4. Manage Transactions

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 - Ensures the committed work is durably commit to the transaction log on disk in case of database failure.

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

  • rollback - Rolls back the current transaction.

  • 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 autocommit command with an argument of 0. When autocommit is turned off, transactions must be committed or rolled back manually by running the commit, commitdurable or rollback commands. The commitdurable command ensures that the transaction's effect is preserved to disk in case of database failure. If autocommit is off, the uncommitted statements that are rolled back are not reported when ttIsql exits.

The isolation command can be used to change the current connection's transaction isolation level. The isolation level 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 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 Java Developer's Guide and Setting a Timeout Duration for SQL Statements in the 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. 

$ ttIsql
Copyright (c) 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> Set sqlquerytimeout 5;
Command> select count (*) from tables, views, columns, all_source;
 6111: SQL statement has reached its timeout limit [timeout msec = 5000, elapsed msec = 5120] and has been terminated
The command failed.
Command> sqlquerytimeout;
Query timeout = 10 seconds
Command> disconnect;
Disconnecting...
Command> exit;
Done.