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

[desmondchew]

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

[john_wang]

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.

[desmondchew · Accepted solution]

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.

[john_wang]

Hi Desmond @desmondchew ,

Glad to hear the feedback. Thanks.

John.