topic Re: Deadlock encounter while trying to load a table into target MySQL DB in Qlik Replicate

Deadlock encounter while trying to load a table into target MySQL DB

desmondchew — Fri, 15 Sep 2023 07:03:38 GMT

We are trying to load a new table from Oracle source endpoint into MySQL 8.0 RDS endpoint. All tables were loaded successfully except one table unable to load due to "deadlock found" error.

How can we fix or workaround this?

Table

.....

;; RetCode: SQL_ERROR SqlState: HY000 NativeError: 1213 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.34]Deadlock found when trying to get lock; try restarting transaction; Failed to start load process for file '2'; Failed to load file '2'; Failed to

Re: Deadlock encounter while trying to load a table into target MySQL DB

john_wang — Fri, 15 Sep 2023 07:35:42 GMT

Hello @desmondchew ,

Thanks for opening the article.

IN general the deadlock is caused by another process accessing and locking the table in the target database while the Replicate task was trying to load the table by using .csv files. Could you please make sure which processes caused the deadlock(s)? At least we need to know if it's caused by different Replicate parallel load threads.

You may check the deadlock by query:

show engine innodb status;

Please let us know the result.

thank you,

John.

Re: Deadlock encounter while trying to load a table into target MySQL DB

desmondchew — Fri, 15 Sep 2023 08:10:04 GMT

Apparently there was trigger associated with this table that we are loading. Once we disable the triggers the job was able to load. Thank you.

Re: Deadlock encounter while trying to load a table into target MySQL DB

john_wang — Fri, 15 Sep 2023 08:12:42 GMT

Hi Desmond @desmondchew ,

Glad to hear the feedback. Thanks.

John.