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:
- Sums the deposits (
SELECT amount FROM deposits WHERE user_id = ...
) - Sums the withdrawals (
SELECT amount FROM withdrawals WHERE user_id = ...
) - If the difference is greater than the requested amount,
INSERT
s 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:
-
Am I correct in understanding that I need to use
SERIALIZABLE
as the isolation level for this transaction? -
Will the
SERIALIZABLE
range lock prevent theREPEATABLE READ
transaction from inserting new rows into the Withdrawals, or removing rows from the Deposits?
Best Answer
Be sure to have
FOR UPDATE
on the end of theSELECTs
. Else another connection could sneak in and change the results of theSELECTs
.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.