Skip to Content
SAP IQ

Global Transaction Resiliency

Tags:

Global Transaction Resiliency is HA functiionality available in IQ 16. This functionality allows Global Transactions to survive temporary communcation and temporary Coordinator failures.

Global Transaction is started by DML( delete/insert/load/update) on Multiplex Secondary(Writer) Node.  Global Transactions are linked to INC( Internal Internode Communication) connection  between Secondary(Writer) node and Coordinator.  RW operation executed on Secondary(Writer) node behave as they were executed the Coordinator and does acquire table locks.  Global Transaction failure can occur due to Coordinator failure(crash due to Hardware/Software issues, Coordinator Shutdown), Failover and INC(internal internode communication) . In such scenarios active Global DML transactions on Secondary(Writer) node  will fail,  secondary node will not be able to commit/rollback these transactions, even after Coordianator is available after some time. These transactions will need to be applied again.

With Global Transaction Resiliency functionality, Global Transactions are suspended and resumed, when INC connection is restored in following conditions:

  • Coordinator Crash(Hardware and Software issue)  and restart.
  • Coordinator shutdown and restart
  • Coordinator failover to New Coordinator.
  • INC(internal internode communication) failure and re-establishment.

In case of temporary failure of INC/Coordianator Global DML transactions on Secondary(Writer) node are kept in suspended state, and failures are resolved within timeout set with MPX_LIVENESS_TIMEOUT database option,  then global transaction resumes, as if there were no failures. If the failures persists longer than the timeout set, then global transaction cann't be resumed, Secondary(Writer) rolls back suspended global transaction.

System Stored Procedure sp_iqconnection can be used to check connection status on Secondary(Writer) Node.

  • INCConnName:  The Name of the underlying INC connection for a user connection. The datatype for INCConnName is varchar(255). If sp_iqconnection shows an INC connection Name for a suspended user connection, that user connection has an associated INC connection that is also suspended.
  • INCConnSuspended: The value of "Y" in this field indicates that the underlying INC connection for a user connection is in suspended state. The "N" indicates that the connection is not suspended.

For complete details on sp_iqconnection:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc38151.1600/doc/pdf/iqrefbb.pdf

System Stored Procedures sp_iqmpsuspendedconninfo() can be used to for details about currently suspended connections and transactions on the Coordinator Node.

and

sp_iqmpxincstatistics()  can be used for snapshot  of the aggregate statistics of  the INC(internal Internode communication) status since server startup as if the moment of execution

For complete details:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01839.1600/doc/pdf/iqmpx.pdf

Database options to control Globaal Transaction Resilency behavior:

mpx_heartbeat_frequency:  Default:  60 seconds

                                             Minimum:  2 seconds

                                             Maximum: 3600 seconds

mpx_heartbeat_frequency option sets the interval at which Secondary node internally polls Coordiator availability.

mpx_liveness_timeout:  Default: 3600 seconds

                                       Minimum:  0 seconds

                                       Maximum: 3600 seconds

mpx_liveness_timeout  option sets the timeout in seconds before a Secondary(Writer) Node rolls back suspended global transactions, if Coordiator is not available for a timeout greater than this option values.  Cooridnator rollback suspended global transactions if Secondary(Writer) Node fails to resume the transactions for a period greater than twice of this option values.

For complete details:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01839.1600/doc/pdf/iqmpx.pdf

Global Transaction Resiliency functionality does not affect trasactions initiated on the Coordinator Node. DDL commands from Secondary Nodes(Writer/Reader) will fail if Coordiator Node is not available.

No comments