I apologize if I have the wrong category, this is my first topic
I have a relational sql database that works pretty well, but scaling it is a real pain
So scylla came to my attention, in which I saw a potential salvation.
To the point
I studied the documentation of scylla, and figured out how to migrate everything but one item:
In my relational database, I use transactions to allocate usage to data.
To oversimplify, it looks like this:
BEGIN; SELECT id, line FROM table_with_important_rows WHERE usages < 123 AND concurrent_usages < 10 FOR UPDATE LIMIT 50; // this blocks 50 matching rows, until they are updated, or the transaction is rolled back UPDATE table_with_important_rows SET usages = usages + 1 AND concurrent_usages = concurrent_usages + 1 WHERE id IN (...) // and this consequently increases the usage counts, and releases the blockage COMMIT;
After reading probably all the articles on the subject of LWT and filtering in scylla, here’s the best I’ve come to:
CREATE TABLE test.table_with_important_rows ( id uuid, usages int, concurrent_usages int, line text, PRIMARY KEY (id)); CREATE INDEX resource_usages_idx ON test.table_with_important_rows((id), usages); CREATE INDEX resource_concurrent_usages_idx ON test.table_with_important_rows((id), concurrent_usages);
But the problem still remains. In order to “grab the rows” I need, I have to do a read first,
because the secondary index doesn’t support update and delete operations, but even if it did,
there’s no way to limit the rows that will be updated. In addition, the secondary index cannot be used with the
counter type, which means there must be a read to increment, or decrement the value.
The problem is that I don’t see any way to perform the read and subsequent update atomically. If don’t do it atomically, there’s no way to avoid data corruption.
The only solution I see is to restrict simultaneous work with one partition at the service level,
and work with the database without blocking.
How can I solve my problem at the database level, and if there is a solution, how resource intensive is it?
This is a very popular table in my database, and there will be a lot of similar operations. I would say this is the main type of load.
Will scylla perform well in this type of operation?
Thanks in advance for the answer