4

I've researched the topic on the official documentation and other questions, however weeks into it I'm still failing to understand what's causing deadlocks in my application. It only happens in production, where we have thousands of users and millions of records. We need to store trades for our clients, the table schema is the following:

CREATE TABLE `log_fill` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `orderId` varchar(63) NOT NULL,
  `clientOrderId` varchar(36) NOT NULL,
  `symbol` varchar(31) NOT NULL,
  `executionId` varchar(255) DEFAULT NULL,
  `executionSide` tinyint NOT NULL COMMENT '0 = long, 1 = short',
  `executionSize` decimal(21,8) NOT NULL,
  `executionPrice` decimal(21,8) unsigned NOT NULL,
  `executionTime` bigint unsigned NOT NULL,
  `executionValue` decimal(21,8) NOT NULL,
  `executionFee` decimal(13,8) NOT NULL,
  `feeAsset` varchar(63) DEFAULT NULL,
  `positionSizeBeforeFill` decimal(21,8) DEFAULT NULL,
  `apiKey` int NOT NULL,
  `side` varchar(20) DEFAULT NULL,
  `reconciled` tinyint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `executionId` (`executionId`,`executionSide`,`apiKey`),
  KEY `apiKey` (`apiKey`,`symbol`,`side`)
) ENGINE=InnoDB

There are two services interacting with the table. The first one inserts records one at a time, in real time. The second is a reconciling process that runs once in a while to ensure data is accurate (for various reasons, the first process might miss some trades from time to time). Therefore, INSERTs on the first services are for a single record, while the reconciler first deletes non reconciled trades and then inserts reconciled ones in batches.
It is important to note there is a BEFORE INSERT trigger to calculate the positionSizeBeforeFill field. This is computationally demanding but we need to keep that information for various reasons. The trigger is

IF(NEW.positionSizeBeforeFill IS NULL) THEN SET 
    NEW.positionSizeBeforeFill = (
    SELECT positionSizeBeforeFill + executionSize 
    FROM log_fill 
    WHERE apiKey = NEW.apiKey AND symbol = NEW.symbol AND side = NEW.side 
    ORDER BY id DESC 
    LIMIT 1
);

The reconciler does not cause any issues, however the real time process feeding data often incurs in deadlocks. Using SHOW INNODB ENGINE STATUS to gather information shows that two transactions are holding each other.

*** (1) TRANSACTION:
TRANSACTION 47977352, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 33621, OS thread handle 23268364363520, query id 1958285567 10.0.0.1 database_name
INSERT INTO log_fill (executionId, executionSide, orderId, clientOrderId, symbol, executionSize, executionPrice, executionTime, executionValue, executionFee, feeAsset, apiKey, side)
    VALUES ('e1661f89', 1, '3fde1207', '', 'ETHUSDT', -4.14, 1575.36, 1676468299769, 6521.99, 3.2609951, 'ETH', 3869, 1)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 71 page no 47926 n bits 624 index apiKey of table `database_name`.`log_fill` trx id 47977352 lock mode S locks gap before rec
Record lock, heap no 230 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000f1d; asc     ;;
 1: len 7; hex 45544855534454; asc ETHUSDT;;
 2: len 1; hex 32; asc 2;;
 3: len 8; hex 80000000479b0309; asc     G   ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 71 page no 47926 n bits 624 index apiKey of table `database_name`.`log_fill` trx id 47977352 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 230 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000f1d; asc     ;;
 1: len 7; hex 45544855534454; asc ETHUSDT;;
 2: len 1; hex 32; asc 2;;
 3: len 8; hex 80000000479b0309; asc     G   ;;


*** (2) TRANSACTION:
TRANSACTION 47977391, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 33622, OS thread handle 23067679225600, query id 1958285687 10.0.0.1 database_name
INSERT INTO log_fill (executionId, executionSide, orderId, clientOrderId, symbol, executionSize, executionPrice, executionTime, executionValue, executionFee, feeAsset, apiKey, side)
    VALUES ('24df10cd', 1, '3fde1207', '', 'ETHUSDT', -0.02, 1575.36, 1676468299769, 31.5072, 0.0157536, 'ETH', 3869, 1)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 71 page no 47926 n bits 624 index apiKey of table `database_name`.`log_fill` trx id 47977391 lock mode S locks gap before rec
Record lock, heap no 230 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000f1d; asc     ;;
 1: len 7; hex 45544855534454; asc ETHUSDT;;
 2: len 1; hex 32; asc 2;;
 3: len 8; hex 80000000479b0309; asc     G   ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 71 page no 47926 n bits 624 index apiKey of table `database_name`.`log_fill` trx id 47977391 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 230 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000f1d; asc     ;;
 1: len 7; hex 45544855534454; asc ETHUSDT;;
 2: len 1; hex 32; asc 2;;
 3: len 8; hex 80000000479b0309; asc     G   ;;

Judging on the type of locks in place (mode S for the tx holding the lock, mode X for tx failing to get the hold) it seems this is caused by the trigger, but there are a few things I don't understand

  1. If it was the trigger's fault, why doesn't this always happen? But just in some cases when the load on the database is high (i.e., production)?
  2. What is actually going on here? Does the first tx get the S lock of the record, then the second tx does the same, then the first tries to write but the lock is held by the second tx? I know that locks of type S prevents type X locks from being acquired. But then I don't understand why this issue doesn't happen with a single transaction as well, because (judging on space ID and pages being held) the piece of information would be locked even when a single tx is running (shouldn't the type S lock prevent type X from kicking in?)

All in all, I'm failing to understand what the steps to reproduce this condition are. I researched MySQL locks but still failing to understand what the root cause is here. Help is much appreciated!

2

1 Answer 1

-1
CREATE TABLE `log_fill` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `orderId` varchar(63) NOT NULL,
  `clientOrderId` varchar(36) NOT NULL,
  `symbol` varchar(31) NOT NULL,
  `executionId` varchar(255) DEFAULT NULL,
  `executionSide` tinyint NOT NULL COMMENT '0 = long, 1 = short',
  `executionSize` decimal(21,8) NOT NULL,
  `executionPrice` decimal(21,8) unsigned NOT NULL,
  `executionTime` bigint unsigned NOT NULL,
  `executionValue` decimal(21,8) NOT NULL,
  `executionFee` decimal(13,8) NOT NULL,
  `feeAsset` varchar(63) DEFAULT NULL,
  `positionSizeBeforeFill` decimal(21,8) DEFAULT NULL,
  `apiKey` int NOT NULL,
  `side` varchar(20) DEFAULT NULL,
  `reconciled` tinyint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `executionId_apiKey` (`executionId`,`executionSide`,`apiKey`),
  KEY `apiKey_symbol_side` (`apiKey`,`symbol`,`side`)
) ENGINE=InnoDB;

changed the index name apiKey to apiKey_symbol_side to reflect the columns it covers. The log_fill table has the following columns:

id: A bigint column that serves as the primary key and is set to auto-increment.
orderId: A varchar(63) column that stores an order identifier and is marked as NOT NULL.
clientOrderId: A varchar(36) column that stores a client order identifier and is marked as NOT NULL.
symbol: A varchar(31) column that stores a symbol and is marked as NOT NULL.
executionId: A varchar(255) column that stores an execution identifier and is set as nullable.
executionSide: A tinyint column that represents the execution side. It is marked as NOT NULL and has a comment indicating that 0 represents "long" and 1 represents "short".
executionSize: A decimal(21,8) column that stores the execution size and is marked as NOT NULL.
executionPrice: A decimal(21,8) unsigned column that stores the execution price and is marked as NOT NULL.
executionTime: A bigint unsigned column that stores the execution time and is marked as NOT NULL.
executionValue: A decimal(21,8) column that stores the execution value and is marked as NOT NULL.
executionFee: A decimal(13,8) column that stores the execution fee and is marked as NOT NULL.
feeAsset: A varchar(63) column that stores a fee asset and is set as nullable.
positionSizeBeforeFill: A decimal(21,8) column that stores the position size before the fill and is set as nullable.
apiKey: An int column that stores an API key and is marked as NOT NULL.
side: A varchar(20) column that stores a side and is set as nullable.
reconciled: A tinyint unsigned column that represents whether the entry is reconciled and has a default value of 0.
1
  • A bit more explanation would help future visitors. Commented Jul 17, 2023 at 1:28

Your Answer

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

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