How Open Global Transactions Make Your Secondary Stuck in Fast Recovery And How To Terminate Those Global Transactions

What is Global Transaction?

A global transaction is a transaction that involves more than one database server. HCL OneDB database servers support two types of global transactions: TP/XA with a transaction manager and two-phase commit.

HCL OneDB uses a two-phase commit protocol to ensure that distributed queries are uniformly committed or rolled back across multiple database servers.

Global Transaction needs to be terminated when your secondary server is stuck in Fast recovery mode and is not coming online. So, we need to locate and terminate global transactions. Sometimes, GT can be terminated gracefully or not, based on their FLAGs.

Scenario 1: Restarting Updateable secondary after a crash will get stuck in fast recovery mode until all open transactions are processed. Global transaction can be terminated gracefully.

In this scenario, your Updateable secondary was crashed due to several reasons and upon starting, it gets stuck in fast recovery mode.

The message below is in online.log of sds node:

12:13:04 Started processing open transactions on secondary during startup

The secondary will not be operational until all the global transactions were cleared.

The message above shows it is incomplete. The secondary will allow new sessions only if you see the completed string in the log.

20:10:05 Finished processing open transactions on secondary during startup.

Example

In the below example SDS was stuck in FR mode for almost 8 hours. We should look for the output of onstat –G from both primary and secondary. They should have different addresses in memory, but they can be identified by the “data” column. The Flag should have ‘H’ at the 3rd position, which means it was heuristically rolling back or rolled back.

We can zap them using onmode –H 0x61fbe988 and onmode -H 0x61fbecf0 on SDS node. Immediately you will see your SDS will be in operational mode.

Scenario 2 : Your secondary was stuck in FR mode and Global Transaction cannot be terminated gracefully.

We have two global transactions holding some locks. See onstat -G and onstat -x output below.

3^rd flag shows

B- Begin Works

X- TP/XA prepared for commit

How can I get rid of them?

• Transaction with flags value of ‘-LX-G’ do not have userthread information as in the example above.
• These transactions are called orphaned transactions, and the user-thread has been dropped from the transaction.
• If these transactions stop using the Logical Log, they may enter the Long Transaction state.

• Log switching occurs naturally and causes a long transaction. This depends on the LTXHWM configuration variable setting, which by default is 70.
• There are 10 Logical Logs, if a transaction is not completed during 7 log switches, a long transaction is usually triggered.

• You can use onmode -l to manually switch the logical log and exceeds the LTXHWM limit, it will be processed as above. Generally, this does not affect instances or other transactions when processed as above.
• Alternatively, you would temporarily lower LTXHWM using ‘onmode -wm LTXHWM=<low_value>’.