I'm trying to achieve a high level of writes on a single account for a ledger, however, if there is a high level of concurrency, things start to slow down significantly. My current setup is basically this:
- Update a single record with the new value (done using
UPDATE table SET field = field+$new_value). This table is used to fetch the current balance quickly. - Insert into another table the amount that was credited/debited.
- Return new balance to client.
This is all done inside a transaction and so the obvious problem is the update locks the account for concurrent access.
I'm trying to set something up that would only use INSERTs, however, in case of a DEBIT transaction, each account holds a minimum value that must not be set below to.
For instance: account A can only go as low as $20 but account B can go to -$50.
Credit operations are fine as INSERT only as there is no business rule to a maximum.
I already tried serializing access to each individual account, but that did not improve performance (measured in TPS, benchmarked with 100 simultaneous threads).
I'm currently using Aurora MySQL 5.7, but I am open to trying other databases
