# Modeling "transactional" behavior, modifying two tables
**URL:** https://forum.scylladb.com/t/modeling-transactional-behavior-modifying-two-tables/2059
**Category:** ScyllaDB
**Tags:** lwt, data-model, migration, acid
**Created:** [May 21, 2024, 5:46pm UTC](https://forum.scylladb.com/t/modeling-transactional-behavior-modifying-two-tables/2059 "2024-05-21T17:46:27Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@xonar7](https://forum.scylladb.com/u/xonar7)
#### Post date: [May 21, 2024, 5:46pm UTC](https://forum.scylladb.com/t/modeling-transactional-behavior-modifying-two-tables/2059/1 "2024-05-21T17:46:27Z")
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”
```auto
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
```auto
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
```auto
INSERT INTO resource (
user_id,
resource_type,
resource_id,
updated_at,
)
VALUES (?,?,?,?)
IF NOT EXISTS
```
-
1. unlock and update quota usage value
```auto
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
---
### Author:  [@felipemendes](https://forum.scylladb.com/u/felipemendes)
#### Post date: [July 11, 2024, 3:21pm UTC](https://forum.scylladb.com/t/modeling-transactional-behavior-modifying-two-tables/2059/2 "2024-07-11T15:21:02Z")
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](https://www.scylladb.com/2020/07/15/getting-the-most-out-of-lightweight-transactions-in-scylla/) .
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!