Undo Space Recycling Blocked by Long Transactions

Symptom

1. The following error information is printed in gs_log:

   snapshot too old! the undo record has been forcibly discarded

   xid xxx, the undo size xxx of the transaction exceeds the threshold xxx. trans_undo_threshold_size xxx,undo_space_limit_size xxx. 

   In the actual error information, xxx indicates the actual data.

2. The value of global_recycle_xid (global recycling XID of the Undo subsystem) does not change for a long time.

   

3. Long transactions exist in the pg_running_xacts and pg_stat_activity views, blocking the progress of oldestxmin and global_recycle_xid. If the value of xmin obtained by querying active transactions in pg_running_xacts is the same as that of gs_txid_oldestxmin and the thread execution time obtained by querying pg_stat_activity based on a PID is too long, the recycling is suspended by a long transaction.

   SELECT * FROM pg_running_xacts WHERE xmin::text::bigint<>0 AND vacuum <> 't' ORDER BY xmin::text::bigint ASC LIMIT 5;
   SELECT * FROM gs_txid_oldestxmin();
   SELECT * FROM pg_stat_activity where pid = Thread PID where the long transaction exists

   

Solution

Use pg_terminate_session(pid, sessionid) to terminate the sessions of the long transactions. (Note: There is no fixed quick restoration method for long transactions. Forcibly ending the execution of SQL statements is a common but high-risk operation. Exercise caution when performing this operation. Before performing this operation, please confirm with the administrator and Huawei technical personnel to prevent service failures or errors.)