Hi, > 1. Is MVCC the only atomicity mode that supports SQL transactions?
Yes, only TRANSACTIONAL_SNAPSHOT atomicity mode is applicable to SQL. > What if I create a table with TRANSACTIONAL atomicity? Will it fall back to like optimistic/pessimistic transactions (OCC/PCC), or just no transaction supports at all? If OCC/PCC is applied, what are the limitations? TRANSACTIONAL atomicity mode works only with KV API. > 2 (1-yes). Is that possible to implement an external cache store that provides some sort of transaction support that could work for SQL OCC/PCC, any suggestions on supplemental materials that are helpful for the goal? No, it is impossible now. Moreover, SQL is not intended for queries over cache store storages. > 3 (1-false). What are the plans for SQL transaction support later on? Will it likely be supported in the following 2.x versions? Currently there is no (at least I don't know about it) the transactional SQL in road map for 2.x. > I've noticed Ignite 3.x initiative that regards tables as first-class citizens so that SQL transactions and K-V transactions would be the same thing and natively be supported? Yes, Apache Ignite 3 goal is to provide transactions with strong serializable consistency. KV API and SQL will use the same transactional protocol and will not provide weak atomic guaranties. Nevertheless, single KV read/write operations will have minimal transactional overhead for most cases. On Tue, Aug 2, 2022 at 3:26 PM Yijie Shen <henry.yijies...@gmail.com> wrote: > > Hi, > > I notice that "Caches with the TRANSACTIONAL_SNAPSHOT atomicity mode > support SQL transactions"[1], but there are also notes on deprecation of ` > TRANSACTIONAL_SNAPSHOT` since Ignite 2.12 due to lots of limitations and > are not considered production ready [2]. > > I have a few questions about SQL transactions in Ignite: > > 1. Is MVCC the only atomicity mode that supports SQL transactions? What if > I create a table with TRANSACTIONAL atomicity? Will it fall back to like > optimistic/pessimistic transactions (OCC/PCC), or just no transaction > supports at all? If OCC/PCC is applied, what are the limitations? > > 2 (1-yes). Is that possible to implement an external cache store that > provides some sort of transaction support that could work for SQL OCC/PCC, > any suggestions on supplemental materials that are helpful for the goal? > > 3 (1-false). What are the plans for SQL transaction support later on? Will > it likely be supported in the following 2.x versions? I've noticed Ignite > 3.x initiative that regards tables as first-class citizens so that SQL > transactions and K-V transactions would be the same thing and natively be > supported? > > Best, > Yijie > > [1] https://ignite.apache.org/docs/latest/transactions/mvcc > [2] https://issues.apache.org/jira/browse/IGNITE-15757