👀 Views: 88 💬 Answers: 1 📅 Created: 2025-08-24

mysql innodb performance SQL

I'm attempting to set up I'm building a feature where I've been struggling with this for a few days now and could really use some help... I'm experiencing important performance optimization while executing multi-row inserts in MySQL 8.0 using InnoDB. My current insert statement looks like this: ```sql INSERT INTO my_table (col1, col2) VALUES (1, 'first'), (2, 'second'), (3, 'third'); ``` While executing this in a transaction, I sometimes encounter a deadlock behavior: `behavior 1213 (40001): Deadlock found when trying to get lock; try restarting transaction`. I have tried adjusting the `innodb_lock_wait_timeout` setting to 50 seconds, but the deadlocks still occur frequently. My table has a composite unique index on (col1, col2) and my insert operation is wrapped in a transaction as follows: ```sql START TRANSACTION; INSERT INTO my_table (col1, col2) VALUES (4, 'fourth'), (5, 'fifth'); COMMIT; ``` My application is using PHP's PDO for database interactions, and these inserts are part of a batch processing operation that runs every hour. I also noticed that while the transaction is running, other queries to this table are significantly slower. I've even tried breaking down the inserts into smaller batches, but it hasn't resolved the scenario. Any suggestions on how to optimize this or manage these deadlocks more effectively? This is part of a larger API I'm building. Any ideas what could be causing this? I'm working on a desktop app that needs to handle this. Any help would be greatly appreciated! I'm using Sql 3.9 in this project. Hoping someone can shed some light on this. I've been using Sql for about a year now. Thanks, I really appreciate it!