Originally from the User Slack
@TN:
what would be the equivalent of this SQL scenario in scylla?
<transaction start>
1. select value v1 from column c1 (e.g. item_id)
2. select value v2 from column c2 (e.g. item_amount)
3. do operations based on v1 and v2 (e.g. new inventory item_amount)
4. insert or update a row based on the above computation (e.g. create a new inventory item or update its amount)
<transaction commit>
I haven’t found any information on how to select values in a batch statement, so since they can change in the meantime (after step 2), you’d need to use counters to ensure that columns are updated correctly but that means that you’d have to update all such types to counter types that take up 64 bits. Is that the correct way?
And if the value doesn’t exist then I think I could just create some transaction that uses IF NOT EXISTS and first check whether the counter update was successful and if not then use the insert?
@Felipe_Cardeneti_Mendes:
- Read item_id, item_amount
- If exists:
a. SET item_amount = ? AND tx_id=? IF item_amount = ?
i. Upon success, great
ii. Upon failure, check tx_id. If tx_id is the one you’ve set, then you know your transaction succeeded but failed to reach the client for some reason, proceed.
iii. If tx_id is different, then you didn’t get consensus and you must retry.
- Write to a log table (that way you can reconciliate if the item never proceeded to a checkout, plus audit, etc
- If not exists, then just insert it (unless you have a situation where new items automagically show up on your application and endpoints can start interacting with them. In this case, then
IF NOT EXISTS
Don’t use a counter for that. Regular int column.