Encounter deadlock while loading data

desmondchew — Thu, 04 Apr 2024 14:21:03 GMT

We are doing a table load from Oracle 11g into MySQL 8.0 on premise.

Hit into a deadlock error. How can we resolve this?

Failed to load data from csv file.
Failed to load file '2'
Failed to start load process for file '2'
RetCode: SQL_ERROR SqlState: HY000 NativeError: 1213 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.36]Deadlock found when trying to get lock; try restarting transaction
Failed (retcode -1) to execute statement: load data local infile "D:\\Attunity\\Replicate\\data\\tasks\

Here's our table DDL

CREATE TABLE `LOCKED_TABLE` (
`PO_ID` int NOT NULL,
`USER_ID` int NOT NULL,
`SESSION_ID` varchar(100) COLLATE utf8mb4_0900_as_cs NOT NULL,
`EXPIRE_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`PO_ID`,`USER_ID`,`EXPIRE_TIME`),
KEY `FK_LOCKED_PO_USER` (`USER_ID`),
CONSTRAINT `FK_LOCKED_TABLE_USER` FOREIGN KEY (`USER_ID`) REFERENCES `USER` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs

Re: Encounter deadlock while loading data

Dana_Baldwin — Thu, 04 Apr 2024 14:39:55 GMT

Hi @desmondchew

It looks like there was activity on the target that was interfering with the task. Can you load this table at a time when user activity on the target is low?

Thanks,

Dana

Re: Encounter deadlock while loading data

desmondchew — Thu, 04 Apr 2024 14:48:38 GMT

I resolved this by creating composite index on additional column. It works now. To check the deadlock, use ==> show engine innodb status;

topic Encounter deadlock while loading data in Qlik Replicate

Encounter deadlock while loading data

Re: Encounter deadlock while loading data

Re: Encounter deadlock while loading data