In the GBase 8c database, the TRUNCATE operation is used to quickly delete all data in a table while preserving the table structure. Normally, the TRUNCATE operation executes swiftly, but in certain scenarios, it may get stuck. This article explores the background of the GBase 8c TRUNCATE hang issue to help readers understand and resolve such problems.

Possible Causes of the Issue:

1. Lock Waits:
   
   If other transactions are accessing the table during the TRUNCATE operation, it may lead to lock waits. In this case, check if there are any long-running uncommitted transactions or if a deadlock exists.

2. System Resource Contention:
   
   When system resources (such as CPU, memory, disk I/O, etc.) are under heavy contention, the TRUNCATE operation may not get sufficient resources to execute, leading to a hang. In this scenario, check the system resource usage, optimize resource allocation, or consider scaling up.

3. Network Issues:
   
   GBase 8c requires communication between different nodes in the database cluster. If there are network issues, the TRUNCATE operation may fail to execute correctly. Ensure the network is stable and functioning properly.

4. Improper Database Parameter Settings:
   
   Certain parameters in GBase 8c can affect the execution of the TRUNCATE operation, such as concurrency settings, transaction isolation levels, etc. If these parameters are misconfigured, the TRUNCATE operation may hang. Review and adjust the database parameters accordingly.

5. Storage Engine Issues:
   
   GBase 8c supports multiple storage engines, and different engines may handle the TRUNCATE operation differently. If the storage engine in use has a bug or performance issue, the TRUNCATE operation may hang. In this case, try switching storage engines or upgrading the engine version.

Example Scenario: Simulating a Truncate Hang

Session 1: Manually Begin a Transaction and Query Table t1 Without Committing

begin;
select * from t1;

Example output:

id | c1
---+---
 1 | 1
(1 row)

Session 2: Execute TRUNCATE on Table t1 and Observe the Hang

truncate table t1;

Query lock information:

select * from pgxc_relation_locks where relname = 't1';

Example output:

Theoretical Analysis of the Two Sessions:

• Session 1:

  • mode: AccessShareLock - The SELECT command applies an AccessShareLock on the table, which persists until the transaction ends.
  • granted: hold lock - Lock is held.

• Session 2:

  • mode: AccessExclusiveLock - The ACCESS EXCLUSIVE lock conflicts with all other locks, typically requested by DDL statements.
  • granted: acquire lock - Lock is requested.

Query Lock Information:

select * from pgxc_locks;

Example output:

The query results clearly show that Session 2 is blocked by Session 1. The block_query indicates the blocking query is the SELECT statement from Session 1, and block_pid is the PID of Session 1.

Resolution:

To resolve the hang, terminate Session 1 by executing the following command:

select pg_terminate_backend(140290346104576);

Conclusion:

The TRUNCATE hang issue in GBase 8c can be caused by various factors, and it requires careful analysis and resolution based on the specific circumstances. To troubleshoot such issues, database logs, system resource monitoring, and other relevant information should be utilized to identify the root cause and apply the necessary fixes. Additionally, optimizing database configurations and adjusting system resource allocations can enhance the performance and stability of the GBase database.