@Rocky Do deletes within a transaction commit immediately?

No, deletes within a SQL transaction do not commit immediately. Like other DML statements in StarRocks, a

DELETE

operation follows transactional atomicity and only becomes permanent once you execute a

COMMIT

statement. Key behaviors regarding deletes in transactions include: * Atomicity: If you wrap a

DELETE

within a

BEGIN

and

COMMIT

block, the changes are only applied if the transaction finishes successfully. If the session terminates or you issue a

ROLLBACK

, the deletion is undone. * Visibility within the transaction: In StarRocks (v4.0+), uncommitted changes (including deletes) are invisible to subsequent statements within the same transaction. For example, if you delete a row and then run a

SELECT

in the same transaction, you will still see that row because StarRocks uses a limited "READ COMMITTED" isolation level where a statement only sees data committed before it began. * Constraints: * Starting from v4.0 (shared-data clusters), you can perform one

UPDATE

or

DELETE

statement against a specific table within a single transaction. * The

DELETE

must precede any

INSERT

statements against the same table in that transaction. * Physical Deletion: Even after committing, the rows are not immediately removed from disk. They are marked as deleted and are physically removed later during the background compaction process. Summary: Your delete remains "pending" until you explicitly commit the transaction. _Ref: SQL Transaction_ References • loading/SQL_transaction.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md

@Rocky Only one delete in a single transaction?

Yes, there are specific limitations on how many

DELETE

statements you can use within a single SQL transaction in StarRocks, and these depend on your cluster architecture: 1. Per-Table Limit (Shared-data clusters only) In shared-data clusters (v4.0 onwards), you are limited to one

UPDATE

or

DELETE

statement per table within a single transaction. * One per table: You can delete from Table A and Table B in the same transaction, but you cannot have two separate

DELETE

statements targeting Table A. * Sequence Matters: The

DELETE

statement must be executed before any

INSERT

statements targeting that same table in the same transaction. * No Multi-Delete/Update: You cannot mix an

UPDATE

and a

DELETE

on the same table in one transaction. 2. Architecture Restriction (Shared-nothing clusters) In shared-nothing clusters (the standard deployment for most on-premises users),

UPDATE

and

DELETE

statements are currently not supported within multi-statement SQL transactions (

BEGIN...COMMIT

). * In these environments, transactions are primarily used for

INSERT

operations to ensure atomicity across multiple tables. 3. Visibility and "Read Committed" Even within the same transaction, StarRocks follows a strict READ COMMITTED isolation level where uncommitted changes are invisible to subsequent statements. * If you delete a row and then run a

SELECT

in the same transaction, the deleted row will still appear in the results until you

COMMIT

. Summary Table Feature | Shared-nothing Cluster | Shared-data Cluster (v4.0+) DELETE in Transaction | Not supported | Supported Max DELETEs per table | N/A | 1 Order in Transaction | N/A | Must come before

INSERT

Multi-table DELETE | No | Yes (1 per table) _Ref: SQL Transaction_ References • loading/SQL_transaction.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • faq/operation_maintenance_faq.md References • loading/SQL_transaction.md • faq/operation_maintenance_faq.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md

@Rocky Are deletes supported on 3.5?

Yes,

DELETE

is supported as a standalone command in StarRocks 3.5, but it is not supported within multi-statement SQL transactions (

BEGIN...COMMIT

). Here is the breakdown of how

DELETE

works in version 3.5: 1. Standalone DELETE (Supported) You can run

DELETE FROM table WHERE ...

as a single, independent command. * Primary Key Tables: Support full

DELETE WHERE

semantics, including subqueries, joins, and CTEs. * Other Tables (Duplicate, Aggregate, Unique): Only support

DELETE

based on specific conditions (usually key columns) and do not support subqueries in the

WHERE

clause. 2. Multi-statement Transactions (Not Supported) While version 3.5 introduced multi-statement SQL transactions as a beta feature, this support is limited to

INSERT

statements only. * If you attempt to put a

DELETE

statement between a

BEGIN

and

COMMIT

block in version 3.5, it will not work as expected or may return an error. * Transactional support for

UPDATE

and

DELETE

is only available starting from v4.0 and is currently restricted to shared-data clusters. Summary for v3.5 Feature | Supported in 3.5? Standalone

DELETE

| ✅ Yes

DELETE

in

BEGIN...COMMIT

| ❌ No (INSERT only)

DELETE

with Joins/CTEs | ✅ Yes (Primary Key tables) If you need to perform deletions and other operations atomically in 3.5, you must execute them as individual statements. The system will treat each one as its own implicit transaction. _Ref: StarRocks 3.5 Release Notes, SQL Transaction_ References • loading/SQL_transaction.md • release_notes/release-3.5.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md References • release_notes/release-3.5.md • loading/SQL_transaction.md