Help Center/ GaussDB/ Feature Guide(Distributed_V2.0-8.x)/ Partitioned Table/ Partition Concurrency Control/ DQL/DML-DDL Concurrency
Updated on 2025-05-29 GMT+08:00
View PDF
Share

DQL/DML-DDL Concurrency

Performing table-level DDL operations or partition-level DDL operations SPLIT or MERGE on a partitioned table will apply a level-8 lock to the partitioned table to block all DQL/DML statements.

Performing partition-level DDL operations ADD, DROP, EXCHANGE, TRUNCATE, MOVE, or RENAME on a partitioned table will apply a level-4 lock to the partitioned table and a level-8 lock to the target partition. When DQL/DML and DDL statements are used in different partitions, concurrent execution is supported. When DQL/DML and DDL statements are used in the same partition, services triggered later will be blocked.

  • During partition-level DDL operations, do not perform DQL/DML operations on the target partition at the same time.
  • If the target partitions of the concurrent DDL and DQL/DML statements overlap, the DQL/DML statements may occur before or after the DDL statements due to serial blocking. You need to know the possible expected results. For example, when TRUNCATE and INSERT take effect on the same partition, if TRUNCATE is triggered before INSERT, data exists in the target partition after the statements are complete. If TRUNCATE is triggered after INSERT, no data exists in the target partition after the statements are complete.
  • If a partitioned table is a bucket table with a global index, the global index is rebuilt when partition-level DDL operations such as MERGE, EXCHANGE, and SET TABLESPACE are performed on the table, blocking DML services during this period.

DQL/DML-DDL Concurrency Across Partitions

GaussDB supports DQL/DML-DDL concurrency across partitions.

The following provides some examples of supporting concurrency in the partitioned table range_sales. 
CREATE TABLE range_sales
(
    product_id     INT4 NOT NULL,
    customer_id    INT4 NOT NULL,
    time_id        DATE,
    channel_id     CHAR(1),
    type_id        INT4,
    quantity_sold  NUMERIC(3),
    amount_sold    NUMERIC(10,2)
)
PARTITION BY RANGE (time_id)
(
    PARTITION time_2008 VALUES LESS THAN ('2009-01-01'),
    PARTITION time_2009 VALUES LESS THAN ('2010-01-01'),
    PARTITION time_2010 VALUES LESS THAN ('2011-01-01'),
    PARTITION time_2011 VALUES LESS THAN ('2012-01-01')
);

CREATE TABLE temp
(
    product_id     INT4 NOT NULL,
    customer_id    INT4 NOT NULL,
    time_id        DATE,
    channel_id     CHAR(1),
    type_id        INT4,
    quantity_sold  NUMERIC(3),
    amount_sold    NUMERIC(10,2)
);
Typically, the partitioned table supports the following concurrent services: 
-- In case 1, inserting partition time_2011 and truncating partition time_2008 do not block each other.
\parallel on
INSERT INTO range_sales VALUES (455124, 92121433, '2011-09-17', 'X', 4513, 7, 17);
ALTER TABLE range_sales TRUNCATE PARTITION time_2008 UPDATE GLOBAL INDEX;
\parallel off

-- In case 2, querying partition time_2010 and exchanging partition time_2009 do not block each other.
\parallel on
SELECT COUNT(*) FROM range_sales PARTITION (time_2010);
ALTER TABLE range_sales EXCHANGE PARTITION (time_2009) WITH TABLE temp UPDATE GLOBAL INDEX;
\parallel off

-- In case 3, updating partitioned table range_sales and dropping partition time_2008 do not block each other. This is because the SQL statement with a condition (partition pruning) updates the time_2010 and time_2011 partitions only.
\parallel on
UPDATE range_sales SET channel_id = 'T' WHERE channel_id = 'X' AND time_id > '2010-06-01';
ALTER TABLE range_sales DROP PARTITION time_2008 UPDATE GLOBAL INDEX;
\parallel off

-- In case 4, any DQL/DML statement of partitioned table range_sales and adding partition time_2012 do not block each other. This is because ADD PARTITION is invisible to other statements.
\parallel on
DELETE FROM range_sales WHERE channel_id = 'T';
ALTER TABLE range_sales ADD PARTITION time_2012 VALUES LESS THAN ('2013-01-01');
\parallel off

DQL/DML-DDL Concurrency on the Same Partition

GaussDB does not support DQL/DML-DDL concurrency on the same partition. A triggered statement will block the subsequent statements.

In principle, you are advised not to perform DQL/DML operations on a partition when performing DDL operations on the partition. This is because the status of the target partition changes abruptly, which may cause unexpected statement query results.

If the DQL/DML and DDL target partitions overlap due to improper statements or pruning failures, consider the following two scenarios:

Scenario 1: If DQL/DML statements are triggered before DDL statements, DDL statements are blocked until DQL/DML statements are committed.

Scenario 2: If DDL statements are triggered before DQL/DML statements, DQL/DML statements are blocked and are executed after DDL statements are committed. The result may be unexpected. To ensure data consistency, the expected result is formulated based on the following rules:

  • ADD PARTITION

    During ADD PARTITION, a new partition is generated and is invisible to the triggered DQL/DML statements. There is no blocking.

  • DROP PARTITION

    During DROP PARTITION, an existing partition is dropped, and the DQL/DML statements triggered on the target partition are blocked. After the blocking is complete, the processing on the partition will be skipped.

  • TRUNCATE PARTITION

    During TRUNCATE PARTITION, data is cleared from an existing partition, and the DQL/DML statements triggered on the target partition are blocked. After the blocking is complete, the processing on the partition continues.

    Note that no data can be queried in the target partition during this period because no data exists in the target partition after the TRUNCATE operation is committed.

  • EXCHANGE PARTITION

    The EXCHANGE PARTITION exchanges an existing partition with an ordinary table. During this period, the DQL/DML statements on the target partition are blocked. After the blocking is complete, the partition processing continues. The actual data of the partition corresponds to the original ordinary table.

    Exception: If the global index exists in the partitioned table, the EXCHANGE statement contains the UPDATE GLOBAL INDEX clause, and the partitioned table query triggered during this period uses the global index, the data in the partition after the exchange cannot be queried. As a result, an error is reported during the query after the blocking is complete.

    ERROR: partition xxxxxx does not exist on relation "xxxxxx"

    DETAIL: this partition may have already been dropped by concurrent DDL operations EXCHANGE PARTITION

  • SPLIT PARTITION

    SPLIT PARTITION blocks DQL/DML operations on the entire table, including those on the target partition. After the SPLIT operation is committed, the DQL/DML operation is performed based on the partitioned table structure after the SPLIT operation is complete.

  • MERGE PARTITION

    MERGE PARTITION blocks DQL/DML operations on the entire table, including those on the target partition. After the MERGE operation is committed, the DQL/DML operation is performed based on the partitioned table structure after the MERGE operation is complete.

  • RENAME PARTITION

    RENAME PARTITION does not change the partition structure information. The DQL/DML statements triggered during this period do not encounter any exception but are blocked until the RENAME operation is committed.

  • MOVE PARTITION

    MOVE PARTITION does not change the partition structure information. The DQL/DML statements triggered during this period do not encounter any exception but are blocked until the MOVE operation is committed.

    • During the DQL/DML operations, if multiple DDL operations are consecutively performed on the partition where DQL/DML operations are performed, there is a low probability that an error is reported, indicating that the partition cannot be found and has been deleted by a DDL operation.
    • There is a low probability that service deadlock or lock timeout occurs when DQL/DML-DDL statements are concurrently executed on the same partition. This is because some DQL/DML operations apply partition locks only on DNs. If the lock sequence of DQL/DML operations on different DNs is different from that of DDL operations, there is a possibility that a deadlock or lock timeout occurs. To avoid deadlocks, you can set the GUC parameter enable_partition_ddl_lowlevel_lock to off. However, the performance will be affected.