0

This is a simplified example of a problem I've been working on. Say I have the following database schema:

Table: Deposits
+--------+------------+--------+---------+
|   ID   |    Date    | Amount | User ID |
+--------+------------+--------+---------+
| c1f... | 1589993488 |   40.0 | 6c7...  |
| bfe... | 1589994420 |   30.0 | 744...  |
+--------+------------+--------+---------+
Table: Withdrawals
+--------+------------+--------+---------+
|   ID   |    Date    | Amount | User ID |
+--------+------------+--------+---------+
| ad4... | 1589995414 |   20.0 | 6c7...  |
| e9b... | 1589996417 |   20.0 | 6c7...  |
+--------+------------+--------+---------+

And I'm writing a function that performs a withdrawal for a User. It:

  1. Sums the deposits (SELECT amount FROM deposits WHERE user_id = ...)
  2. Sums the withdrawals (SELECT amount FROM withdrawals WHERE user_id = ...)
  3. If the difference is greater than the requested amount, INSERTs a new Withdrawal

We're using MySQL 8 and the default isolation level of REPEATABLE READ.

This function may be running as a lambda, so memory locks are not an option, and we don't have distributed locking (ie. a Redid-based lock) available.

A Caveat

There are existing admin operations run at the REPEATABLE READ level to create/delete these entities on-demand, by ID.

My Questions:

  1. Am I correct in understanding that I need to use SERIALIZABLE as the isolation level for this transaction?

  2. Will the SERIALIZABLE range lock prevent the REPEATABLE READ transaction from inserting new rows into the Withdrawals, or removing rows from the Deposits?

1
  • You say "sum", yet there is not SUM() in the SELECT. Please show us the real code, not hand-waving.
    – Rick James
    May 23, 2020 at 3:06

1 Answer 1

0

Be sure to have FOR UPDATE on the end of the SELECTs. Else another connection could sneak in and change the results of the SELECTs.

Also, steps 1,2,3 need to be inside a single TRANSACTION.

Once you do all that, you can probably leave the isolation level at the default.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.