> I would love hearing from people on what they think.
^^ It would be great to have more participants in this conversation
> For context, my questions earlier were based on my 20+ years of using SQL
> transactions across different systems.
We probably don’t come from a very different place. I spent too many years with
T-SQL.
> When you start a SQL transaction, you are creating a branch of your data that
> you can operate with until you reach your desired state and then merge it
> back with a commit.
That’s the essential complexity we’re grappling with: how much do we permit
your “branch” to do, how do we let you express it, and how do we let you
express conditions?
We must balance the fact we cannot afford to do everything (yet), against the
need to make sure what we do is reasonably intuitive (to both CQL and SQL
users) and consistent – including with whatever we do in future.
Right now, we have the issue that read-your-writes introduces some complexity
to the semantics, particularly around the conditions of execution.
LWTs impose conditions on the state of all records prior to execution, but
their API has a lot of shortcomings. The proposal of COMMIT IF (Boolean expr)
is most consistent with this approach. This can be confusing, though, if the
condition is evaluated on a value that has been updated by a prior statement in
the batch – what value does this global condition get evaluated against?*
SQL has no such concept, but also SQL is designed to be interactive. Depending
on the dialect there’s probably a lot of ways to do this non-interactively in
SQL, but we probably cannot cheaply replicate the functionality exactly as we
do not (yet) support interactive transactions that they were designed for. To
submit a conditional non-interactive transaction in SQL, you would likely use
IF (X) THEN
ROLLBACK
RETURN (ERRCODE)
END IF
or
IF (X) THEN RAISERROR
So, that is in essence the question we are currently asking: do we want to have
a more LWT-like approach (and if so, how do we address this complexity for the
user), or do we want a more SQL-like approach (and if so, how do we modify it
to make non-interactive transactions convenient, and implementation tractable)
* This is anyway a shortcoming of existing batches, I think? So it might be we
can sweep it under the rug, but I think it will be more relevant here as people
execute more complex transactions, and we should ideally have semantics that
will work well into the future – including if we later introduce interactive
transactions.
From: Patrick McFadin <[email protected]>
Date: Saturday, 11 June 2022 at 15:33
To: dev <[email protected]>
Subject: Re: CEP-15 multi key transaction syntax
I think the syntax is evolving into something pretty complicated, which may be
warranted but I wanted to take a step back and be a bit more reflective on what
we are trying to accomplish.
For context, my questions earlier were based on my 20+ years of using SQL
transactions across different systems. That's my personal bias when I see the
word "database transaction" in this case. When you start a SQL transaction, you
are creating a branch of your data that you can operate with until you reach
your desired state and then merge it back with a commit. Or if you don't like
what you see, use a rollback and act like it never happened. That was the
thinking when I asked about interactive sessions. If you are using a driver,
that all happens in a batch. I realize that is out of scope here, but that's
probably knowledge that is pre-installed in the majority of the user community.
Getting to the point, which is developer experience. I'm seeing a philosophical
fork in the road which hopefully will generate some comments in the larger user
community.
Path 1)
Mimic what's already been available in the SQL community, using existing CQL
syntax. (SQL Example using JDBC: https://www.baeldung.com/java-jdbc-auto-commit)
Path 2)
Chart a new direction with new syntax
I genuinely don't have a clear answer, but I would love hearing from people on
what they think.
Patrick
On Fri, Jun 10, 2022 at 12:07 PM
[email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>> wrote:
This might also permit us to remove one result set (the success/failure one)
and return instead an exception if the transaction is aborted. This is also
more consistent with SQL, if memory serves. That might conflict with returning
the other result sets in the event of abort (though that’s up to us
ultimately), but it feels like a nicer API for the user – depending on how
these exceptions are surfaced in client APIs.
From: [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Date: Friday, 10 June 2022 at 19:59
To: [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Subject: Re: CEP-15 multi key transaction syntax
So, thinking on it myself some more, I think if there’s an option that
*doesn’t* require the user to reason about the point at which the read happens
in order to understand how the condition is applied would probably be better.
What do you think of the IF (Boolean expr) ABORT TRANSACTION idea?
It’s compatible with more advanced IF functionality later, and probably not
much trickier to implement?
The COMMIT IF syntax is more succinct, but ambiguity isn’t ideal and we only
get one chance to make this API right.
From: Blake Eggleston <[email protected]<mailto:[email protected]>>
Date: Friday, 10 June 2022 at 18:56
To: [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Subject: Re: CEP-15 multi key transaction syntax
Yeah I think that’s intuitive enough. I had been thinking about multiple
condition branches, but was thinking about something closer to
IF select.column=5
UPDATE ... SET ... WHERE key=1;
ELSE IF select.column=6
UPDATE ... SET ... WHERE key=2;
ELSE
UPDATE ... SET ... WHERE key=3;
ENDIF
COMMIT TRANSACTION;
Which would make the proposed COMMIT IF we're talking about now a shorthand. Of
course this would be follow on work.
On Jun 8, 2022, at 1:20 PM, [email protected]<mailto:[email protected]>
wrote:
I imagine that conditions would be evaluated against the state prior to the
execution of statement against which it is being evaluated, but after the prior
statements. I think that should be OK to reason about.
i.e. we might have a contrived example like:
BEGIN TRANSACTION
UPDATE tbl SET a = 1 WHERE k = 1 AS q1
UPDATE tbl SET a = q1.a + 1 WHERE k = 1 AS q2
COMMIT TRANSACTION IF q1.a = 0 AND q2.a = 1
So q1 would read a = 0, but q2 would read a = 1 and set a = 2.
I think this is probably adequately intuitive? It is a bit atypical to have
conditions that wrap the whole transaction though.
We have another option, of course, which is to offer IF x ROLLBACK TRANSACTION,
which is closer to SQL, which would translate the above to:
BEGIN TRANSACTION
SELECT a FROM tbl WHERE k = 1 AS q0
IF q0.a != 0 ROLLBACK TRANSACTION
UPDATE tbl SET a = 1 WHERE k = 1 AS q1
IF q1.a != 1 ROLLBACK TRANSACTION
UPDATE tbl SET a = q1.a + 1 WHERE k = 1 AS q2
COMMIT TRANSACTION
This is less succinct, but might be more familiar to users. We could also
eschew the ability to read from UPDATE statements entirely in this scheme, as
this would then look very much like SQL.
From: Blake Eggleston <[email protected]<mailto:[email protected]>>
Date: Wednesday, 8 June 2022 at 20:59
To: [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Subject: Re: CEP-15 multi key transaction syntax
> It affects not just RETURNING but also conditions that are evaluated against
> the row, and if we in future permit using the values from one select in a
> function call / write to another table (which I imagine we will).
I hadn’t thought about that... using intermediate or even post update values in
condition evaluation or function calls seems like it would make it difficult to
understand why a condition is or is not applying. On the other hand, it would
powerful, especially when using things like database generated values in
queries (auto incrementing integer clustering keys or server generated
timeuuids being examples that come to mind). Additionally, if we return these
values, I guess that would solve the visibility issues I’m worried about.
Agreed intermediate values would be straightforward to calculate though.
On Jun 6, 2022, at 4:33 PM, [email protected]<mailto:[email protected]>
wrote:
It affects not just RETURNING but also conditions that are evaluated against
the row, and if we in future permit using the values from one select in a
function call / write to another table (which I imagine we will).
I think that for it to be intuitive we need it to make sense sequentially,
which means either calculating it or restricting what can be stated (or
abandoning the syntax).
If we initially forbade multiple UPDATE/INSERT to the same key, but permitted
overlapping DELETE (and as many SELECT as you like) that would perhaps make it
simple enough? Require for now that SELECTS go first, then DELETE and then
INSERT/UPDATE (or vice versa, depending what we want to make simple)?
FWIW, I don’t think this is terribly onerous to calculate either, since it’s
restricted to single rows we are updating, so we could simply maintain a
collections of rows and upsert into them as we process the execution. Most
transactions won’t need it, I suspect, so we don’t need to worry about perfect
efficiency.
From: Blake Eggleston <[email protected]<mailto:[email protected]>>
Date: Tuesday, 7 June 2022 at 00:21
To: [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Subject: Re: CEP-15 multi key transaction syntax
That's a good question. I'd lean towards returning the final state of things,
although I could understand expecting to see intermediate state. Regarding
range tombstones, we could require them to precede any updates like selects,
but there's still the question of how to handle multiple updates to the same
cell when the user has requested we return the post-update state of the cell.
On Jun 6, 2022, at 4:00 PM, [email protected]<mailto:[email protected]>
wrote:
> if multiple updates end up touching the same cell, I’d expect the last one to
> win
Hmm, yes I suppose range tombstones are a plausible and reasonable thing to mix
with inserts over the same key range.
What’s your present thinking about the idea of handling returning the values as
of a given point in the sequential execution then?
The succinct syntax is I think highly desirable for user experience, but this
does complicate it a bit if we want to remain intuitive.
From: Blake Eggleston <[email protected]<mailto:[email protected]>>
Date: Monday, 6 June 2022 at 23:17
To: [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Subject: Re: CEP-15 multi key transaction syntax
Hi all,
Thanks for all the input and questions so far. Glad people are excited about
this!
I didn’t have any free time to respond this weekend, although it looks like
Benedict has responded to most of the questions so far, so if I don’t respond
to a question you asked here, you can interpret that as “what Benedict said” :).
Jeff,
> Is there a new keyword for “partition (not) exists” or is it inferred by the
> select?
I'd intended this to be worked out from the select statement, ie: if the
read/reference is null/empty, then it doesn't exist, whether you're interested
in the partition, row, or cell. So I don't think we'd need an additional
keyword there. I think that would address partition exists / not exists use
cases?
> And would you allow a transaction that had > 1 named select and no
> modification statements, but commit if 1=1 ?
Yes, an unconditional commit (ie: just COMMIT TRANSACTION; without an IF) would
be part of the syntax. Also, running a txn that doesn’t contain updates
wouldn’t be a problem.
Patrick, I think Benedict answered your questions? Glad you got the joke :)
Alex,
> 1. Dependant SELECTs
> 2. Dependant UPDATEs
> 3. UPDATE from secondary index (or SASI)
> 5. UPDATE with predicate on non-primary key
The full primary key must be defined as part of the statement, and you can’t
use column references to define them, so you wouldn’t be able to run these.
> MVs
To prevent being spread too thin, both in syntax design and implementation
work, I’d like to limit read and write operations in the initial implementation
to vanilla selects, updates, inserts, and deletes. Once we have a solid
implementation of multi-key/table transactions supporting foundational
operations, we can start figuring out how the more advanced pieces can be best
supported. Not a great answer to your question, but a related tangent I should
have included in my initial email.
> ... RETURNING ...
I like the idea of the returning statement, but to echo what Benedict said, I
think any scheme for specifying data to be returned should apply the same to
select and update statements, since updates can have underlying reads that the
user may be interested in. I’d mentioned having an optional RETURN statement in
addition to automatically returning selects in my original email.
> ... WITH ...
I like the idea of defining statement names at the beginning of a statement,
since I could imagine mapping names to selects might get difficult if there are
a lot of columns in the select or update, but beginning each statement with
`WITH <name>` reduces readability imo. Maybe putting the name after the first
term of the statement (ie: `SELECT * AS <name> WHERE...`, `UPDATE table AS
<name> SET ...`, `INSERT INTO table AS <name> (...) VALUES (...);`) would be
improve finding names without harming overall readability?
Benedict,
> I agree that SELECT statements should be required to go first.
+1
> There only remains the issue of conditions imposed upon UPDATE/INSERT/DELETE
> statements when there are multiple statements that affect the same primary
> key. I think we can (and should) simply reject such queries for now, as it
> doesn’t make much sense to have multiple statements for the same primary key
> in the same transaction.
Unfortunately, I think there are use cases for both multiple selects and
updates for the same primary key in a txn. Selects aren’t as problematic, but
if multiple updates end up touching the same cell, I’d expect the last one to
win. This would make dealing with range tombstones a little trickier, since the
default behavior of alternating updates and range tombstones affecting the same
cells is not intuitive, but I don’t think it would be too bad.
Something that’s come up a few times, and that I’ve also been thinking about is
whether to return the values that were originally read, or the values written
with the update to the client, and there are use cases for both. I don’t
remember who suggested it, but I think returning the original values from named
select statements, and the post-update values from named update statements is a
good way to handle both. Also, while returning the contents of the mutation
would be the easiest, implementation wise, swapping cell values from the
updates named read would be most useful, since a txn won’t always result in an
update, in which case we’d just return the select.
Thanks,
Blake
On Jun 6, 2022, at 9:41 AM, Henrik Ingo
<[email protected]<mailto:[email protected]>> wrote:
On Mon, Jun 6, 2022 at 5:28 PM [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>> wrote:
> One way to make it obvious is to require the user to explicitly type the
> SELECTs and then to require that all SELECTs appear before
> UPDATE/INSERT/DELETE.
Yes, I agree that SELECT statements should be required to go first.
However, I think this is sufficient and we can retain the shorter format for
RETURNING. There only remains the issue of conditions imposed upon
UPDATE/INSERT/DELETE statements when there are multiple statements that affect
the same primary key. I think we can (and should) simply reject such queries
for now, as it doesn’t make much sense to have multiple statements for the same
primary key in the same transaction.
I guess I was thinking ahead to a future where and UPDATE write set may or may
not intersect with a previous update due to allowing WHERE clause to use
secondary keys, etc.
That said, I'm not saying we SHOULD require explicit SELECT statements for
every update. I'm sure that would be annoying more than useful.I was just
following a train of thought.
> Returning the "result" from an UPDATE presents the question should it be the
> data at the start of the transaction or end state?
I am inclined to only return the new values (as proposed by Alex) for the
purpose of returning new auto-increment values etc. If you require the prior
value, SELECT is available to express this.
That's a great point!
> I was thinking the following coordinator-side implementation would allow to
> use also old drivers
I am inclined to return just the first result set to old clients. I think it’s
fine to require a client upgrade to get multiple result sets.
Possibly. I just wanted to share an idea for consideration. IMO the temp table
idea might not be too hard to implement*, but sure the syntax does feel a bit
bolted on.
*) I'm maybe the wrong person to judge that, of course :-)
henrik
--
Henrik Ingo
+358 40 569 7354<tel:358405697354>
