Modeling "transactional" behavior, modifying two tables

Hello!

Evaluating ScyallDB for PosstgreSQL replacement and I need help in modeling of “transactional” scenario in Scylladb.

I have one table, kind of “resources”, and second table which holds information about user paid quota, so user can create and store only N resources in resource table.

I’m trying to model scenario when quota value incrementing/decrementing when resource created/deleted, but it is kind of untrivial.

For now I came up with:

  • resource table with idempotent resource_id (has same value on request retry)
  • and quota table (prepopulating with quota values for each resource type after user registration) used for “locks”
CREATE TABLE IF NOT EXISTS resource
(
    user_id    TEXT,
    resource_type SMALLINT,
    resource_id   TEXT,
    updated_at    TIMESTAMP,
    PRIMARY KEY ( (user_id, resource_type, resource_id), updated_at )
)

;

CREATE TABLE IF NOT EXISTS resource_quota
(
    user_id            TEXT,
    resource_type      SMALLINT,
    locked_at          TIMESTAMP,
    locked_resource_id TEXT,
    quota              BIGINT,
    used_value         BIGINT,

    PRIMARY KEY ( (user_id, resource_type) )
)
;

and this workflow:

    1. create lock with lwt
UPDATE resource_quota USING TTL 300
SET
	locked_at = ?,
	locked_resource_id = ?
WHERE
	user_id = ?
	AND resource_type = ?
IF
	-- no active locks
	locked_at = NULL

	-- row exists
	AND quota != null
	AND used_value != null
	AND used_value <= quota
    1. create resource with lwt
INSERT INTO resource (
	user_id,
	resource_type,
	resource_id,
	updated_at,
)
VALUES (?,?,?,?)
IF NOT EXISTS
    1. unlock and update quota usage value
UPDATE resource_quota
SET
	locked_at = NULL,
	locked_resource_id = NULL,
	used_value = ?
WHERE
	user_id = ?
	AND resource_type = ?
IF
	locked_resource_id = ?
	AND used_value < quota
    1. prepare response and send it to client

Edge cases:

  • break executiion and retry after step1: check lock exists (lwt not applied) and check locked_resource_id still the same, go to step 2)
  • break executiion and retry after step2: the same for lock checking, resource wont’be created, and we get resource_id of existed row, check it is the same and we are ok, go to step 3
  • all possible breaks, when we reach step 3 on retrying request:
    • lock was applied (new lock on step 1) AND resource creation was applied (resource created on step 2) → unlock with usage increment
    • lock was applied (new lock on step 1) AND resource creation was not applied (resource existed on step 2) → unlock, usage without modification (it was done on the previous request)
    • lock was not applied (old lock on step 1) AND resource creation was applied (resource created on step 2) → unlock with usage decrement
    • lock was not applied (old lock on step 1) AND resource creation was not applied (resource existed on step 2) → unlock, usage without modification (it was done on the previous request)

So, is it idempotent workflow? Am I missing something?
Is it correct usage of lwt?

For now it seems to me it is ok, but I’m not sure, please advise.

Thanks in advance.

#transaction #atomic lwt acid