0

I'm exploring moving an application built on top of MySQL into Spanner and am not sure if I can replicate certain functionality from our MySQL db.

basically a simplified version of our mysql schema would look like this

users
id
name
balance

user_transactions
id
user_id
external_id
amount

user_locks
user_id
date

when the application receives a transaction for a user the app starts a mysql transaction, updates the user_lock for that user, checks if the user has sufficient balance for the transaction, creates a new transaction, and then updates the balance. It is possible the application receive transactions for a user at the same time and so the lock forces them to be sequential.

Is it possible to replicate this in Spanner? How would I do so? Basically If the application receives two transactions at the same time I want to ensure that they are given an order and that the changed data from the first transaction is propagated to the second transaction.

Maxim
  • 4,075
  • 1
  • 14
  • 23
Tucker Joseph
  • 11
  • 2

1 Answers1

0

Cloud Spanner would do this by default since it provides serializability which means that all transactions appear to have occurred in serial order. You can read more about the transaction semantics here: https://cloud.google.com/spanner/docs/transactions#rw_transaction_semantics

Vikas Kedia
  • 648
  • 1
  • 7
  • 9
  • Let's assume sequentially the follow happens: start transaction A, read data X, start transaction B, read data X, write data X, commit A. I want to ensure that transaction B's read includes the committed changes from A but I can't tell from the docs if thats the case by default or if it's not is there some way to create a lock that gets acquired at the beginning of the transaction. – Tucker Joseph Oct 31 '17 at 10:23
  • When you try to commit Transaction B, it will be aborted if any data that it read has changed in the meanwhile. Then you will need to retry B (client libraries would do that automatically for you) and then it would read the latest value of X. – Vikas Kedia Nov 02 '17 at 16:51
  • If you read the data in a read/write transaction, it would automatically lock the rows/columns returned. Note, not the entire row is locked, only the columns returned by the query is locked. – kanagaraj palanisamy Nov 03 '17 at 00:53