Run Units, Locks, and Database Transactions › Sharing Transactions Among Sessions

Sharing Transactions Among Sessions

Sharing a Transaction

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

Enabling Transaction Sharing

A run unit is eligible to share its transaction if transaction sharing is in effect when the BIND RUN UNIT is issued. Whether transaction sharing is in effect depends on whether the run unit is a top-level or subordinate session.

A run unit (or any database session) started by an application program that is not executing as part of a database procedure or an SQL routine, is referred to as a top-level session. Transaction sharing is in effect for a top-level session if it is enabled in one of the following ways:

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

  See CA IDMS Common Facilities Guide for information about SYSIDMS parameters.

  The IDMSCINT or CICSOPT parameter specifies TXNSHR=ON for CICS applications

  See CA IDMS System Operations Guide for information about IDMSCINT and CICSOPT parameters.

• Transaction sharing is enabled for the executing DC/UCF task by means of a SYSGEN or DCMT command.

  See CA IDMS System Operations Guide for information about DCMT commands and CA IDMS System Generation Guide for information about system generation.

• Transaction sharing is enabled through a call to IDMSIN01 before the BIND RUN UNIT is issued.

  See CA IDMS Callable Services Guide for information about calling IDMSIN01.

A run unit (or any database session) started by an application program that is executing as part of a database procedure or an SQL routine is referred to as a subordinate session. For subordinate sessions started by database procedures, transaction sharing is in effect if it has been enabled prior to procedure invocation or by a call to IDMSIN01 from within the procedure. For subordinate sessions started by SQL routines, transaction sharing is controlled through the TRANSACTION SHARING parameter of the SQL routine definition unless overridden by a call to IDMSIN01 from within the routine itself.

See CA IDMS SQL Reference Guide for information about the TRANSACTION SHARING parameter of the CREATE PROCEDURE, CREATE TABLE PROCEDURE or CREATE FUNCTION statements.

Whether transaction sharing is enabled for a remote run unit is determined by the attribute in effect in the CA IDMS environment in which the BIND RUN UNIT is issued. (A remote run unit is one for which the database being accessed resides on a central version different from where the application is executing.)

System internal run units never share their transactions.

Sharing Transactions

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

• A top-level run unit will share its transaction with another top-level session if they are both eligible for transaction sharing. The other top-level session could be another run unit or an SQL session.

  

• A subordinate run unit that is eligible for transaction sharing shares its parent session's transaction even if the parent 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 Interference

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 MODIFY relies on that invalidated currency, an error is returned:

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

Before enabling transaction sharing for an application, you should ensure that affected programs handle these errors appropriately. For instance, a navigational DML program could re-obtain the record that was the target of a failed MODIFY.

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, including those made by other database sessions. Other sessions sharing the transaction must issue their own rollback request before issuing other DML requests. Issuing a non-rollback DML request first will result in an error:

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

Effect of Commit Requests

If multiple 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 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 database changes (COMMIT, FINISH, COMMIT TASK, etc.).

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.