I have a table user_rollup_message_list that is keyed by (user_id, 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.

  • Can you add the CREATE TABLE code? Does the table have any other columns/indexes? – ypercubeᵀᴹ Oct 7 '13 at 16:26
  • And just curious, why have you chosen the (id,user_id) for PK - and not only the (id)? – ypercubeᵀᴹ Oct 7 '13 at 16:27
  • Also, you say it's write-intensive. Do you have only INSERTs? Or is it UPDATE and DELETE intensive as well? – ypercubeᵀᴹ Oct 7 '13 at 16:29
  • @ypercube 1. There are other columns and KEYs (not unique), including keys on user_id, (user_id, id) and user_id + other columns. I'd rather understand which of these can be factors in write-performance than post the complete code, I'd be a little uncomfortable having that much IP in public. 2. I literally have no idea (it's legacy code). 3. only INSERTs. – djechlin Oct 7 '13 at 17:52
  • 1
    A very important point here is that this error has nothing to do with deadlocks. Deadlocks generate an entirely different error message. This error occurs when a query has to wait longer than innodb_lock_wait_timeout seconds to lock a row. Conversely, InnoDB detects deadlocks immediately and the error says Deadlock found when trying to get lock. – Michael - sqlbot Oct 8 '13 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.