> In the case that the condition is met, is the mutation applied at that point, > or has it already happened and there is something like a rollback segment?
The condition is a part of the transaction execution, so no mutation is applied until it has been evaluated – there is no rollback. > What is the case when the condition is not met and what is presented to the > end-user? I think you can expect to have any SELECT/RETURN (whatever we settle on) results returned, along with FALSE for the executed result set. > More importantly, what happens with respect to the A & I in ACID when the > transaction is applied? Not sure what you mean? They’re maintained at all times, but would be happy to explain more if I can understand the question better. > If UPDATE is used, returning the number of rows changed would be helpful. Do we support updates that affect an uncertain number of rows at the moment? Besides DELETE, for which we don’t want to calculate it, as it’s costlier. > Is this something that can be done interactively in cqlsh or does it all have > to be submitted in one statement block? These are non-interactive, so it needs to be declared in a single statement. I think Accord can be extended to natively support interactive transactions in future, in a manner consistent with its fast non-interactive transactions, but that’s a whole other endeavour. From: Patrick McFadin <pmcfa...@gmail.com> Date: Sunday, 5 June 2022 at 01:47 To: dev <dev@cassandra.apache.org> Subject: Re: CEP-15 multi key transaction syntax I've been waiting for this email! I'll echo what Jeff said about how exciting this is for the project. On the SELECT inside the transaction: In the first example, I'm making an assumption that you are doing a select on a partition key and only expect one result but is any valid CQL SELECT allowed here? If 'model' were a non-partition key column name and was indexed, then you could potentially have multiple rows returned and that isn't an allowed operation. Are only partition key lookups allowed or is there some logic looking for only one row? I'm asking because I can see in reverse time series models where you can select the latest temperature SELECT temperature FROM weather_station WHERE id=1234 AND DATE='2022-06-04' LIMIT 1; (also, horrible example. Everyone knows that the return value for a Pinto.is_running will always evaluate to FALSE) On COMMIT TRANSACTION: So much to unpack here. In the case that the condition is met, is the mutation applied at that point, or has it already happened and there is something like a rollback segment? What is the case when the condition is not met and what is presented to the end-user? More importantly, what happens with respect to the A & I in ACID when the transaction is applied? If UPDATE is used, returning the number of rows changed would be helpful. Is this something that can be done interactively in cqlsh or does it all have to be submitted in one statement block? I'll stop here for now. Patrick On Sat, Jun 4, 2022 at 3:34 PM bened...@apache.org<mailto:bened...@apache.org> <bened...@apache.org<mailto:bened...@apache.org>> wrote: > The returned result set is after the updates are applied? Returning the prior values is probably more powerful, as you can perform unconditional updates and respond to the prior state, that you otherwise would not know. It’s also simpler to implement. My inclination is to require that SELECT statements are declared first, so that we leave open the option of (in future) supporting SELECT statements in any place in the transaction, returning the values as of their position in a sequential execution of the statements. > And would you allow a transaction that had > 1 named select and no > modification statements, but commit if 1=1 ? My preference is that the IF condition is anyway optional, as it is much more obvious to a user than concocting some always-true condition. But yes, read-only transactions involving multiple tables will definitely be supported. From: Jeff Jirsa <jji...@gmail.com<mailto:jji...@gmail.com>> Date: Saturday, 4 June 2022 at 22:49 To: dev@cassandra.apache.org<mailto:dev@cassandra.apache.org> <dev@cassandra.apache.org<mailto:dev@cassandra.apache.org>> Subject: Re: CEP-15 multi key transaction syntax And would you allow a transaction that had > 1 named select and no modification statements, but commit if 1=1 ? > On Jun 4, 2022, at 2:45 PM, Jeff Jirsa > <jji...@gmail.com<mailto:jji...@gmail.com>> wrote: > > > >> On Jun 3, 2022, at 8:39 AM, Blake Eggleston >> <beggles...@apple.com<mailto:beggles...@apple.com>> wrote: >> >> Hi dev@, > > First, I’m ridiculously excited to see this. > >> >> I’ve been working on a draft syntax for Accord transactions and wanted to >> bring what I have to the dev list to solicit feedback and build consensus >> before moving forward with it. The proposed transaction syntax is intended >> to be an extended batch syntax. Basically batches with selects, and an >> optional condition at the end. To facilitate conditions against an arbitrary >> number of select statements, you can also name the statements, and reference >> columns in the results. To cut down on the number of operations needed, >> select values can also be used in updates, including some math operations. >> Parameterization of literals is supported the same as other statements. >> >> Here's an example selecting a row from 2 tables, and issuing updates for >> each row if a condition is met: >> >> BEGIN TRANSACTION; >> SELECT * FROM users WHERE name='blake' AS user; >> SELECT * from cars WHERE model='pinto' AS car; >> UPDATE users SET miles_driven = user.miles_driven + 30 WHERE name='blake'; >> UPDATE cars SET miles_driven = car.miles_driven + 30 WHERE model='pinto'; >> COMMIT TRANSACTION IF car.is_running; >> >> This can be simplified by naming the updates with an AS <name> syntax. If >> updates are named, a corresponding read is generated behind the scenes and >> its values inform the update. >> >> Here's an example, the query is functionally identical to the previous >> query. In the case of the user update, a read is still performed behind the >> scenes to enable the calculation of miles_driven + 30, but doesn't need to >> be named since it's not referenced anywhere else. >> >> BEGIN TRANSACTION; >> UPDATE users SET miles_driven += 30 WHERE name='blake'; >> UPDATE cars SET miles_driven += 30 WHERE model='pinto' AS car; >> COMMIT TRANSACTION IF car.is_running; >> >> Here’s another example, performing the canonical bank transfer: >> >> BEGIN TRANSACTION; >> UPDATE accounts SET balance += 100 WHERE name='blake' AS blake; >> UPDATE accounts SET balance -= 100 WHERE name='benedict' AS benedict; >> COMMIT TRANSACTION IF blake EXISTS AND benedict.balance >= 100; >> >> As you can see from the examples, column values can be referenced via a dot >> syntax, ie: <select_name>.<column> -> select1.value. Since the read portion >> of the transaction is performed before evaluating conditions or applying >> updates, values read can be freely applied to non-primary key values in >> updates. Select statements used either in checking a condition or creating >> an update must be restricted to a single row, either by specifying the full >> primary key or a limit of 1. Multi-row selects are allowed, but only for >> returning data to the client (see below). >> >> For evaluating conditions, = & != are available for all types, <, <=, >, >= >> are available for numerical types, and EXISTS, NOT EXISTS can be used for >> partitions, rows, and values. If any column references cannot be satisfied >> by the result of the reads, the condition implicitly fails. This prevents >> having to include a bunch of exists statements. > > Is there a new keyword for “partition (not) exists” or is it inferred by the > select? > >> >> On completion, an operation would return a boolean value indicating the >> operation had been applied, and a result set for each named select (but not >> named update). We could also support an optional RETURN keyword, which would >> allow the user to only return specific named selects (ie: RETURN select1, >> select2). >> > > The returned result set is after the updates are applied? > > >> Let me know what you think! >> >> Blake
