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
- 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)?
- 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!
