MySQL 8.0 - Trouble with InnoDB Lock Wait Timeout in High Concurrency Environment
I'm trying to figure out I'm writing unit tests and I'm experiencing a `Lock wait timeout exceeded; try restarting transaction` behavior in MySQL 8.0 while working with a high concurrency environment. I have a transaction that tries to update multiple rows in a table, and it's causing lock contention, especially when multiple users are accessing the same records simultaneously. Here's the code I'm using for the update: ```sql START TRANSACTION; UPDATE users SET status = 'active' WHERE last_login < NOW() - INTERVAL 30 DAY; COMMIT; ``` I've noticed that when one transaction is waiting for the lock to be released, it sometimes leads to a timeout. I attempted to increase the `innodb_lock_wait_timeout` value to 120 seconds, but it hasn't helped. I've also checked the `SHOW ENGINE INNODB STATUS;` output, and it shows that the locks are being held by a long-running transaction that isn't committing. To debug, I ran `SHOW PROCESSLIST;` and found that many connections are in the `Locked` state, which led me to suspect that my queries are not optimized for concurrency. I'm using the following isolation level: ```sql SET TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` To mitigate this scenario, I tried breaking the update into smaller batches: ```sql SET @batch_size = 100; UPDATE users SET status = 'active' WHERE last_login < NOW() - INTERVAL 30 DAY LIMIT @batch_size; ``` However, this didn't resolve the lock issues completely, and the performance is still suboptimal. What strategies can I implement to reduce lock contention and improve performance in this high-concurrency scenario? Are there better ways to structure my transactions or optimize my queries? Any advice on best practices for handling such scenarios would be greatly appreciated. I'm using Sql 3.11 in this project. Could someone point me to the right documentation? What would be the recommended way to handle this? This is part of a larger CLI tool I'm building. What are your experiences with this?