Modeling "transactional" behavior, modifying two tables

ScyllaDB
, , ,
1

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

2

Your flow looks reasonable, but you may want to have a history table to address some potential issues you’ve already outlined. See, for example, the transfers table in Getting the Most out of Lightweight Transactions in ScyllaDB - ScyllaDB .

Another idea would be to know who locked the resource_quota table, as in case the process fails in the middle, you know the authoritative process able to resume the operation (if that’s something you want), rather than waiting for the TTL to expire and failing everything until then.

One potential optimization is whether you really need resource_id to be part of the partition key on the resource table, and whether updated_at can be a non-key column. If you don’t, then you maybe don’t need used_value, and all you would have to do would be to simply acquire a resource_quota lock. Scan the resource’s partition in question (user_id, resource_type) and from there infer the user_id’s consumption.

As you acquired the lock, other instances of your application know it can read the table as needed, but it can’t issue a read-before-write because there may be another update in progress.

As you satisfy the constraints, then simply batch all updates to resource and release the lock from resource_quota.

In the end it would end up with something like (note we eliminated one LWT write):

  1. Lock resource_quota
  2. Read and Update resource accordingly
  3. Release resource_quota lock

Thus, after (2) above, you are ok with responding the client - you don’t need to wait for unlocking as you ain’t updating anything else (ie: used_value) in the resource_quota table.

This also assumes that upserting values is ok for you use case, which AFAICT it looks doable.

Hope it helps!

1 Like