# Implement transaction in client side

**URL:** <https://forum.scylladb.com/t/implement-transaction-in-client-side/3082>\
**Category:** ScyllaDB\
**Tags:** lwt, data-model, ttl, secondary-index, consistency\
**Created:** [October 24, 2024, 2:26pm UTC](https://forum.scylladb.com/t/implement-transaction-in-client-side/3082 "2024-10-24T14:26:03Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jerome](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jerome](https://forum.scylladb.com/u/jerome)\
**Post date:** [October 24, 2024, 2:26pm UTC](https://forum.scylladb.com/t/implement-transaction-in-client-side/3082/1 "2024-10-24T14:26:03Z")

</div>

Howdy people! This is my first post on this forum, but I’ve been using ScyllaDB for the better part of this year.

My use case requires the use of transactions. This means updating a group of records across partitions with guaranteed all succeed or rollback, which LWT doesn’t provide. I’m taking inspiration from [GitHub - awslabs/dynamodb-transactions](http://github.com/awslabs/dynamodb-transactions) with a CQL port to tackle this issue.

A simple concrete example –

- table **accounts** : id\*, email (e.g. 12345, [foo@example.com](mailto:foo@example.com))
- table **emails** : email\*, account\_id (e.g. [foo@example.com](mailto:foo@example.com), 12345)

When a user wants to change his email, we need to update both tables, namely:

1. UPDATE accounts SET email = ‘bar@example.com’ WHERE id = 12345;
2. DELETE FROM emails WHERE email = ‘foo@example.com’;
3. INSERT INTO emails (id, email) VALUES (‘bar@example.com’, 12345);

I am aware that we can use GSI, but I’m not doing that for a variety of reasons.

Taking a page out of the dynamodb-transactions repo, what I think may be possible (node here means the client program that is interacting with the database):

1. database:

- modify both **accounts** and **emails** tables with additional fields: version, lock, lock\_tmp, lock\_done
- create table **tx** : id\*, created, image(blob), status, updated

1. read table accounts and emails to make sure their lock field is null. Abort if not null. Otherwise, note their current field values and version. Serialize all that info into bytes.
2. INSERT INTO tx: id is random UUID, image, is the serialized bytes, and status is PENDING. set created and updated to current time. At this point if node fails, we are left with an orphan record in tx, which I figure is not a big deal.
3. set lock field for all items, using conditions for concurrency conflict.
4. make the change for all items, again using conditions for concurrency conflict and ensuring lock is still there.
5. set the tx status to committed, again using conditions
6. remove the lock on each item, or if lock\_tmp is true, delete the item.
7. set tx status to completed. Done.

This will require N quorum reads from (2), 1 INSERT with one consistency from (3), and 3N+2 LWT from (4) through (8).

Do you suppose this is a good idea?

Also a side question: can I regard the result from a LWT query as of quorum consistency?

---

<div class="post-metadata">

**Author:** ![felipemendes](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.scylladb.com/felipemendes/32/11_2.png) [@felipemendes](https://forum.scylladb.com/u/felipemendes)\
**Post date:** [October 28, 2024, 3:14pm UTC](https://forum.scylladb.com/t/implement-transaction-in-client-side/3082/2 "2024-10-28T15:14:28Z")

</div>

> I am aware that we can use GSI, but I’m not doing that for a variety of reasons.

Note we do [support synchronous](https://enterprise.docs.scylladb.com/stable/cql/cql-extensions.html#synchronous-global-secondary-indexes) GSIs.

> - INSERT INTO tx: id is random UUID, image, is the serialized bytes, and status is PENDING. set created and updated to current time. At this point if node fails, we are left with an orphan record in tx, which I figure is not a big deal.

You may also TTL it, as ideally this tx shouldn’t live for too long anyway. You definitely want to TTL the `lock` field for all items, in case a worker dies and you don’t hold it for too long (unless you plan to come up with a custom-made _unlocking_ mechanism)

> - make the change for all items, again using conditions for concurrency conflict and ensuring lock is still there.
> - set the tx status to committed, again using conditions
> - remove the lock on each item, or if lock\_tmp is true, delete the item.
> - set tx status to completed. Done.

Looks fine, could maybe be optimized a few. FWIW, [Service Resilience — part 3: Distributed Locking | by Martina Alilovic Rojnic | ReversingLabs Engineering | Medium](https://medium.com/reversinglabs-engineering/service-resiliency-part-3-distributed-locking-666ba09f761d) is a nice write-up on how ReversingLabs did it with ScyllaDB, so it may also help you out.

> Also a side question: can I regard the result from a LWT query as of quorum consistency?

See `SERIAL CONSISTENCY` in [Lightweight Transactions | ScyllaDB Docs](https://opensource.docs.scylladb.com/stable/using-scylla/lwt.html), though yea - it requires a majority for consensus.