Assume a use case where we have 3 caches, called companies, employees and customers. A company record owns multiple employee records and multiple customer records. Employees have their own primary key and a foreign key companyId which references their owner company record. We use ignite to place an index on the Employee companyId field, either using SQL Create Index or the @QuerySqlField(index = true) annotation. The customer cache is setup in exactly the same way as the employee cache.
Let's say we want to delete the company, so we want to run the following SQL statements within a single transaction to ensure the company and its child records are either entirely deleted or entirely not deleted if the transaction fails (so we keep referential integrity).
DELETE FROM companies where id=companyId2Delete;
DELETE FROM employees where companyId=companyId2Delete;
DELETE FROM customers where companyId=companyId2Delete;
We therefore need to do this within a transaction in ignite, as multiline SQL statements don't work in ignite. However for transactions to work with ignite using SQL, the 3 caches need to be defined with atomicity=TRANSACTIONAL_SNAPSHOT (see here and here). The docs also say that TRANSACTIONAL_SNAPSHOT is in beta release and should not be used for production.
If we use the key-value API only, we can use atomicity type TRANSACTIONAL instead, which is fully supported (see here). However the SQL delete statements which delete from employees and customers use the index we created in each cache on companyId, so the delete is efficient. If we delete these records using the key-value API only within a transaction, I don't see any way of using the index on companyId (which appears to only be available to the SQL). Presumably we'd have to scan the entire employees table instead, and same for customers, which would be very slow. We could use an affinity key but that would only ensure that entities within the same company are stored on the same ignite node, it presumably wouldn't use the index properly.
What's the best way to do this?
Is there a way to get the key-value API to use the index on companyId when removing the records? (bearing in mind companyId is not the primary key for employees and customers?)
Within a single transaction - i.e. between tx =ignite.transactions().txStart() and tx.commit() - could I (1) use SQL queries to get all the primary keys in customers and employees with companyId=companyId2Delete (which uses the index), (2) hold these keys temporarily in memory and (3) use IgniteCache.removeAll(keys) to delete these keys? Would this be supported with atomicity TRANSACTIONAL because I'm only using SQL to query and not manipulate data? What happens if another request tries to add an employee to the company at the same time?
Could I use 100% SQL with TRANSACTIONAL_SNAPSHOT and hope it comes out of beta soon?
Edit. It looks like ignite are removing Multiversion Concurrency Control (MVCC), see here and here. MVCC is the functionality that powers TRANSACTIONAL_SNAPSHOT (see here). It therefore looks to me that ignite is going to drop support for transactions with SQL.
Unfortunately now it is not possible to use SQL index from Cache API operations and performing SQL queries inside cache API transaction as well.
Concerning MVCC removing, looks like its true only for 2.x generation. AFAIK in Ignite 3.x this features (MVCC transactions, SQL index sharing with Cache API) should be present.