maedhroz commented on code in PR #4572: URL: https://github.com/apache/cassandra/pull/4572#discussion_r3076267585
########## doc/modules/cassandra/pages/developing/cql/transactions.adoc: ########## @@ -0,0 +1,987 @@ += Accord Transactions +:page-nav-title: Transactions + +Accord provides strong consistency and ACID guarantees for Cassandra operations. +When enabled on a table, **all CQL operations automatically execute through Accord** - no code changes required. +For complex multi-step operations, explicit transaction syntax (`BEGIN TRANSACTION ... COMMIT TRANSACTION`) allows you to read, apply conditions, and write atomically across multiple partitions and tables. + +== Overview + +=== Key Benefits + +* **Automatic Strong Consistency**: Normal CQL reads and writes become linearizable when `transactional_mode='full'` +* **ACID Guarantees**: Atomicity, Consistency, Isolation, and Durability across multiple operations +* **Multi-Partition Consistency**: Coordinate updates across different partition keys +* **Multi-Table Support**: Update multiple tables atomically within a single transaction +* **Complex Business Logic**: Support for conditional operations with multiple steps + +=== When to Use Explicit Transactions + +While normal CQL operations are automatically transactional with `transactional_mode='full'`, use explicit `BEGIN TRANSACTION ... COMMIT TRANSACTION` syntax when you need: + +* **Read-Modify-Write Patterns**: Check a condition before making changes +* **Complex Business Logic**: Multi-step operations that must be atomic +* **Cross-Partition Operations**: Updates that span multiple partition keys +* **Multi-Table Atomicity**: Ensure related changes across tables succeed or fail together + +=== Safety & Consistency + +Accord ensures data integrity through: + +* **Strict Serializability**: Transactions execute as if in a single, total order that respects real-time ordering +* **Conflict Detection**: Automatic handling of concurrent access to the same data +* **Atomic Commitment**: All changes commit together or none at all +* **Durable Writes**: Committed transactions survive node failures + +== Getting Started + +=== Prerequisites + +Before using transactions: + +. **Enable Accord globally** in `cassandra.yaml`: ++ +[source,yaml] +---- +accord: + enabled: true +---- + +. **Enable transactional mode on tables**: ++ +[source,cql] +---- +CREATE TABLE users ( + id UUID PRIMARY KEY, + email text, + balance decimal +) WITH transactional_mode = 'full'; +---- + +See <<transactional-modes>> for detailed mode explanations. + +=== Normal CQL Operations Are Transactional + +When a table has `transactional_mode='full'`, your existing CQL statements are automatically executed through Accord. **You do not need to rewrite your application code.** + +[source,cql] +---- +-- These normal CQL operations are automatically transactional: + +-- Reads are executed through Accord +SELECT id, email, balance FROM users WHERE id = 123e4567-e89b-12d3-a456-426614174000; + +-- Writes are executed through Accord +INSERT INTO users (id, email, balance) VALUES (123e4567-e89b-12d3-a456-426614174000, '[email protected]', 100.00); + +UPDATE users SET balance = 50.00 WHERE id = 123e4567-e89b-12d3-a456-426614174000; + +DELETE FROM users WHERE id = 123e4567-e89b-12d3-a456-426614174000; +---- + +Each statement executes as an individual Accord transaction, providing linearizability, consistency, and durability. Migrating to Accord can be as simple as enabling `transactional_mode='full'` on your tables. + +=== Your First Explicit Transaction + +[source,cql] +---- +BEGIN TRANSACTION + SELECT id, email, balance FROM users WHERE id = 123e4567-e89b-12d3-a456-426614174000; +COMMIT TRANSACTION +---- + +This simple transaction reads a single row with full ACID guarantees. + +== Transaction Syntax + +=== Basic Structure + +All transactions follow this pattern: + +[source,cql] +---- +BEGIN TRANSACTION + [LET assignments] + [SELECT statements] + [IF conditions THEN] + [modification statements] + [END IF] +COMMIT TRANSACTION +---- + +=== LET Assignments + +LET statements read data and bind it to variables for use later in the transaction: + +[source,cql] +---- +BEGIN TRANSACTION + LET user_data = (SELECT id, balance FROM users WHERE id = ?); + LET account_data = (SELECT account_type FROM accounts WHERE user_id = ?); + + IF user_data.balance > 100 AND account_data.account_type = 'premium' THEN + UPDATE users SET balance = balance - 50 WHERE id = ?; + END IF +COMMIT TRANSACTION +---- + +**LET Requirements:** + +* Each LET must specify a unique variable name +* SELECT must return exactly one row (use `LIMIT 1` if needed) +* All partition key columns must be specified with equality operators +* Cannot use `ORDER BY`, `GROUP BY`, or aggregation functions +* Cannot use range queries or multi-partition operations + +**Valid LET Examples:** +[source,cql] +---- +LET user_data = (SELECT balance, status FROM users WHERE id = ?); +LET order_info = (SELECT total, shipping_fee FROM orders WHERE id = ? LIMIT 1); +LET static_config = (SELECT max_attempts FROM config WHERE setting_type = 'retry'); +---- + +**Invalid LET Examples:** +[source,cql] +---- +-- Missing LIMIT 1 with potential multiple results +LET users = (SELECT * FROM users WHERE status = 'active'); + +-- Range query not allowed +LET recent = (SELECT * FROM events WHERE id > ? AND id < ?); + +-- Aggregation not supported +LET total = (SELECT COUNT(*) FROM orders WHERE user_id = ?); +---- + +=== Row References + +Access fields from LET variables using dot notation: + +[source,cql] +---- +BEGIN TRANSACTION + LET current_user = (SELECT balance, status FROM users WHERE id = ?); + + -- Access fields with dot notation + SELECT current_user.balance, current_user.status; + + -- Use in conditions + IF current_user.balance > 0 AND current_user.status = 'active' THEN + UPDATE users SET balance = balance - 25 WHERE id = ?; + END IF +COMMIT TRANSACTION +---- + +[[row-reference-limitations]] +**Row Reference Limitations:** + +Row reference arithmetic in SET clauses and row references in VALUES are not currently supported. Pass values as parameters instead. See xref:developing/cql/transactions-limitations.adoc[Transaction Limitations] for complete details on unsupported syntax and workarounds. + +[source,cql] +---- +-- Application code computes values, passes as parameters +BEGIN TRANSACTION + LET user_data = (SELECT balance FROM users WHERE id = ?); + + IF user_data.balance >= ? THEN -- Pass order_total as parameter + UPDATE users SET balance = balance - ? WHERE id = ?; -- Pass order_total + END IF +COMMIT TRANSACTION +---- + +=== Conditional Logic + +Add conditional logic to transactions with IF blocks: + +[source,cql] +---- +BEGIN TRANSACTION + LET sender = (SELECT balance FROM accounts WHERE user_id = ?); + + IF sender.balance >= 100 THEN + UPDATE accounts SET balance = balance - 100 WHERE user_id = ?; + UPDATE accounts SET balance = balance + 100 WHERE user_id = ?; + END IF +COMMIT TRANSACTION +---- + +**Supported Operators:** + +* Comparison: `=`, `<`, `<=`, `>`, `>=`, `!=` +* Null checks: `IS NULL`, `IS NOT NULL` +* Logical: `AND` (only - `OR` is not supported) + +**Complex Condition Examples:** +[source,cql] +---- +-- Multiple conditions with AND +IF user_data.balance >= 100 AND user_data.status = 'active' + AND user_data.credit_limit > 150 THEN + -- statements +END IF + +-- Null checking +IF account_info IS NOT NULL AND account_info.balance > 0 THEN + -- statements +END IF +---- + +**Important Notes:** + +* Only `AND` is supported, not `OR` +* Null handling is strict (any null comparison returns false) +* All modification statements must be inside the IF block when using conditions + +=== Returning Results Review Comment: nit: There's an argument for moving this section on how to return results up above the conditional stuff, and even the unconditional writes. That's kind of assuming a model that linearly introduces LET, SELECT, simple writes, conditional writes, etc. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
