Explain inexplicable deadlock

Question

First of all, I don't see how I could be getting any deadlock at all, since I am using no explicit locking, there's only one table involved, there's a separate process each to insert, select, and update rows, only one row is inserted or updated at a time, and each process only rarely (perhaps once a minute) runs at all.

It's an email queue:

CREATE TABLE `emails_queue` (
  `id` varchar(40) NOT NULL,
  `email_address` varchar(128) DEFAULT NULL,
  `body` text,
  `status_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('pending','inprocess','sent','discarded','failed') DEFAULT NULL,
  KEY `status` (`status`),
  KEY `status_time` (`status`,`status_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

The generating process, in response to some user action but roughly every 90 seconds, does an insert to the table, setting the status to "pending".

There's a monitoring process that every minute checks that the number of "pending" and "failed" emails is not excessive. It takes less than a second to run and has never given me any trouble.

Every minute, the sending process grabs all the pending emails. It loops through and one email at a time, sets its status to "inprocess", tries to send it, and finally sets its status accordingly to "sent", "discarded" (it has reasons for deciding an email shouldn't go out), or "failed" (rejected by the SMTP system).

The statement for setting the status is unusual.

UPDATE emails_queue SET status=?, status_time=NOW() WHERE id=? AND status = ?

That is, I only update the status if the current status it already what I believe it to be. Before this mechanism, I accidentally kicked off two sending processes and they would each try to send the same email. Now, if that were to happen, one process would successfully move the email from "pending" to "inprocess", but the second one would update zero rows, realize there's a problem, and skip that email.

The problem is, about one time in 100, the update fails altogether! I get com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

WTH?

This is the only table and only query that this happens to and it only happens in production (to maximize difficulty in investigating it).

The only two things that seem at all unusual are (1) updating a column that participates in the WHERE clause, and (2) the (unused) automatic updating of the status_time.

I'm looking for any suggestions or diagnostic techniques.

Answer 1

With some database servers there are default settings for locking behavior. The usual default it to use locks (at least on the systems I used). I'm not sure this is true on mysql but I believe it is.

Do you have an index on the emails_queue table? The type of index can change how it does locking. In one case I dealt with not having a clustered index on the table caused it to use page locking instead of row locking. I had explicitly told it to use row locking and it silently changed it. Page locking can cause deadlocks. Try checking that index.

If those don't help the solution is the one suggested in the error message. Catch the exception for deadlocks and re-run the sql when it happens.

Answer 2

You have not described the scope of the transactions in your description. If each process that you have described is trying to do everything within a single transaction, then there certainly is the potential for deadlock in this system.

While it may seem like a deadlock should not occur because only a single table is involved, the resources that are being locked are not tables but rows. Two processes may each be holding a row lock that is required by the other processes, if the same transaction is used to manipulate multiple rows.

Answer 3

Firstly, deadlocks do not depend on explicit locking. MySQL's LOCK TABLE or using non-default transaction isolation modes are NOT required to have a deadlock. You can still have deadlocks if you never use an explicit transaction.

Deadlocks can happen on a single table, quite easily. Most commonly it's from a single hot table.

Deadlocks can even happen if all your transactions just do a single row insert.

A deadlock can happen if you have

• More than one connection to the database (obviously)
• Any operation that internally involves more than one lock.

What is not obvious, is that most of the time, a single row insert or update involves more than one lock. The reason for this is that secondary indexes also need to be locked during inserts / updates.

SELECTs won't lock (assuming you're using the default isolation mode, and aren't using FOR UPDATE) so they can't be the cause.

SHOW ENGINE INNODB STATUS is your friend. It will give you a bunch of (admittedly very confusing) information about deadlocks, specifically, the most recent one.

• You can't completely eliminate deadlocks, they will continue to happen in production (even on test systems if stress them properly)
• Aim for a very low amount of deadlocks. If 1% of your transactions deadlock, that is possibly too many.
• Consider changing the transaction isolation level of your transactions to read-committed IF YOU FULLY UNDERSTAND THE IMPLICATIONS
• ensure that your software handles deadlocks appropriately.