Writing an SQL Program › Database Transactions › Sharing Transactions Among Sessions

Sharing Transactions Among Sessions

Sharing a Transaction

A transaction can be shared by multiple database sessions, both SQL sessions and non-SQL sessions (rununits). By sharing a transaction, sessions will not deadlock among themselves even if they access and update the same data.

Enabling Transaction Sharing

An SQL session is eligible to share its transaction if transaction sharing is in effect when the database session is started.

Transaction sharing is in effect for a top-level session if:

• TRANSACTION_SHARING=ON is specified in the SYSIDMS file for a batch application.

  Note: For more information about SYSIDMS parameters, see the CA IDMS Common Facilities Guide.

• IDMSCINT or CICSOPT parameter specified TXNSHR=ON for CICS applications.

  Note: For more information about IDMSCINT and CICSOPT parameters, see the CA IDMS System Operations Guide.

• Transaction sharing has been enabled for the executing DC/UCF task by means of a SYSGEN or DCMT command.
• Transaction sharing has been enabled though a call to IDMSIN01.

For subordinate sessions, transaction sharing is controlled through the TRANSACTION SHARING parameter of the SQL invoked routine's definition unless overridden by a call to IDMSIN01 from within the routine.

• If TRANSACTION SHARING ON is specified, transaction sharing is enabled for all sessions started by the routine.
• If TRANSACTION SHARING OFF is specified, transaction sharing is disabled for all sessions started by the routine.
• If TRANSACTION SHARING DEFAULT is specified (or defaulted), the transaction sharing state that was in effect before the routine was called applies to all sessions started by the routine.

Note: For more information about the TRANSACTION SHARING parameter of the CREATE PROCEDURE, CREATE TABLE PROCEDURE or CREATE FUNCTION statements, see the CA IDMS SQL Reference Guide.

Whether transaction sharing is enabled for a remote SQL session is determined by the attribute in effect in the CA IDMS environment in which the session-initiating statement is issued. (A remote session is one that is connected to a dictionary residing on a central version different from where the application is executing.)

Regardless of how transaction sharing is enabled, if it is in effect at the time a database session is started, then that database session is eligible to share its transaction with other database sessions started by the same task or user session. The following rules determine whether two sessions will share a transaction.

• A top-level database session will share its transaction with another top-level session if they are both eligible for transaction sharing.

  

• A subordinate database session that is eligible for transaction sharing will share its encompassing session's transaction even if the encompassing session is not eligible to share its transaction.

  

Application Programming Considerations

Transaction sharing affects applications in the following ways:

• An update made through a database session may impact other database sessions sharing the same transaction.
• A rollback issued within one database session affects all sessions that share the same transaction.
• A commit issued by a database session whose transaction is shared has no affect on the transaction unless all other sharing sessions have also been committed.

Inter-session Conflicts

Database sessions that share a transaction can impact each other in ways that would not be possible without transaction sharing since locking would prevent such interactions. For example, a record can be deleted by one database session while it is current of another database session that is sharing the same transaction. This can result in new and possibly unexpected error conditions. If a database session's currency is impacted by an update made through another database session, that currency is invalidated. If a subsequent DML request, such as a FETCH from a cursor, relies on that invalidated currency, an error is returned.

• For SQL, the application receives an SQLCODE of -4 (statement failure) and an SQLRSN of 1087 (conflicting activity within a shared transaction).
• For navigational DML, an error status of xx03 is returned to the application.

Before enabling transaction sharing for an application, you should ensure that affected programs handle these errors appropriately.

Effect of Rollback Requests

If multiple database sessions share a transaction and one of those sessions issues a rollback request, all changes made within the transaction are immediately rolled out. Other sessions sharing the transaction must issue their own rollback requests before issuing any other DML requests. Issuing another DML request instead of a rollback will result in an error:

• For SQL, the application receives an SQLCODE of -5 (transaction failure) and an SQLRSN of 1088 (transaction forced to backout)
• For navigational DML, the run unit is terminated and an error status of xx19 is returned to the application.

Effect of Commit Requests

If multiple top level database sessions share a transaction and one of those sessions issues a commit request, no changes are committed until:

• All top-level sharing sessions that have had activity since the last commit, rollback or start of a transaction have issued a commit, or,
• Until a teleprocessing commit is issued.

The term "commit" refers to any DML command that would normally result in committing changes (COMMIT RELEASE, COMMIT CONTINUE, FINISH, and so forth).

A commit issued through a subordinate session has no impact on its transaction if it is shared since such a transaction can only be committed through the encompassing session.

Unless a COMMIT CONTINUE request is issued (for which currency locks are retained), all currencies owned by the issuing database session are immediately released. However, implicit exclusive locks and explicit locks acquired by the database session remain until the transaction is committed, even if the request terminates the database session.