Order Matching System Design : how to design an efficient and secure crypto account system

Question

Context

I am working in a crypto exchange company as backend engineer. Recently, we are facing a performance issue in our matching system. When user's order got match, system will deduct or add order's amount to their account. This process will lock user's account with database write lock.

Problem

If users make order too frequently in a short time, lots of order will try to lock the same record. Under such lock contention situation, DB transaction will timeout and retry until finishing matching process. Meanwhile db CPU usage increase quickly. We are trying to fix this lock contention problem.

Present System Design

CREATE TABLE `user_accounts` (
  `user_id` bigint(20) unsigned NOT NULL,
  `btc` decimal(65,0) NOT NULL DEFAULT '0' COMMENT 'btc balance',
  `btc_trade` decimal(65,0) NOT NULL DEFAULT '0' COMMENT 'trading btc amount',
  `eth` decimal(65,0) NOT NULL DEFAULT '0',
  `eth_trade` decimal(65,0) NOT NULL DEFAULT '0',
  `usdt` decimal(65,0) NOT NULL DEFAULT '0',
  `usdt_trade` decimal(65,0) NOT NULL DEFAULT '0',
  `sol` decimal(65,0) NOT NULL DEFAULT '0',
  `sol_trade` decimal(65,0) NOT NULL DEFAULT '0',
  `balance_checksum` VARCHAR(255) NOT NULL DEFAULT '',
  ....
)

Above db schema is used to store user's balance and matching flow is that :

1. If user's order got match on btc/usdt trading pair, the system execute this sql firstly. SELECT btc, btc_trade, usdt, usdt_trade, balance_checksum FROM user_accounts WHERE user_id = ? FOR UPDATE
2. Using amount value and checksum to verify correctness of user's balance. This checksum value can prevent from modifying balance directly by db operation.
3. If balance verification pass, the system generate new checksum based on updated amount and execute update SQL. UPDATE user_accounts SET btc_trade=?, usdt=?, balance_checksum=? WHERE user_id = ?

Possible Solutions

After some brainstorming, we came out some of ideas.

1. Redesign account_balances db schema :

Current schema store all currencies in the same record, however the system only use two currencies during matching process. Some of developer present new db schema like :

CREATE TABLE `new_user_accounts` (
  `id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `currency` VARCHAR(50) NOT NULL DEFAULT '',
  `amount` decimal(65,0) NOT NULL DEFAULT '0',
  `lock_amount` decimal(65,0) NOT NULL DEFAULT '0',
  `balance_checksum` VARCHAR(255) NOT NULL DEFAULT '',
  ....
)

This solution might reduce some lock contention situation, however most of order require trading USDT or other fiat currencies. Therefore, I proposed the second solution.

2. Get rid of checksum mechanism

If we remove checksum value from db schema, we can update balance by using SQL UPDATE user_accounts SET btc_trade=btc_trade-?, usdt=usdt+? WHERE user_id = ? AND btc_trade >= ?. With this update SQL, the transaction does not require the lock. We can solve lock contention problem completely.

One of problem of this solution is that I have to find out a alternative for checksum value. Is there better way to prevent from modifying balance directly by db operation or better way to monitor illegal balance modification operation.

Answer 1

Have you considered introducing a pipeline for redis?