I have a table
user_rollup_message_list that is keyed by (
message_reference). It is a write-heavy table, and gets enough writes that I get many lock timeouts, with a timeout of 50 seconds, on writes each day. It is possible that the writes are starved because of reads (I don't actually know how to profile this), but this is unlikely because MySQL prioritizes writes against reads.
Our engine is InnoDB, which does row-level locking. As I understand, the reason locks are needed on
INSERT (we are actually using
INSERT IGNORE if that makes a difference) is to make sure we won't duplicate a unique key, either against an existing row or one being added concurrently.
My table has two unique keys:
PRIMARY KEY (`id`,`user_id`), UNIQUE KEY `rollup_message_list` (`user_id`,`message_reference`),
Which refer to these columns:
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `message_reference` varchar(80) DEFAULT NULL,
so the primary key should not cause timeouts. However in my Java application I get many per day:
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
As for the other unique key, an aspect of the application is that for a fixed
user_id, writes should actually be very sparse. If the MySQL server locks intelligently, it should be able to avoid deadlocks entirely, but perhaps inserts are unwisely locking the entire table.
How can I diagnose or improve this? I believe with the right query or table design, it should be possible to get the locks to the queries that need them, based on the application behavior.