Implement transaction in client side

ScyllaDB
, , , ,
1

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 with a CQL port to tackle this issue.

A simple concrete example –

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?

2

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

Note we do support synchronous 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 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, though yea - it requires a majority for consensus.

1 Like