If I've read this right -- https://www.cockroachlabs.com/blog/how-cockroachdb-distributes-atomic-transactions/ -- Cockroach achieves it by using a convention of writing "intents" to provisionally "change" any given key with a timestamp suffix from the hybrid logical clock which orders transactions. When reading a given key, they'll actually do a range scan of sorts over all the key's write intents to see which is the latest that successfully committed -- subsequently they'll promote write intents to genuine write "effects" and remove prior records. Their SQL is built on top of the key-value interface so they get parallel optimistic transactions "for free" -- I think YugaByte's "provisional writes" work much the same way ( https://docs.yugabyte.com/latest/architecture/transactions/distributed-txns/ ).
Seeing that Ignite would support the optimistic transactions for it's key-value cache, I assumed you were already doing something similar and I was kind of surprised that SQL optimistic transactions weren't naturally supported (hence my question). On Sat, Oct 19, 2019 at 12:06 AM Ivan Pavlukhin <[email protected]> wrote: > Hi Justin, > > Thank you for sharing a details about your use case. Quite interesting. > > It seems that in Ignite something like that is possible with > optimistic cache transactions (key-value API). Technically it seems to > be achievable when transaction protocol accumulates transaction > read-write set in memory area allocated for each transaction. Ignite > key-value transactions behaves so. But MVCC transactions was designed > to support SQL update operations as well which possible be larger than > available memory. So, new row versions are written in the same place > where a regular (already committed) data resides. Supporting multiple > not committed versions will complexify existing implementation a lot > (do not have good ideas from scratch how to implement it). > > Really interesting if cockroachdb supports something like this. > > сб, 19 окт. 2019 г. в 03:36, Justin Moore <[email protected]>: > > > > Thanks Ivan, > > > > First, apologies, I don't have a simple example to share... > > > > I'm trying to explore the possibility answering "what-if" hypothetical > queries using (SQL) transactions that don't commit (i.e. transactions that > always rollback/abort). Each what-if transaction would be like a > self-contained alternate reality that can surface information from an > alternate future -- but to do so effectively, the transaction ought to > process all statements, even though it will never (succeed to) commit, so > it must not abort early/unintentionally. > > > > I think a couple of other "distributed SQL" offerings > (cockroachDB/yugabyte) are architected in a way that makes this possible, > where traditionally "unclustered" databases (Postgres) generally seemed to > rely on locking that would prevent the capability. I was -- and still am > -- looking for other (probably distributed) options that might make this > feasible, which lead me to the Ignite docs. > > > > My specific use case is to do something kind of like a Log Sorted Merge > representation with a twist, using a shared database and a shared Write > Ahead Log (the twist is that the WAL is not strictly append-only). So > concurrent clients would assemble the current state on demand by applying > the log to the database -- which is a "snapshot" practically speaking -- in > a transaction that would not be committed (a separate "compaction" process > would apply a prefix of the log to be persisted permanently). As such, > concurrent clients are going to be trying to do the exact same writes to > the database in their transactions -- they need not commit but all other > statements should be executed. > > > > Sorry if it's a bit confusing... > > > > Cheers, > > Justin > > > > On Fri, Oct 18, 2019 at 6:31 AM Ivan Pavlukhin <[email protected]> > wrote: > >> > >> Hi, > >> > >> Currently there are no activity on optimistic transaction support for > SQL. > >> > >> A transaction will be aborted on a first write conflict. May be I got > >> it wrong, but what is the benefit of aborting later (on commit) > >> instead of earlier (on write conflict)? Perhaps a scenario written in > >> terms of Ignite operations (cache.get, cache.put, cache.query) can > >> illustrate your problem better for my understanding. > >> > >> пт, 18 окт. 2019 г. в 06:58, Justin Moore <[email protected]>: > >> > > >> > Hi, > >> > > >> > Very new to Ignite and just trying to assess its capabilities. > >> > > >> > tl;dr: are optimistic serializable sql transactions on the roadmap? > >> > > >> > It seems that currently only pessimistic repeatable_read sql > transactions are supported (in beta as of the current version -- 2.7). I'm > trying to confirm that, in Ignite, if I started two concurrent transactions > (from the same snapshot) where one intends to execute statements #1 > (compare-and-set), #2 (read-only), and #3 (whatever else), while the other > intends to execute the exact same update statements #1, #2, and #3, but > also a subsequent #4, understanding that (all but) one of those > transactions would probably fail to commit, I'm looking to clarify whether > or not the failing one would throw/abort even before reaching statement #2 > (which might be a read returning values)? > >> > > >> > If I'm reading the docs correctly it seems that in pessimistic > repeatable_read mode the transaction would fail one of the transactions at > statement #1 (due to write conflict), but if it could have been optimistic > serializable, the transaction would simply rollback at the point a commit > was attempted. Please correct me if I'm wrong. > >> > > >> > Lastly, are optimistic serializable sql transactions on the roadmap? > >> > > >> > Thanks > >> > >> > >> > >> -- > >> Best regards, > >> Ivan Pavlukhin > > > > -- > Best regards, > Ivan Pavlukhin >
