Questions about SQL transactions

[Yijie Shen <he...@gmail.com>]

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

Re: Questions about SQL transactions

[Andrey Gura <ag...@apache.org>]

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 <he...@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