Managing Nonshareable Transactions

Writing an SQL Program › Database Transactions › Managing Nonshareable Transactions

Managing Nonshareable Transactions

Beginning a Transaction

A nonshareable transaction is started when the program submits an SQL statement that results in access to either user data or a dictionary, unless the session is already associated with a transaction.

Transaction Hierarchy

Just as sessions can be related in a hierarchical way, their associated transactions can also be related hierarchically. If a session is subordinate to another session, its transaction is subordinate to the encompassing session's transaction.

Note: For more information about session hierarchies, see Beginning and Ending an SQL Session.

When a transaction is committed or rolled back, all of its direct and indirect subordinates are also committed or rolled back.

Ending a Transaction

If a session's transaction is not shareable, it ends when:

A COMMIT statement is executed.
A ROLLBACK statement is executed.
The SQL session is terminated.

When a transaction ends, all open cursors are closed, all temporary tables are dropped, and all prepared statements are dropped.

More Information

For more information about cursors, see Using a Cursor.
For more information about temporary tables, see Creating and Using a Temporary Table.
For more information about prepared statements, see Executing Prepared SELECT Statements.

Committing Changes

Changes made through an SQL session are committed when an SQL COMMIT statement is executed or when a teleprocessing statement is executed that results in the committing of database updates. If changes are not committed in one of these ways, updates made through an SQL session are backed out, either as the result of an explicit ROLLBACK request or automatically as the result of a teleprocessing statement or event.

Note: For more information about the effect of teleprocessing statements on database transactions, see Effect of Teleprocessing Statements and Events.

Transaction sharing impacts the committing of database changes.

Note: For more information about the impact that sharing database transactions has on committing changes, see Sharing Transactions Among Sessions.

Preserving Session State after a Commit

Normally when a transaction is committed, the state of the session is reset: cursors are closed, prepared statements are deleted and temporary tables are dropped. However, a CA IDMS extension to the SQL standard allows you to commit updates but preserve the session state as it was prior to the commit. This extension is the CONTINUE parameter of the COMMIT statement:

   EXEC SQL
      COMMIT CONTINUE
   END-EXEC.

The CONTINUE parameter limits the effect of a COMMIT to committing updates and downgrading or releasing update locks held for the transaction.