8.1 Overview and Use Criteria

Device measurements accumulate continuously, while inspection state and inventory quantities require updates to existing values. Using one model for both can mix source-retention requirements with state changes. Start with TRANSACTION for mutable business data and TAG/LOG for source time series.

TRANSACTION Table Characteristics

TRANSACTION supports SELECT, INSERT, UPDATE, DELETE, PRIMARY KEY, UNIQUE INDEX, and secondary indexes. It is Standard Edition only. The following three forms create the same table type.

Use CREATE TRANSACTION TABLE in public documentation and operational scripts to make the type explicit. CREATE RDB TABLE and CREATE TRX TABLE are unsupported. None of the three supported creation forms is available in Cluster. Specify CREATE LOG TABLE explicitly for LOG.

State Changes and Rollback

CREATE TRANSACTION TABLE ch8_overview (
    item_id LONG PRIMARY KEY,
    qty     INTEGER NOT NULL
);
INSERT INTO ch8_overview VALUES (42, 10);

BEGIN;
UPDATE ch8_overview SET qty = qty - 3 WHERE item_id = 42 AND qty >= 3;
SELECT item_id, qty FROM ch8_overview;
ROLLBACK;

SELECT item_id, qty FROM ch8_overview;
DROP TABLE ch8_overview;

On the same connection, the query inside the transaction shows quantity 7; after ROLLBACK, it shows 10. qty >= 3 is the business predicate that prevents a change when stock is insufficient.

Do not interpret an error-free UPDATE as business success. If no row matches, the affected row count can be 0. The application must check that the expected one row was affected before deciding to continue or roll back. This check matters more than simply substituting numbers in the SQL example.

Comparison with Other Tables

TRANSACTION can hold equipment inspection state, business history, and separate summary results. For bulk source collection, compare throughput and ingestion paths with TAG/LOG. TRANSACTION also supports Append, but do not assume identical throughput or batch boundaries. See Ingestion Methods.

LOOKUP does not replace every TRANSACTION feature. If relational transactions are required in Cluster, consider an architecture that includes a separate RDBMS.

Design Criteria

Distinguish the row identifier from the business key used to prevent duplicates. An internal number may use PRIMARY KEY, while a separate unique value such as an external-system code may need UNIQUE INDEX. Automatic numbering does not eliminate duplicate business keys.

Next, define frequent WHERE predicates and business success criteria. Decide where transactions end and what to check after errors so concurrent requests or connection failures have clear handling. Read Schema together with Transactions.