MySQL 8.0 - implementing Deadlocks in Mixed Storage Engines between InnoDB and MyISAM
I'm optimizing some code but Hey everyone, I'm running into an issue that's driving me crazy. I'm experiencing deadlocks when executing transactions that involve both InnoDB and MyISAM tables in MySQL 8.0. My application uses InnoDB for most tables due to its support for transactions, but I have a few legacy tables still using MyISAM. I attempted to run two concurrent transactions that involve the same MyISAM table while also accessing an InnoDB table. Here's a simplified version of what I'm trying to do: ```sql START TRANSACTION; UPDATE my_innodb_table SET value = value + 1 WHERE id = 1; UPDATE my_myisam_table SET value = value + 1 WHERE id = 1; COMMIT; ``` And in another session: ```sql START TRANSACTION; UPDATE my_myisam_table SET value = value + 1 WHERE id = 1; UPDATE my_innodb_table SET value = value + 1 WHERE id = 1; COMMIT; ``` When both transactions run concurrently, I frequently encounter a `Deadlock found when trying to get lock; try restarting transaction` behavior. I've tried using different isolation levels like `READ COMMITTED` and `SERIALIZABLE`, but the scenario continues. The MyISAM table does not support row-level locking, which I suspect is causing the deadlock when mixed with InnoDB's row-level locking. I also ran the `SHOW ENGINE INNODB STATUS;` command and noticed the deadlock report indicates that one transaction was waiting for a lock on the MyISAM table while the other held a lock on the InnoDB table. Is there a recommended approach to minimize or avoid these deadlocks when working with mixed storage engines? Should I consider converting all tables to InnoDB, or are there specific best practices I can apply in this scenario? Any insights on this would be greatly appreciated! My development environment is Windows 10. Am I missing something obvious?