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

  • Why not just using the sum of the transactions as the new sum and forget about the extra table where the locking will occur?
    – user953
    Jun 22, 2022 at 20:08
  • Not sure what your question is.
    – mustaccio
    Jun 22, 2022 at 20:28
  • @ik_zelf because I have to check if debit is allowed based on how much balance there is left. Even if I do a select and an insert inside a transaction, concurrent transactions can and will cause the balance to be wrong, because they will all se the same available balance.
    – Luis
    Jun 23, 2022 at 1:10
  • @mustaccio I'm trying to improve TPS for this kind of operation. Locking does not scale as much as I need, so I'm trying to think of a solution using only inserts, or something else
    – Luis
    Jun 23, 2022 at 1:11
  • Additional information request, please. AWS Aurora TYPE? Any SSD or NVME devices on MySQL Host server? Post TXT results on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions for performance improvement. Jun 24, 2022 at 21:44


Your Answer

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